[SOLVED] overwriting data in db

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

[SOLVED] overwriting data in db

Post 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]
Last edited by knallbernd on Thu Jun 28, 2007 11:00 am, edited 1 time in total.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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?
User avatar
arturm
Forum Commoner
Posts: 86
Joined: Fri Apr 13, 2007 8:29 am
Location: NY
Contact:

Post by arturm »

make sure that ID in your personen table is auto_increment.
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

Post 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.
User avatar
arturm
Forum Commoner
Posts: 86
Joined: Fri Apr 13, 2007 8:29 am
Location: NY
Contact:

Post 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
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

Post 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.
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

Post 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...
User avatar
arturm
Forum Commoner
Posts: 86
Joined: Fri Apr 13, 2007 8:29 am
Location: NY
Contact:

Post 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
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

Post 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'
User avatar
arturm
Forum Commoner
Posts: 86
Joined: Fri Apr 13, 2007 8:29 am
Location: NY
Contact:

Post 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 ?
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

Post 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?
User avatar
arturm
Forum Commoner
Posts: 86
Joined: Fri Apr 13, 2007 8:29 am
Location: NY
Contact:

Post by arturm »

you can do that or you can check before inserting/updating

Code: Select all

if (empty($wert)) {
    continue;
}
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

Post by knallbernd »

Allright! Thank you very very much for your efforts!
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

Post 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.
User avatar
arturm
Forum Commoner
Posts: 86
Joined: Fri Apr 13, 2007 8:29 am
Location: NY
Contact:

Post by arturm »

do

Code: Select all

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