0

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?

user2770029
  • 722
  • 1
  • 7
  • 8
  • Try using [mysqli::multi_query](http://www.php.net/manual/en/mysqli.multi-query.php) instead because your query is actually multiple statements – Mark Baker Sep 18 '13 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] . "
    "; } But mysqli_fetch_row returns error.     – user2770029 Sep 18 '13 at 19:17
  • mysqli_fetch_row() expects parameter 1 to be mysqli_result, boolean given – user2770029 Sep 18 '13 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 Sep 18 '13 at 20:06

1 Answers1

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);

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 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. – Michael Jun 15 '14 at 15:51
  • @Michael, read the first paragraph of this manual page: http://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. – Bill Karwin Jun 15 '14 at 16:53