Page 1 of 1

Two MySQL queries into one.

Posted: Sat Jan 09, 2010 2:48 pm
by satikas
Hi, I have two queries for changing the items position. Move one up and other down.

The problem is that any user who clicks on the arrow fast enough can break the script when it hasn't yet moved the second item.

It works fine, when I use it normally.

Can I somehow put these queries together?

Thank you for your time.

Code: Select all

 
<?php 
 
mysql_query("UPDATE bookmarks SET position='$position'
WHERE position='$higherposition' AND ename='$ename' AND parent='$parent'") or die(mysql_error());
 
mysql_query("UPDATE bookmarks SET position='$higherposition' 
WHERE ID='$id' AND ename='$ename' AND parent='$parent'") or die(mysql_error());
?>
 

Re: Two MySQL queries into one.

Posted: Sat Jan 09, 2010 2:58 pm
by Eran
There are two things you could do -
1. Group the two queries into a transaction http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
2. Prevent the user from pressing the button too fast at the UI level

Re: Two MySQL queries into one.

Posted: Sat Jan 09, 2010 3:08 pm
by satikas
Thanks for your reply.
I'm new to this transaction thing.

Is it supposed to be like this?
Doesn't do anything.

Code: Select all

 
<?php
mysql_query("START TRANSACTION;
  UPDATE bookmarks SET position='$position'
  WHERE position='$higherposition' AND ename='$ename' AND parent='$parent';
  
  UPDATE bookmarks SET position='$higherposition' 
  WHERE ID='$id' AND ename='$ename' AND parent='$parent';
  COMMIT;");
?>
 

Re: Two MySQL queries into one.

Posted: Sat Jan 09, 2010 3:14 pm
by Eran
You can't run multiple queries with one mysql_query() call. You can either switch to mysqli or issue the queries one at a time. As a side note, store the result of a mysql_query call in variable and check it when things aren't working.

Code: Select all

$result = mysql_query(...);
if($result === false) {
    echo mysql_error();
}

Re: Two MySQL queries into one.

Posted: Sat Jan 09, 2010 3:23 pm
by satikas
Thank you for the tips. Putting the queries into "while{}" made the trick.