two query updates doesn't work

Question

hi guys I got a little problem while I am was trying to updates my table. I just explain you my situation: index.php

 <?php
          $query = mysql_query("SELECT * FROM `references` ORDER BY `ID` ASC") or die(mysql_error());
while($referenc = mysql_fetch_assoc($query)){
        echo '

        <tr>    

               <td>'.$referenc['name'].'</td>
               <td>'.$referenc['url'].'</td>
               <td>'.$referenc['date'].'</td>
               <td><a href="updatetest.php?id=' . $referenc['ID'] . '&move=up&position=' . $referenc['position'] . '">up</a></td>
               <td><a href="updatetest.php?id=' . $referenc['ID'] . '&move=down&position=' . $referenc['position'] . '">down</a></td> 
               <td><a href="showrefer.php?id=' . $referenc['ID'] . '">edit</a></td>
        </tr>    

            ';  
    }  


          ?>

how you can see I have there two links for up and down. after click on one of that link I am going on updatetest.php

updaterest.php f.e

move = up
position = 2;
id = 2;



  <?php
            $move = $_GET['move'];

             if($move == "up"){
                    $getcurrentid = $_GET['id'];
                                $moveup = $_GET['position']-1;

                $getprevposition = $_GET['position']-1;
                $position = $_GET['position'];

               $query = mysql_query("UPDATE `references` SET  `position`  = '$moveup' WHERE `references`.`ID`='$getcurrentid'") or die(mysql_error());

$query2 = mysql_query("UPDATE `references` SET  `position`  = '$position' WHERE `references`.`position`='$getprevposition'") or die(mysql_error());

              }
              else{echo'down';}

              ?>

Now it doing always just one update if I comment first update second one is working fine if I comment second update first one is working fine but if I uncomment it and wanna do it together its always doing just one update. I looking for some way how to do 2 updates in same time any ideas ?

http://stackoverflow.com/questions/5580854/how-to-run-multiple-update-queries-in-single-statement-in-php-and-mysql — ray, Jun 08 '14 at 11:00

score 1 · Accepted Answer · answered Jun 08 '14 at 10:32

You need to do the queries in another order. $moveup and $getprevposition have the same value, so you are currently doing something like this

change position to new where id = x
change position to old where position = new

Since you just changed position to new, the second WHERE will catch the item you already edited (and undo your edit). Instead, you should do it like this

change position to old where position = new
change position to new where id = x

Now you don't have a conflict, as the row you really want to change doesn't yet have position = new.

two query updates doesn't work

1 Answers1