Page 1 of 3
[SOLVED] overwriting data in db
Posted: Tue Jun 26, 2007 7:00 am
by knallbernd
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
hi!
i've made a script for an inquiry which already works right now - except one problem ;-(.
Code: Select all
$y = $_POST['name'];
echo $y;
for ($i=1; $i<3; $i++ ) {
$zahl = "Frage".$i;
$array[$zahl] = $_POST[$zahl];
foreach ($array[$zahl] as $key => $wert) {
echo "In '".$key."' steht bei '".$zahl."' der Wert '".$wert."'<br />\n";
$Name = $key;
$Abteilung = $y;
$Wert = $wert;
if ($i == 1){
$query = "INSERT INTO personen (ID, Name, Abteilung) VALUES (NULL, '$key', '$y')";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
}
$aendern = "UPDATE personen SET $zahl = '$wert' WHERE Name = '$key'";
$ergebnis = mysql_query($aendern) or die('Query failed: ' . mysql_error());
echo $ergebnis;
// AND ID = LAST_INSERT_ID()
}
}
mysql_close();
As you see, I create the name if $i==1 and put the values into the specific name. The problem is, if I do the inquiry again, the values from
the names will be overwritten (which is logical). I've already tried it with LAST_INSERT_ID(), but now the values are only written in the last created name, which is also logical. But I don't get the clue how to make it another way. Maybe someone could help me out. I can also post the whole code if it would make this more understandable. thanx!
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Tue Jun 26, 2007 7:55 am
by Begby
The best way to start is to write out clearly what this code is supposed to do? What should the end result be after you run it?
Posted: Tue Jun 26, 2007 8:06 am
by arturm
make sure that ID in your personen table is auto_increment.
Posted: Tue Jun 26, 2007 8:47 am
by knallbernd
It is auto_increment

.
To make it more understandable, I'll give you the code with comments.
First the form, which is dynamically produced from the db.
Code: Select all
<html>
<head>
</head>
<form method="post" action="formular.php">
<?php
error_reporting(E_ALL);
//produce the form dynamically from the db, depending on the chosen "kurs":
$x = mysql_real_escape_string($_POST["kurs"]); //saves the chosen "kurs" in the variable $x
$abfrage = "SELECT $x FROM lehrende";
$ergebnis = mysql_query($abfrage) or die("MySQL-Fehler: " . mysql_error());
if (mysql_num_rows($ergebnis)) {
while($row = mysql_fetch_array($ergebnis))
{
$rname = $row[0];
echo "$rname
//producing the radiobuttons
<table>
<tr><td>Unterrichtsvorbereitung</td>
<td><input type=\"radio\" name=\"Frage1[{$rname}]\" value=\"1\"></td>
<td><input type=\"radio\" name=\"Frage1[{$rname}]\" value=\"2\"></td>
<td><input type=\"radio\" name=\"Frage1[{$rname}]\" value=\"3\"></td>
<td><input type=\"radio\" name=\"Frage1[{$rname}]\" value=\"4\"></td>
<td><input type=\"radio\" name=\"Frage1[{$rname}]\" value=\"5\"></td>
<td><input type=\"radio\" name=\"Frage1[{$rname}]\" value=\"6\"></td>
</tr>
<tr><td>Fachliche Sicherheit</td>
<td><input type=\"radio\" name=\"Frage2[{$rname}]\" value=\"1\"></td>
<td><input type=\"radio\" name=\"Frage2[{$rname}]\" value=\"2\"></td>
<td><input type=\"radio\" name=\"Frage2[{$rname}]\" value=\"3\"></td>
<td><input type=\"radio\" name=\"Frage2[{$rname}]\" value=\"4\"></td>
<td><input type=\"radio\" name=\"Frage2[{$rname}]\" value=\"5\"></td>
<td><input type=\"radio\" name=\"Frage2[{$rname}]\" value=\"6\"></td>
</tr>
</table>";
}
} else {
echo "No result for {$_POST['kurs']}<br />";
}
?>
<input type="hidden" name="name" value="<?PHP print ("$x"); ?>">
<input value=" Beurteilung absenden" type="submit">
</form>
</html>
And now how I write the results in the db.
Code: Select all
<?php
error_reporting(E_ALL);
$verbindung = mysql_connect ('localhost', 'root', '')
or die ("keine Verbindung möglich.
Benutzername oder Passwort sind falsch");
mysql_select_db("homepage")
or die ("Die Datenbank existiert nicht.");
echo '<pre>';
var_dump($_POST);
echo '</pre>';
$y = $_POST['name']; //$y not important for my problem
echo $y;
//if-statement, the limit is equal to the amount of questions to be asked
for ($i=1; $i<3; $i++ ) {
$zahl = "Frage".$i; //variable, depends on $i and belongs to the questions in the form (Frage1, Frage2,...) --> named as the columns in the db.
$array[$zahl] = $_POST[$zahl]; // saves the values from the form into an array ($array[Frage1], $array[Frage2],...)
foreach ($array[$zahl] as $key => $wert) {
echo "In '".$key."' is question '".$zahl."' the value '".$wert."'<br />\n"; //for example: In Name1 is Frage1 the value 3.
$Name = $key;
$Abteilung = $y;
$Wert = $wert;
//write in db: only if $i==1, because I want the names only to be written once.
if ($i == 1){
$query = "INSERT INTO personen (ID, Name, Abteilung) VALUES (NULL, '$key', '$y')";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
}
//updating the values - for example:
//Name/ Frage1 / Frage2
//Name1 / 1 / 5
//Name2 / 2 / 3
//here is the problem, because ALL names (personen) are going to be UPDATED when a new form ist completed --> doesn't work with LAST_INSERT_ID() because only the last name is going to be UPDATED.
$aendern = "UPDATE personen SET $zahl = '$wert' WHERE Name = '$key'";
$ergebnis = mysql_query($aendern) or die('Query failed: ' . mysql_error());
echo $ergebnis;
}
}
mysql_close();
?>
And these are all POST-values:
array(3) {
["Frage1"]=>
array(3) {
["Name1"]=>
string(1) "2"
["Name2"]=>
string(1) "2"
["Name3"]=>
string(1) "3"
}
["Frage2"]=>
array(3) {
["Name1"]=>
string(1) "1"
["Name2"]=>
string(1) "1"
["Name3"]=>
string(1) "4"
}
As I mentioned, it principally works, but only if only one person completes the form. The others would overwrite the results.
Posted: Tue Jun 26, 2007 8:55 am
by arturm
I think I know where the problem is.
after
Code: Select all
$query = "INSERT INTO personen (ID, Name, Abteilung) VALUES (NULL, '$key', '$y')";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
add
and change update to:
Code: Select all
$aendern = "UPDATE personen SET $zahl = '".$wert."' WHERE ID = '".$ID."'";
$ergebnis = mysql_query($aendern) or die('Query failed: ' . mysql_error());
It will prevent overwriting old settings for new submits
Posted: Tue Jun 26, 2007 9:11 am
by knallbernd
Hm, still the same problem as with LAST_INSERT_ID():
Name / Frage1 / Frage2 / Frage3
Name1 / 3 /.../
Name2 / 2 / .../
Name3 / 2 / 4
The values from Frage2 are only written in the last INSERTED Name, because of the if-statement.
Posted: Tue Jun 26, 2007 9:40 am
by knallbernd
Ok, now I made it like this:
Code: Select all
if ($i==1){
$query = "INSERT INTO personen (ID, Name, Abteilung) VALUES (NULL, '$key', '$y')";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$ID = mysql_insert_id();
}
/*$aendern = "UPDATE personen SET $zahl = '$wert' WHERE ID = '".$ID."'";
$ergebnis = mysql_query($aendern) or die('Query failed: ' . mysql_error());*/
$aendern = "UPDATE personen SET $zahl = '$wert' WHERE Name = '$key' AND $zahl = ''";
$ergebnis = mysql_query($aendern) or die('Query failed: ' . mysql_error());
But maybe not the most elegant solution

. I would still be grateful for 'better' solutions...
Posted: Tue Jun 26, 2007 9:40 am
by arturm
you are right - ID will work only in first case
can you do:
Code: Select all
$aendern = "UPDATE personen SET $zahl = '$wert' WHERE Name = '$key'";
$ergebnis = mysql_query($aendern) or die('Query failed: ' . mysql_error());
echo $aendern."<br>";
and post result
Posted: Tue Jun 26, 2007 9:48 am
by knallbernd
In 'Name1' steht bei 'Frage1' der Wert '1'
UPDATE personen SET Frage1 = '1' WHERE Name = 'Name1'
1In 'Name2' steht bei 'Frage1' der Wert '2'
UPDATE personen SET Frage1 = '2' WHERE Name = 'Name2'
1In 'Name3' steht bei 'Frage1' der Wert '3'
UPDATE personen SET Frage1 = '3' WHERE Name = 'Name3'
1In 'Name1' steht bei 'Frage2' der Wert '1'
UPDATE personen SET Frage2 = '1' WHERE Name = 'Name1'
1In 'Name2' steht bei 'Frage2' der Wert '2'
UPDATE personen SET Frage2 = '2' WHERE Name = 'Name2'
1In 'Name3' steht bei 'Frage2' der Wert '3'
UPDATE personen SET Frage2 = '3' WHERE Name = 'Name3'
Posted: Tue Jun 26, 2007 10:00 am
by arturm
Code: Select all
UPDATE personen SET Frage1 = '1' WHERE Name = 'Name1'
UPDATE personen SET Frage1 = '2' WHERE Name = 'Name2'
UPDATE personen SET Frage1 = '3' WHERE Name = 'Name3'
UPDATE personen SET Frage2 = '1' WHERE Name = 'Name1'
UPDATE personen SET Frage2 = '2' WHERE Name = 'Name2'
UPDATE personen SET Frage2 = '3' WHERE Name = 'Name3'
after this you should have in you database:
Name | Frage1 | Frage2
Name1 | 1 | 1
Name2 | 2 | 2
Name3 | 3 | 3
Is it right ?
Posted: Tue Jun 26, 2007 10:08 am
by knallbernd
Right. But it will still overwrite the names when sending the form again. I will try with to make it with the solution mentioned above (only UPDATE empty fields). Together with a form-check if all radio-buttons are marked this should work, don't you think?
Posted: Tue Jun 26, 2007 10:17 am
by arturm
you can do that or you can check before inserting/updating
Posted: Tue Jun 26, 2007 10:22 am
by knallbernd
Allright! Thank you very very much for your efforts!
Posted: Tue Jun 26, 2007 11:09 am
by knallbernd
Nearly
How can I manage to prove if ANY radiobutton is empty?
Code: Select all
$zahl = "Frage".$i;
if ( empty($_POST[$zahl]) ){
echo "please answer every question";
exit;
}
This ain't working.
Posted: Tue Jun 26, 2007 11:16 am
by arturm
do
before that and check what's inside