Jump to content

update several rows in one column


error_22
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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")

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Dan The Prof
Link to comment
Share on other sites

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 by Dan The Prof
Link to comment
Share on other sites

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 by Dan The Prof
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 by Dan The Prof
Link to comment
Share on other sites

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']} :)
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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>";}

Link to comment
Share on other sites

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']}'";

Link to comment
Share on other sites

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>";	}

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...