如何获得一个在PHPmyadmin中工作的mysql脚本，以便在PHP mysql查询中工作？

Question

Simple question with i'm sure a simple answer. I have searched around and can't find a solution. Basically my code below works in Phpmyadmin but it wont work in a PHP mysql query script. The code orders my "rank" row by the largest number, and then updates the "position" row with a number, starting at 1 for the greatest number in the "rank" column, and then incrementing the "position" row as the "rank" row values become smaller.

SET @r=0;
UPDATE Data SET position= @r:= (@r+1) ORDER BY rank DESC;
SET @r=0;
SELECT *, @r:= (@r+1) as position FROM Data ORDER BY rank DESC;

Thanks in advance!!

Answer 1

You have multiple queries here. Try using mysqli_multi_query:

$result = mysqli_multi_query($con,"SET @r=0; 
              UPDATE Data SET position= @r:= (@r+1) ORDER BY rank DESC;
              SET @r=0; SELECT *, @r:= (@r+1) as position 
              FROM Data ORDER BY rank DESC;") ;