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

,

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

,

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

Code: Select all

$ID = mysql_insert_id();
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

Code: Select all

if (empty($wert)) {
    continue;
}

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 :oops:

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

Code: Select all

print_r($_POST[$zahl]);
before that and check what's inside