Can't update MySQL table

[Tilex]

Hi!

 

This is my first post because I just started programming with PHP...It probably won't be the last post!

 

OK here's my little problem:

 

I have a form that loops through a table (clients) and outputs the info from all the rows (name, address, date joined).  So far so good.

I want the info to be erased in the form and updated by the user.  So the user wants to change the address of client1, and then presses "Submit".

Here my problem arises.

 

if (isset($_POST['Submit'])){
echo "Submission....<BR>";

foreach ($_POST['name'] as $name) {
$i=1;
echo $nom;
echo "<BR>";
$sql1="UPDATE $tbl_name SET name='$name', adsress='$adsress', date_joined='$date_joined' WHERE id='$i'";
$result1=mysql_query($sql1);
++$i;
}
}

if($result1){
echo "<BR> Success";
}
mysql_close();
?>

 

I didn't put the code from the form because it works.  Indeed, in the foreach function, I output the $name (which is the name after the button was pressed), and the output is fine.  BUT, the "Success" string is never outputed, and the table is never updated, so the MySQL query fails.

 

Does anyone have a thought?

[F1Fan]

I see a few things:

 

Is "adsress" a typo? Make sure that's not a typo in your code.

 

Where are you getting "$adsress" and "$date_joined" from? It looks like you're looking through your $_POST['name'] array, but not accessing any other $_POST arrays.

 

How do you know that you are using the same key for each line item?

 

You're updating the DB using var $i that you set in the loop, so how do you know you're updating the right row?

 

Finally, you keep assigning the $result var to your mysql_query(), but you're overwriting the same var over and over, so your "if($result){" will only be looking at the last UPDATE query.

[Tilex]

Hi F1FAN,

 

Thanks for the quick reply!

 

Sorry for the typos, I translated the variables from French so people would understand, and I actually missed you up!

 

Here's the updated code:

 

if (isset($_POST['Submit'])){
echo "Submission....<BR>";

foreach ($_POST['name'] as $name) {
$i=1;
echo $name;
echo "<BR>";
$sql1="UPDATE $tbl_name SET name='$name' WHERE client_id='$i'";
$result1=mysql_query($sql1);
++$i;
}
}

if($result1){
echo "<BR> Success";
}
mysql_close();
?>

 

So "adsress" and "date_joined" are not there anymore.  Let's say I only want to update the "name" field.

You're right about the success of the mysql_query(), it will only look at the last one.

 

For the "id" field, I use an incremental "$i" because I want to update all the "name" in the table, no matter if it got changed in the form or not.

 

Thanks again!

[Tilex]

Hum..

 

The initialization of $i=1 should be outside the foreach loop!

 

I got it working...my bad!  The original id in the UPDATE was in fact client_id, which I changed in my second post because you made me realize I had typos! 

 

Thanks!!!  Sometimes, have someone else looking at your code makes you realize silly mistakes! :-D

[F1Fan]

OK, if you want to delete ALL the rows in the table and then re-insert them, do this:

<?php
if (isset($_POST['Submit'])){
echo "Submission....<BR>";
$query = "DELETE FROM $tbl_name; ";
$i=1;
foreach ($_POST['name'] as $name) {
echo $name;
echo "<BR>";
$query .= "INSERT INTO $tbl_name (name, client_id) VALUES ('$name', '$i'); ";
++$i;
}
}
$result1=mysql_query($query);
if($result1){
echo "<BR> Success";
}
mysql_close();
?>

 

Or, if you just want to update, do this:

<?php
if (isset($_POST['Submit'])){
echo "Submission....<BR>";
$query = "";
$i=1;
foreach ($_POST['name'] as $name) {
echo $name;
echo "<BR>";
$query .= "UPDATE $tbl_name SET name='$name' WHERE client_id='$i'; ";
++$i;
}
}
$result1=mysql_query($query);
if($result1){
echo "<BR> Success";
}
mysql_close();
?>

 

In both cases, this will basically build up one large query, then run it all together AFTER the loop. This way, if it fails, it all fails, but nothing gets messed up. Plus, you can echo the $query variable if there's a problem so you can see what query looks like and maybe see problems.