[RESOLVED] update all rows in some field at the same time, how?

error_22

Hi,

I have a table like this:

Table name: page

ID | name | status | order

The "name" is being looped in an menu.....and the "order" field sets the order of the menu links (name).

I have a form to udate all "order"-rows at the same time but i cant get it to work....heres my form and db script:

echo "<form action='page.php?action=order' method='POST'>";  

$sql = "SELECT * FROM `page` ORDER BY `id` DESC";  

    $result = mysql_query($sql) or die(mysql_error());  

    while ($row = mysql_fetch_object($result))  

{  

echo "<input type='text' name='order[$row->id]' value='$row->order'>";  

}  

echo "<input type='submit' value='save'></form>";

in page.php?action=order:

$sql = "SELECT * FROM `page` ORDER BY `id` DESC"; 
    $result = mysql_query($sql) or die(mysql_error()); 
    while ($row = mysql_fetch_object($result)) 
    { 
        $insert = $_POST["order[$row->id]"]; 

    $sql = "UPDATE `page` SET `order` = `$insert` ORDER BY `id` DESC"; 
    mysql_query($sql) or die(mysql_error()); 
}

When running this i get the following error:

Unknown column '' in 'field list'

What am I doing wrong?

Thanks in advance
Niklas

bpat1434

try this as your query:

UPDATE page SET order='$insert'

The order-by clause isn't necessary, since you're not returning a result set, and since you're updating all rows regardless of order.

error_22

yeah i noticed that i had used ` when i should have used '.

One problem less, but its still not working. All rows get the same value.

Im pretty sure the problem is that...

$sql = "UPDATE `page` SET `order` = '".$insert."'";

...cant handle several different values. The question is, what to do about it?

Thanks for answering!

bpat1434

What do you mean it can't handle several different values?

error_22

echo "<form action='page.php?action=order' method='POST'>";   

$sql = "SELECT * FROM `page` ORDER BY `id` DESC";   

    $result = mysql_query($sql) or die(mysql_error());   

    while ($row = mysql_fetch_object($result))   

{   

echo "<input type='text' name='order[$row->id]' value='$row->order'>";   

}   

echo "<input type='submit' value='save'></form>";

in HTML, that results in:

<form action='page.php?action=order' method='POST'>
<input type='text' name='order[1]' value='1'>
<input type='text' name='order[2]' value='2'>
<input type='text' name='order[3]' value='3'>
<input type='submit' value='save'></form>

The information is then sennt to the mysql query. Heres where the problem occurs: All rows get the same value, in this case "3" (the last value in the HTML form).

bpat1434

and your code for the query is? Your HTML form is fine, so it's your code on the page.php that's getting messed up.

error_22

$sql = "SELECT * FROM `page` ORDER BY `id` DESC"; 
    $result = mysql_query($sql) or die(mysql_error()); 
    while ($row = mysql_fetch_object($result)) 
    { 
        $insert = $_POST["order[$row->id]"]; 

    $sql = "UPDATE `page` SET `order` = '$insert' ORDER BY `id` DESC"; 
    mysql_query($sql) or die(mysql_error()); 
}

bpat1434

I think this:

$insert = $_POST["order[$row->id]"];

shoudl be:

$insert = $_POST["order"][$row->id];

error_22

I've tried that already. The problem is not that $insert is empty. The problem is that only one value is being saved in the databse with the query im using. Do you know how to update several rows at the same time?

error_22

anyone?

bpat1434

the query does update all the rows in the database as it should. As long as you don't have a limiting clause, or a WHERE clause which determines what conditions the row/column values meet, all the rows in the database should be updated.

Now, here's teh thing:

Select all the rows from the table
For each row returned (so every row) we loop:
--- We assign the id of the current row to $insert
--- We update each row in the table setting 'order' = $insert value

If you do that, every row in the table should have the last row id as the value of order. Which means your query works.

I'm not sure what you're trying to do. Your query should loop through each time, and your database should be updated on each loop and have the values change form one to the next until the last itteration when it sets it for the last time. Then all the rows have that last value. So it's an expected result.

error_22

I have a form with several input fields....or to be exact, one input field which is looped as many tmes as the amount of rows in the table. I want the values in these text fields to be saved in the ríght row....

input field 1 -> row 1
input field 2 -> row 2
etc.

as it is now its more like this:

input field 1 -> nothing is sent
input field 2 (assuming that field 2 is the last one) -> this value is saved in all rows.