Two MySQL queries into one.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
satikas
Forum Newbie
Posts: 19
Joined: Sat Mar 28, 2009 9:14 am

Two MySQL queries into one.

Post 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());
?>
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Two MySQL queries into one.

Post 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
satikas
Forum Newbie
Posts: 19
Joined: Sat Mar 28, 2009 9:14 am

Re: Two MySQL queries into one.

Post 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;");
?>
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Two MySQL queries into one.

Post 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();
}
satikas
Forum Newbie
Posts: 19
Joined: Sat Mar 28, 2009 9:14 am

Re: Two MySQL queries into one.

Post by satikas »

Thank you for the tips. Putting the queries into "while{}" made the trick.
Post Reply