Running 2 Mysql queries via PHP

Question

I am trying to run the below query, I've figured out the SET statements are a separate query to the SELECT query. I've found a few answers relating to this but cant get them to work in my code.

It all works fine in phpMyAdmin but not in this PHP webpagepage

How do i go about running the 2 queries to get the result?

  $queryRank = "SET @rnk=0; SET @rank=0; SET @curscore=0;

SELECT rank FROM
        (
         SELECT AA.*,BB.playerId,
        (@rnk:=@rnk+1) rnk,
        (@rank:=IF(@curscore=best3,@rank,@rnk)) rank,
        (@curscore:=best3) newscore
        FROM
          (
           SELECT * FROM
           (SELECT COUNT(1) scorecount,best3
           FROM leaderboard GROUP BY best3
        ) AAA
    ORDER BY best3 DESC
    ) AA LEFT JOIN leaderboard BB USING (best3)) A
    WHERE playerId='$memberID'";



$resultRank = mysqli_query($link, $queryRank);

if (mysqli_num_rows($resultRank) > 0) {

        $rowRank = mysqli_fetch_array($resultRank);

echo "<p class='mt-2 statNumber'>".$rowRank['rank']."</p>";


    } else {

        echo "No events played";


   }

With `mysqli_query()` you can only run one query in one call. There's also [mysqli_multi_query()](https://www.php.net/manual/en/mysqli.multi-query.php). — KIKO Software, May 08 '19 at 09:09
@KIKOSoftware for your information, this function is a complex and dangerous tool, very complex to use. So it is not to be recommended by default. Not to mention neither mysqli_query() nor mysqli_multi_query() should be used with php variables in the query — Your Common Sense, May 08 '19 at 09:27
I'd suggest that you create a `stored procedure` to accomplish your goal which would remove the need for multi-queries within the PHP code leaving a simple `call proc(?)` type query — Professor Abronsius, May 08 '19 at 09:33

Professor Abronsius · Accepted Answer · 2019-05-08T12:14:45.173

0

Just a thought but if you were to generate a simple Stored Procedure your querying in PHP would be much simpler and would negate the apparent need for multiple queries

DROP PROCEDURE IF EXISTS `spGetRank`;
delimiter //
create procedure `spGetRank`(
    IN `p_player_id` VARCHAR(64)
)

begin
    declare _player_id integer default 0;


    set @_player_id=cast( p_player_id as unsigned );
    set @rnk=0;
    set @rank=0;
    set @curscore=0;

    select rank from
        (
         select aa.*, bb.playerid,
        (@rnk:=@rnk+1) rnk,
        (@rank:=if(@curscore=best3,@rank,@rnk)) rank,
        (@curscore:=best3) newscore
        from
          (
           select * from
           (select count(1) scorecount, best3
               from leaderboard group by best3
            ) aaa
                order by best3 desc
            ) aa 
    left join leaderboard bb using (best3)) a
   where playerid=@_player_id;

end//
delimiter ;

Then, in PHP

$sql='call `spGetRank`(?)';
$stmt=$link->prepare( $sql );
if( $stmt ){
    $stmt->bind_param('s', $memberID );
    $res=$stmt->execute();



    if( $res ){
        $stmt->store_result();
        $stmt->bind_result( $rank );
        $stmt->fetch();
        $stmt->close();

        echo 'Rank:' . $rank;
    }
}

Another possibility would be to define the variables in the actual query itself rather than as separate queries - though to be honest the above query is pretty complicated. The general gist of it would be like this though:

 select ... (@rnk := ifnull( @rnk, 0 ) + 1 ) etc

edited May 08 '19 at 12:14

answered May 08 '19 at 09:42

Professor Abronsius

33,063
5
32
46

The stored procedure looks like a good way to solve, I've attempted to use but just keep getting a rank of '0' – Jon May 08 '19 at 10:10
mysql db will do odd things with variables. before running the above try, in your mysql client app ( gui or cmdline ) setting these variables to null. Then try running the stored procedure from gui or cmd. The above was not tested but is essentially the exact same code as you had initially - sadly I have no way to test – Professor Abronsius May 08 '19 at 10:19
I've tried running procedure within phpMyAdmin, asks for the parameter but still gives 0. Excuse my complete ignorance with the following but the memberID variable that comes in is a number between 1 and 30, does this make any difference with any of variable declarations? – Jon May 08 '19 at 10:51
you can always modify that to match the other integers - I simply guessed at varchar – Professor Abronsius May 08 '19 at 11:13
can you add the table schema & possibly some sample data? At least then I could test – Professor Abronsius May 08 '19 at 12:05
1

OK - apologies for the incorrect procedure originally given. I mocked upa simple table based upon columns I could determine in the original query and made some amendments to the Stored Procedure above - which seems now to work?? – Professor Abronsius May 08 '19 at 12:15
No need to apologise, your edit now works perfectly. You beat me to it, was just going to add table schema. Thanks for your assistance, its much appreciated. – Jon May 08 '19 at 12:24

Running 2 Mysql queries via PHP

1 Answers1