Different result posting sql query through php than through phpmyadmin

Question

Sorry if it's been asked before but I think this might be very specific to the query..I'm very new to both PHP and SQL but got a high scores system working and now stuck on one last thing...I have a query that if I paste into phpmyadmin it works fine, but when included between $sql ="" (in PHP) it doesn't. In a vain attempt to debug it calling via a browser I can see the offending item just can't see why, in PHP:

$sql = "SET @rownum := 0; SELECT * FROM (SELECT @rownum := @rownum+1 AS rank, ID, Username, Score, UDID FROM users ORDER BY Score DESC)
AS derived_table WHERE Username = 'Dave';";

This returns nothing...but paste it into phpmyadmin and it works fine, returns two entries sorted by score and displaying their rank.

If I remove SET @rownum := 0; then it works but returns null for rank...so I guess the ; is causing it to terminate?

Any ideas would be a big help before I pull the last piece of hair out.

Without your PHP code, it's hard to tell, but I'm going to guess you may not be using mysqli_multi_query. The semi-colon means it's the end of a statement, and regular mysqli/PDO will stop there. Try breaking it up into several statements. — aynber
– aynber, Commented Jan 11, 2017 at 18:34

Community · Accepted Answer · 2023-11-17 20:16:56Z

1

In a regular mysql API in PHP, this is not possible. Try executing your queries separately (SET & SELECT) and use mysqli_multi_query. https://www.php.net/manual/en/mysqli.multi-query.php

edited Nov 17, 2023 at 20:16

CommunityBot

11 silver badge

answered Jan 11, 2017 at 18:36

Roljhon

1,3392 gold badges9 silver badges22 bronze badges

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

2 Comments

user7406099 Over a year ago

That's correct, I suggested it was to do with the ; terminating...and now I've switched to multi query it does work...so that's kind of proved it, however the internet is full of people saying it's very bad practice as it opens you up to sql injection and is to be avoided...any thoughts on this anyone?

Roljhon Over a year ago

Read the security considerations here php.net/manual/en/mysqli.quickstart.multiple-statement.php

Collectives™ on Stack Overflow

Different result posting sql query through php than through phpmyadmin

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