Row ranking query works in MySQL but not in PHP

Question

I've looked at about a dozen posts about queries not working in PHP, but they all haven't been able to solve my problem - hopefully this is an easy one!

What I am attempting to do here is "rank" the rows based on their total yearly sales, and that's worked pretty well so far. When I run my query in MySQL, it works properly - I get back hundreds of results.

SET @row_number := 0;
SELECT @row_number := @row_number + 1 AS row_number, 
        TotalRevenue, 
        CompanyID
        FROM (
                SELECT CompanyID, 
                SUM(Sales_Amt) AS TotalRevenue 
                FROM Sales, 
                (SELECT @row_number := 0) r 
                GROUP BY CompanyID 
            ) t 
        WHERE TotalRevenue > 0 
        ORDER BY TotalRevenue DESC

Produces:

row_number | TotalRevenue | CompanyID
-----------+--------------+----------
         1 |     81130.00 |       333
         2 |     72234.00 |       876
         3 |     62653.00 |       123
         4 |     54408.40 |       999
         5 |     44548.00 |       111

However, when I run it via PHP, I get back the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @row_number := @row_number + 1 AS row_number, TotalRevenue' at line 2

Based on other posts here, I've tried:

adding `` around my column names
adding '' around my column names
adding := instead of = when I created @row_number
confirming that all spaces are added in after my query in PHP so that key words are not squished together
setting my mysqli->charset to utf-8

Here's my PHP code, in case you need that as well:

$query = "SET @row_number := 0; 
        SELECT @row_number := @row_number + 1 AS `row_number`, 
        TotalRevenue, 
        CompanyID 
        FROM ( 
                SELECT CompanyID, 
                SUM(`Sales_Amt`) AS TotalRevenue 
                FROM Sales 
                GROUP BY CompanyID 
            ) t 
        WHERE TotalRevenue > 0 
        ORDER BY TotalRevenue DESC";


if (!$result = $mysqli->query($query))
{
    print_r($mysqli->error);
}

Hoping this will be something really simple that I am just NOT seeing.

Thanks!

For further help, see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, May 30 '18 at 15:33
I don't think the question marked as the one this is a dupe of isn't quite right... The OP in that instance was specifically asking about mutli query, whereas in this instance, an error in the query, which similar, somewhat different in tact... — Can O' Spam, May 30 '18 at 15:41

score 0 · Accepted Answer · answered May 30 '18 at 15:31

To add another option aside from S. Dev's answer, you can use mysqli multi query;

$query = "SET @row_number := 0; 
        SELECT @row_number := @row_number + 1 AS `row_number`, 
        TotalRevenue, 
        CompanyID 
        FROM ( 
                SELECT CompanyID, 
                SUM(`Sales_Amt`) AS TotalRevenue 
                FROM Sales 
                GROUP BY CompanyID 
            ) t 
        WHERE TotalRevenue > 0 
        ORDER BY TotalRevenue DESC";


if (!$result = $mysqli->multi_query($query))
{
    print_r($mysqli->error);
}

This will allow you to preform more than 1 query at one time, keeping it all within the same remit of the query

It also uses slightly less overhead as you are only doing (technically) 1 query as there is only 1 connection rather than 2 separate calls

It could be worth looking into stored procedures to make it so you don't have to set variables, rather, pass them into the query itself

Thank you Sam! You are completely correct - I didn't even know about multi_query, and that was the solution to all of my problems. I'll vote up your answer, and mark as solved. — Heather Stewart, May 30 '18 at 16:38
If this answers your question, please consider marking as accepted :) — Can O' Spam, May 31 '18 at 07:55

Row ranking query works in MySQL but not in PHP

1 Answers1