Why Output of PHP script and MySQL database data are not same?

Question

Please refer to the following image. In this image number (1) is what the field position in my database userdb looks like before I run my code. Number (2) is what change I am expecting in the position field of some rows after I run the code. The value of the position field of second row is primarily 2 what I want to be changed to 3. Similarly, The values of the position field of third, fourth and fifth rows are primarily 3, 4 and 5 respectively what I want to be changed to 4, 5 and 6 respectively. So, I run the following code and in my browser I see what I expect. However, In the database I see wrong data is inserted, like number (3) on the image. Notice that my last expected value of the position field is 6 (for fifth row), but I am getting 6 in all rows!!!

enter image description here

Here is the code I run:

            $requestedPosition = 2;  $oldPosition = 6;

        for($i=$requestedPosition+1; $i <= $oldPosition; $i++){

        $query = mysql_query("UPDATE userdb SET position = '$i' WHERE position='{$requestedPosition}' ");

            echo 'Old Position: '.$requestedPosition.' is now: '.$i.'<br>';
        $requestedPosition++;
        }

I think something must be wrong in the mysql query. If I comment out the query I get expected result in browser. However, to insert those data into database I need to run the query but when I do it, I get wrong data inserted in database. I am on windows, running PHP 5.3.13, MySQL 5.5.24.

Most Confusingly, in the for loop, if I change $i=$requestedPosition+1; to $i=$requestedPosition; I get 2,3,4,5 inserted (in 2nd,3rd,4th,5th row respectively) rather than 6,6,6,6. However, I want 3,4,5,6 inserted (in 2nd,3rd,4th,5th row respectively), so I had to use $i=$requestedPosition+1; in for loop, and when I do, Code gets Mad!!! So, do I :( Stuck with this (with bad headache) for last two days!

andrewsi · Accepted Answer · 2014-04-03 14:50:52Z

5

There's a slight flaw in your logic.

The first time that your loop runs, you're updating one row - you're updating the row with position 2 to be position 3. This is fine.

But the second time the loop runs, you're updating all rows where position is 3 to be position 4 - and there's two of them now, the one from the table, and the one you've just updated. The same happens on the third and fourth iteration.

You can probably do this more elegantly, with one call:

$query = mysql_query("UPDATE userdb SET position = position + 1 WHERE position >={$requestedPosition} AND position <= ${oldPosition}' ");

I've not tested that, but it should at the least give you an idea.

answered Apr 3, 2014 at 14:50

andrewsi

10.7k132 gold badges37 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ifti Mahmud Over a year ago

Hi, the query you gave isn't working for some reason ("position = position + 1", is this right?), but the flaw you detected in the logic is really really helpful. I will work on that and update here as soon as it works. Thanks a lot!

andrewsi Over a year ago

@flight-87 - glad it helped. position=position+1 should increment position for each row that matches the WHERE clause. If you get it working, feel free to edit this answer and add in what actually works!

Ifti Mahmud Over a year ago

The code you gave actually worked. The reason it didn't work first time is because it had a little typing mistake and missing quotations. I updated the query later and it worked. Here is the query:

$query = mysql_query("UPDATE userdb SET position = position + 1 WHERE position >= '{$requestedPosition}' AND position < '{$oldPosition}' ");

Thanks a lot for the help :)

andrewsi Over a year ago

Huh. That's really interesting - do you have the position set up as a varchar field, by any chance?

Ifti Mahmud Over a year ago

No. The position field is set as INT. Why? do you see anything unusual with the query?

|

Collectives™ on Stack Overflow

Why Output of PHP script and MySQL database data are not same?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related