update several rows in one column

[error_22]

I want to update serveral/all rows in one column in my mysql db table. Is that possible and if yes, what does the code look like?Thanks in advanceNiklas

[Chocolate570]

Yes, it's possible. The code would be like:

UPDATE table SET field = value WHERE where_clause

Where table is the table, field is the field, value is the value, and where_clause is the where clause. (thanks to Ascii_OSborn for giving me the answer on that one )Good luck.

[Jack McKalling]

If the rows have a common part in the where clause, there is no need to further look while the while clause can span more rows (ie: all the rows in which the second column contains "John Doe")If not, you may try to loop in PHP code to execute several queries in a row Then it may look like this:

<?php$array = array("Apple","Bear","Something");for ($n=0; $n<sizeof($array); $n++){$result = mysql_query("UPDATE table SET column{$n}={$array[$n]}");}?>

It sets every loop a different value (from "Apple" to "Something") to a different column (from "column0" to "column2")

[error_22]

Thanks for your help guys, still not sure how i would do this though.....Perhaps I should explain my situatuion:Ok so i have a db table named "page" in that table there are several columns, and one of them is named "order".....I have a script for updating the order column but its not working as it should. All rows get the same value, thats the problem. this is my 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());     }

This does not work as it should, what do I need to change?Thanks in advanceNiklas

[Jack McKalling]

Okay, before you go further, remember that not any name used in your database may be one of the preserved words, just like "order". It is used in SQL to order the result rows, as in the instruction "ORDER BY column_name". (instructions are case insensitive)Some things I don't recognise in your php script, but it should work anyway if you only change the name of that column to something like "SortOrder". I've had exactly the same column, and every SQL query failed due to this. I managed to get the SQL error message, saying it was the column name that caused the error.I hope this helped you

Edited April 19, 2006 by Dan The Prof

[error_22]

Oh i didnt know that, thanks a lot!Its not working though lol......all rows get the same value.

[Jack McKalling]

It is just that you use _fetch_object that I can't help you with you're actual code :)I don't know how to use it, sorry.Maybe you can try using mysql_fetch_row() ? It is the same as mysql_fetch_array(....,MYSQL_NUM), only shorter

Edited April 19, 2006 by Dan The Prof

[error_22]

Im pretty sure its something with the update query. All rows get the same value, which means that the update query cant manage several different values at the same time.

[error_22]

how would your code look with my information? I dont understand it really :S

[Jack McKalling]

In that case, maybe you should try to fetch array, instead of an object.With it you can call to a certain dimension of the array, wich will allways work, maybe unlike the object, I don't know.If the object doesn't work in the SQL query, the UPDATE query wouldn't be able to insert the correct value :)I'll write what I mean:

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_array($result,MYSQL_ASSOC))   {   echo "<input type='text' name='order[{$row['id']}]' value='{$row['order']}'>";   }   echo "<input type='submit' value='save'></form>";

$sql = "SELECT * FROM page ORDER BY id DESC";   $result = mysql_query($sql) or die(mysql_error());   while ($row = mysql_fetch_array($result,MYSQL_ASSOC))   {       $insert = $_POST["order[{$row['id']}]"];           $sql = "UPDATE page SET order = '$insert' ORDER BY id DESC";       mysql_query($sql) or die(mysql_error());   }

The quotes surrounding tablenames or column names aren't neccesary I think, but I'm not sure.It looks dirty this way, inside the POST variable, but it should work. The curly brackets tells the string that inside is a variable, else the angled brackets of that variable wouldn't work.This way changes the object into an array, and this is how you should operate those. If this does not work, please tell me again Edited April 19, 2006 by Dan The Prof

[error_22]

well, nothing works now :)cant even update all rows with the same value :)oh and the quotes are there for security purposes

[Jack McKalling]

Could you give me the result code then, I'll check it again

[error_22]

page.php?show=all:

echo '<br><br><br><table class="tborder" cellpadding="6" cellspacing="1" border="0" width="80%" align="center"><tr>';	echo "<td colspan='3' class='thead'><b><center>Visa alla sidor</center></b></td></tr><tr>";	echo "<td class='alt2'><b>Titel:</b></td><td class='alt2'><b>Status:</b></td><td class='alt2'><b>Ordning:</b></td></tr>";	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_array($result,MYSQL_ASSOC))	{  echo "<tr><td class='alt2'>$row[title]</td>";  echo "<td class='alt2'>";  if ($row['status'] == "true")  {  	echo "Aktiv";  }  elseif ($row['status'] == "false")  {  	echo "Inaktiv";  }  echo "</td>";  echo "<td class='alt2'><input type='text' name='sortorder[{$row['id']}]' value='$row[sortorder]'></td></tr>";	}	echo "</table><br><br>";	echo "<center><input type='submit' value='Spara'></form>";

page.php?action=order:

$sql = "SELECT * FROM `page` ORDER BY `id` DESC";	$result = mysql_query($sql) or die(mysql_error());	while ($row = mysql_fetch_array($result,MYSQL_ASSOC))	{  $insert = $_POST["order[{$row['id']}]"];	  $sql = "UPDATE page SET `sortorder` = '$insert' ORDER BY `id` DESC";       mysql_query($sql) or die(mysql_error());  ?><script>location.href='page.php?show=all';</script><?	}

Thanks!

[Jack McKalling]

You have two little mistakes in this, actuallly three.Don't forget to place variables in strings like the folowing, when you specifie something in angled brackets behind it.

$string = "<.. some attribute='{$array['key_name']}'>...</..>";

:)It is both at the beginning and end of the while loop, where you accidentally forgot some quotes and curly brackets :)After that, this MUST work, else I'll kick it in its bottom

Edited April 19, 2006 by Dan The Prof

[error_22]

you lost me there, what do you mean? :S

[Jack McKalling]

Check this:

echo '<br><br><br><table class="tborder" cellpadding="6" cellspacing="1" border="0" width="80%" align="center"><tr>';echo "<td colspan='3' class='thead'><b><center>Visa alla sidor</center></b></td></tr><tr>";echo "<td class='alt2'><b>Titel:</b></td><td class='alt2'><b>Status:</b></td><td class='alt2'><b>Ordning:</b></td></tr>";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_array($result,MYSQL_ASSOC)){ echo "<tr><td class='alt2'>$row[title]</td>"; echo "<td class='alt2'>"; if ($row['status'] == "true") {  echo "Aktiv"; } elseif ($row['status'] == "false") {  echo "Inaktiv"; } echo "</td>"; echo "<td class='alt2'><input type='text' name='sortorder[{$row['id']}]' value='$row[sortorder]'></td></tr>";}echo "</table><br><br>";echo "<center><input type='submit' value='Spara'></form>";

Should be {$row['title']} and {$row['sortorder']}

[error_22]

what you just corrected only regards what is being displayed in the input text fields. That has nothing to do with the actual update query. Further more, it works as it should, even though it might not be the perfect solution. But what about the problem with the updating? :S

[Jack McKalling]

Well, if this is not working well enough, it would error the updating before it starts :)this line:"<tr><td class='alt2'>$row[title]</td>";Means actually this:"<tr><td class='alt2'>Array[title]</td>";Because php does not recognise [title] as part of the variable, but as a string. Resulting in incorrect values in the input fields, resulting the updating having wrong data to set

[justsomeguy]

There are a few things I would change. First, in your html you call the variable 'sortorder', and in the php you just say 'order'. That's an error.One thing I would change is not to use an array to pass post variables, just separate it with an underscore:

echo "<td class='alt2'><input type='text' name='sortorder_{$row['id']}' value='{$row[sortorder]}'></td></tr>";

Which makes this part easy (there are a few changes):page.php?action=order:

$sql = "SELECT * FROM `page` ORDER BY `id` DESC";$result = mysql_query($sql) or die(mysql_error());while ($row = mysql_fetch_assoc($result)){  $insert = $_POST["sortorder_" . $row['id']];  $sql = "UPDATE page SET `sortorder` = '$insert' ORDER BY `id` DESC";   mysql_query($sql) or die(mysql_error());  echo "<script type=\"text/javascript\">location.href=\"page.php?show=all\";</script>";}

[error_22]

changed it, same thing as before

[error_22]

justsomeguy: I made the changes, still the same thing. Nnothing happens, nothing at all. No errors or anything, but the values arent being updated.

[justsomeguy]

Wow, I missed a glaring step. Here's your query:

$sql = "UPDATE page SET `sortorder` = '$insert' ORDER BY `id` DESC";

You are missing your WHERE clause, so all rows get updated with the same value (whatever the last value was, all rows will have that value). UPDATE queries also do not have an ORDER BY clause. Here you go:

$sql = "UPDATE page SET `sortorder` = '{$insert}' WHERE id = '{$row['id']}'";

[error_22]

same thing, nothign happens, but this time its cause $_POST["sortorder_" . $row['id']] doesnt seem to contain any information. I tried to print it but nothing showed up.

[justsomeguy]

OK, let's back up. Post the relevant HTML and PHP code again.

[error_22]

wow it works now!!but only when i changed the $_POST variable to the following:

echo '<br><br><br><table class="tborder" cellpadding="6" cellspacing="1" border="0" width="80%" align="center"><tr>';	echo "<td colspan='3' class='thead'><b><center>Visa alla sidor</center></b></td></tr><tr>";	echo "<td class='alt2'><b>Titel:</b></td><td class='alt2'><b>Status:</b></td><td class='alt2'><b>Ordning:</b></td></tr>";	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_array($result,MYSQL_ASSOC))	{  echo "<tr><td class='alt2'>{$row['title']}</td>";  echo "<td class='alt2'>";  if ($row['status'] == "true")  { 	 echo "Aktiv";  }  elseif ($row['status'] == "false")  { 	 echo "Inaktiv";  }  echo "</td>";  echo "<td class='alt2'><input type='text' name='sortorder{$row['id']}' value='{$row['sortorder']}'></td></tr>";	}	echo "</table><br><br>";	echo "<center><input type='submit' value='Spara'></form>";

$sql = "SELECT * FROM `page` ORDER BY `id` DESC";	$result = mysql_query($sql) or die(mysql_error());	while ($row = mysql_fetch_assoc($result))	{ 	 $insert = $_POST["sortorder$row[id]"]; 	  	 $sql = "UPDATE page SET `sortorder` = '{$insert}' WHERE `id` = '{$row['id']}'";  	 mysql_query($sql) or die(mysql_error()); 	 echo "<script type=\"text/javascript\">location.href=\"page.php?show=all\";</script>";	}