Mysqli multi query PHP

Question

I want to use php mysqli function, to query this:

SET @rownum = 0, @rank = 1, @prev_val = NULL;
SELECT @rownum := @rownum + 1 AS row,
    @rank := IF(@prev_val!=score,@rownum,@rank) AS rank,
    userid,
    @prev_val := score AS score
FROM (
    SELECT userid, sum(amount) as score 
    from leads WHERE date(time) >= '2013-08-15'
    group by userid
) exodus_entries 
 ORDER BY rank asc LIMIT 0,100;

I tried using the mysqli_query(link, query); function, but no luck, any help?

Try using mysqli::multi_query instead because your query is actually multiple statements — Mark Baker
– Mark Baker, Commented Sep 18, 2013 at 19:07
I am trying this: $query = mysqli_multi_query($cxn,$sqll); $result = mysqli_store_result($cxn); while ($row = mysqli_fetch_row($result)) { echo $row[1] . "<br>"; } But mysqli_fetch_row returns error. — user2770029
– user2770029, Commented Sep 18, 2013 at 19:17
mysqli_fetch_row() expects parameter 1 to be mysqli_result, boolean given — user2770029
– user2770029, Commented Sep 18, 2013 at 19:22
in that case, show your (php) code. And you can already try changing mysqli_multi_query(.....); into mysqli_multi_query(.....) or die (mysqli_error());. This should tell you if you have any mysql errors that would otherwise be silently ignored. — nl-x
– nl-x, Commented Sep 18, 2013 at 20:06

Bill Karwin · Accepted Answer · 2013-09-18 19:22:40Z

3

There is no need to use multi-query, and you risk security problems whenever you use multi-query.

The values for you user variables @rownum, @rank, @prev_val will remain in effect for the second query, as long as you run both query in the same connection.

So just run mysqli_query("SET ..."); and then run mysqli_query("SELECT..."); separately. It's much simpler to do this, and avoids at least one security risk.

Regarding your error:

mysqli_fetch_row() expects parameter 1 to be mysqli_result, boolean given

Always check the return value of mysqli_query(). It will return false if there's an error. And of course you can't run mysqli_fetch_row(false);

answered Sep 18, 2013 at 19:22

Bill Karwin

567k87 gold badges710 silver badges870 bronze badges

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

2 Comments

Michael Over a year ago

Awesome. This is documented almost nowhere on any APIs or forums. I don't know if this is an "obvious" solution, but I've been searching for over 2 days for this solution.

Bill Karwin Over a year ago

@Michael, read the first paragraph of this manual page: dev.mysql.com/doc/refman/5.6/en/user-variables.html It doesn't literally say that setting a user-variable lasts for the rest of the session, but that's what happens, and it's sure easy to test if you are in doubt.

Collectives™ on Stack Overflow

Mysqli multi query PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related