update multiple records ??

[hisham]

I have the following table:
Id priority
1 3
2 5
3 7
4 2
5 1
6 6
7 4

$pr = 3;
$query = mysql_query("select * from mytable ORDER BY priority"
$num = mysql_num_rows($query);
for ($i=1;$i<$num+1;$i++){
if ($i>=$pr){
$j = $i + 1;
$SQL=&quot;UPDATE mytable SET priority ='$j' where priority = '$i'&quot;;

am trying to update the table by adding 1 to the selected rows then 3 will be 4 and 4 will be 5 etc…, but it won't work.
Thanks in advance

 

[sleipnir214]

You don't have to use PHP variables or looops at all.

This:

UPDATE mytable SET priority = priority + 1

will do it in a single query Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[hisham]

Thank you sleipnir214,
But I don't want to update the records their values are: 1 , 2 and 3, I need to update the values of the records from 4 to the end of the table

 

[sleipnir214]

Then,

UPDATE mytable SET priority = priority + 1 WHERE priority >= 4 Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[hisham]

Thanks again,
and i tried :

$old = 3;
$new = 2;

$SQL = mysql_query(&quot;UPDATE mytable SET priority = priority +1 and priority between $new and $old&quot;
$update = mysql_query(&quot;UPDATE mytable SET priority = $new where id = 4&quot;
it works but it updates the rest of IDs to 0 ??

 

[sleipnir214]

This is correct behavior for this query:

UPDATE mytable SET priority = priority +1 and priority between $new and $old


MySQL is interpreting it as

UPDATE mytable SET priority = ((priority +1) and (priority between $new and $old))

The lefthand side of the assignment statement is being interpreted as a logic operation. The column priority then gets the value of that assigment. In the cases where priority is not between $new and $old, the logical operation evaluates to zero.

Simple SQL queries take the general form of: <SOME ACTION> WHERE <SOME CONDITIION>. You might try the query:

UPDATE mytable SET priority = priority +1 WHERE priority between $new and $old Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!