I need the 'position (row)' from an SQL table using my PHP script. I tested my SQL statement and it worked fine. The output is correct.
My SQL statement:
SET @rownum = 0;
SELECT position, name,cash FROM (SELECT name, cash, @rownum := @rownum + 1 AS position FROM 'my_DB'.'Cash' ORDER BY cash DESC ) AS t WHERE name = 'user44';
Output:
"position":44,"name":"user44","cash":"5600"
But if i put it in PHP:
$query="SET @rownum = 0; SELECT position, name,cash FROM (SELECT name, cash, @rownum := @rownum + 1 AS position FROM 'my_DB'.'Cash' ORDER BY cash DESC ) AS t WHERE name = 'user44';";
it shows me a errant query
.
I also tried something like this:
$query="SELECT position, name,cash FROM (SELECT name, cash, @rownum := @rownum + 1 AS position FROM 'my_DB'.'Cash' ORDER BY cash DESC ) AS t WHERE name = 'user44';";
Output:
"position":null,"name":"user44","cash":"5600"
Thanks to VolkerK, i solved that Problem with:
$query = "SET @rownum =0;";
$result = mysql_query($query,$link) or die('Errant query: '.$query);
$query="SELECT position, name,cash FROM (SELECT name, cash, @rownum := @rownum + 1 AS position FROM 'my_DB'.'HSCash' ORDER BY cash DESC ) AS t WHERE name = 'user44'";
$result = mysql_query($query,$link) or die('Errant query: '.$query);