Php / MySQL - Updating two separate rows in same table at once?

Question

I've tried just about every way possible to update two rows at once. Basically I need to swap the two integer numbers, in a field named 'position'.

I'm setting up a website and I want to be able to edit the content from the website. I have a login page, and many more pages, including a picture's page, which I want to be able to move each picture back and forth. The pictures are ordered by the 'position' field, and I'm not anywhere near professional level of programming or code, so my way is probably very complicated.

I set it up so it links you to a page like: "/?page=pictures&edit=1&p=3&t=4". the page and edit can be ignored, as they are just where at in the page to goto, but 'p' is the current position, and 't' is the target location. So I setup the update code like this...

$pict01 = mysql_real_escape_string($_GET['p']);
$pict02 = mysql_real_escape_string($_GET['t']);

mysql_query("UPDATE pictures SET position = '$pict02' WHERE position='$pict01'");
mysql_query("UPDATE pictures SET position = '$pict01' WHERE position='$pict02'");

It only updates one, so I tried multiple ways, such as having it link to a second page, and updating the second after updating the first. Or using a third and fourth variable instead of the same two. Still nothing, Then I tried a suggestion, of:

mysql_query("UPDATE pictures SET position = 'holdme' WHERE position='$pict01'");
mysql_query("UPDATE pictures SET position = '$pict01' WHERE position='$pict02'");
mysql_query("UPDATE pictures SET position = '$pict02' WHERE position='holdme'");

Still nothing, it seems to update only one thing. Every time I test, I check it before and set each one to 1-5 (there are 5 current records). After executing it, it comes in (for example), 1,3,3,4,5 or 1,2,2,4,5, or 1,2,3,3,5, etc. I've tried separating the code, nothing, even putting the numbers in, nothing. Thanks for any help in advance.

Answer 1

Typically you would assign a unique id to each record in your table by using an auto incrementing field. Then, when you update a record, you would do so by specifying the id in the WHERE clause of your SQL statement.



Assuming your table has an auto incrementing field called id, you can update your position fields like so:



$pict01 = mysql_real_escape_string($_GET['p']);

$pict02 =mysql_real_escape_string($_GET['t']);



$query = "SELECT id FROM pictures WHERE position = '$pict01' "

$result = mysql_query($query);

$row = mysql_fetch_assoc($result)

$id1 = $row['id']



$query = "SELECT id FROM pictures WHERE position = '$pict02' "

$result = mysql_query($query);

$row = mysql_fetch_assoc($result)

$id2 = $row['id']





$result = mysql_query("UPDATE pictures SET position = '$pict02' WHERE id=$id1");

$result = mysql_query("UPDATE pictures SET position = '$pict01' WHERE id=$id2");

Answer 2

First, my compliments for the way you phrase your questions (I noticed the primary key one, yesterday, as well); I wish everyone on this forum wrote their questions the way you do: elaborate, not a homework question (if it is, then it's obvious you've been trying to do it yourself and you deserve some help), and no obvious spelling or grammar mistakes, or typos. Thanks for that!



I can't see the reason why Colanth's answer wouldn't work (his answers usually are pretty good). In the last section you write that you tried setting the initial state (1-5), and you print the final result. Could you try taking even smaller steps?



Like this (in shorthand):



Set the positions in the records to 1 to 5

echo 'Initial state', PHP_EOL;

print the result of "SELECT * FROM pictures"

$pict01 = mysql_real_escape_string($_GET['p']);

$pict02 = mysql_real_escape_string($_GET['t']);

echo 'pict1 = ', $pict01, ' pict2=', $pict02, PHP_EOL;

$sql = "UPDATE pictures SET position = 'holdme' WHERE position='$pict01'";

echo $sql, PHP_EOL;

mysql_query( $sql );

echo 'After step one', PHP_EOL;

print the result of "SELECT * FROM pictures"

$sql = "UPDATE pictures SET position = '$pict01' WHERE position='$pict02'";

echo $sql, PHP_EOL;

mysql_query( $sql );

echo 'After step two', PHP_EOL;

print the result of "SELECT * FROM pictures"

$sql = "UPDATE pictures SET position = '$pict02' WHERE position='holdme'";

echo $sql, PHP_EOL;

mysql_query( $sql );

echo 'After step three', PHP_EOL;

print the result of "SELECT * FROM pictures"



After this, you could check if each of the steps do what you expect them to do. If not, post the log here and we'll try to help you again. I'm actually intrigued now. :-)



Good luck!

Answer 3

First, I'm not a MySql guy.



I'm not sure if {MERGE INTO... USING()... WHEN MATCHED UPDATE SET... WHEN NOT MATCHED INSERT... } statement is supported in MySql, maybe you can look up in the documentation.



Another option would be to create a user-defined function in MySql that will accept two parameters, namely 'p' and 't'. That function will then execute two UPDATE statements that will set the two rows with the given 'p' and 't', vice-versa. You then call that function with the parameters instead of executing an UPDATE statement.



Hope this gave you an idea.

Cheers!