PHP & MySQL: How can I use "SET @rank=0;" in $query=

Question

In my PHP file, I use this line to pull data from my mySQL database:

$query = "SET @rank=0; SELECT @rank:=@rank +1 as rank, Blah Blah...";

If I check the SELECT statement in phpMyAdmin's SQL window (without $query= ) it works fine.

But, if I use it in PHP, then I get an error. It doesn't like the "SET @rank=0;" bit. Is there a way to use "SET @rank=0;" when it's in "$query=" ? Is there a workaround?

The rest of the code is standard stuff for pulling data from a db:

public function getmyData() {


 $mysql = mysql_connect(connection stuff);

 $query = "SELECT @rank:=@rank +1 as rank, formatted_school_name,  blah blah";

 $result = mysql_query($query);

            $ret = array();
                 while ($row = mysql_fetch_object($result)) {
                    $tmp = new VOmyData1();
                    $tmp->stuff1 = $row-> stuff1;
                    $tmp->stuff2 = $row->stuff2;

                    $ret[] = $tmp; 
                        }
                 mysql_free_result($result);

                 return $ret;

    }

Update: I'm trying to use Amerb's suggestion of using multi-query. I concatenated the query like so:

$query = "SET @rank = 0";

$query .= "SELECT @rank:=@rank +1 as rank...

I changed the result to:

$result = $mysqli_multi_query($query);

But, it's failing for some reason. I'm on a machine running PHP 5.2. Any suggestions?

What code are you using to run the "query"? That's actually two queries (note the semicolon before `SELECT`) and at least some of the PHP MySQL bindings do not expect multiple queries in the same call. — Amber, Aug 17 '11 at 23:59
@Amber, Hi Amber thanks for the message. I added some more code. It's the usual code for pulling data from the db. I see your point regarding the ";". Is there a way to get around that problem? Thank you. — Laxmidi, Aug 18 '11 at 00:09
Hi Amber, Thank you for the link. I'm trying to use the multi-query, but it's not working for some reason. I'm doing something wrong. I posted some more code. If you have a suggestion, I'd love to hear it. Thanks! — Laxmidi, Aug 18 '11 at 00:51
When you say "it's failing for some reason" please elaborate - *how* is it failing, what error messages or behavior are you seeing, etc. — Amber, Aug 18 '11 at 01:08
Thanks for the message. Sorry, I wasn't clearer. I'm pulling data from the db into a Flex (Flash) app. I'm getting the following error: faultCode:Client.Error.DeliveryInDoubt faultString:'Channel disconnected' faultDetail:'Channel disconnected before an acknowledgement was received'. If I look in HttpFox, I've got: "Error loading content (NS_ERROR_DOCUMENT_NOT_CACHED)". — Laxmidi, Aug 18 '11 at 02:12

Marvo · Accepted Answer · 2012-12-03T22:52:49.957

9

This guy here seems to have a way of setting the variable in the same query to zero. I don't have MySQL set on up on this machine to try it, though.

Here's the query he suggests in his blog post:

select @rownum:=@rownum+1 ‘rank’, p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10;

(Is there some homework assignment coming due somewhere having to do with computing ranks? This is the third question I've seen on this in two days.)

Are you checking for duplicate scores?

edited Dec 03 '12 at 22:52

answered Aug 18 '11 at 00:05

Marvo

17,845
8
50
74

Hi @Marvo, Thank you for the link. That solved my problem. I am ranking scores... I wish that I were still a young, studly student-- those were the days :) . Thanks for the help. – Laxmidi Aug 18 '11 at 03:11
Maybe this is a stupid question, but what does the ‘p’ stand for? When I run the query, I get the error “Table ‘my_table_name.p’ doesn’t exist”. – Paul Chris Jones Jul 10 '18 at 16:31
That's the alias for the player table. You can see after the p.* that there's the clause "from player p". The p.* now means "all fields from the player table. It's just a way of doing shorthand in SQL. – Marvo Jul 11 '18 at 17:38

score 2 · Answer 2 · answered Aug 18 '11 at 00:00

2

Try executing it as 2 separate successive queries.

answered Aug 18 '11 at 00:00

JJ.

5,425
3
26
31

score 1 · Answer 3 · answered Aug 17 '11 at 23:58

1

You have to enable the use of multiple queries in one, but i forgot how do do this at the moment. It's a security feature.

answered Aug 17 '11 at 23:58

Johni

2,933
4
28
47

score 0 · Answer 4 · answered Mar 14 '22 at 22:42

0

Use mysql_multi_query() or rather mysqli_multi_query() instead of mysql_query()

answered Mar 14 '22 at 22:42

Simon

66
5

PHP & MySQL: How can I use "SET @rank=0;" in $query=

4 Answers4

Linked