Echo a single result from MySQL query

Question

I have the following MySQL query;

    SELECT *, @rownum := @rownum + 1 from
    (
    SELECT (display_name) 'Author',
        IFNULL(ROUND(SUM(balance.meta_value),2),2) 'Balance'
    FROM posts p
    JOIN users u 
        ON p.post_author = u.ID
    JOIN postmeta odd ON p.ID = odd.post_id AND odd.meta_key = 'odd' AND odd.meta_value >= 1.5
    LEFT JOIN postmeta balance 
        ON p.ID = balance.post_id AND balance.meta_key = 'balance'
    WHERE p.post_status = 'publish' and p.post_author = 'User1' 
    GROUP BY u.ID
    ORDER BY Balance DESC
    )x, (SELECT @rownum := 0) r

which produces the following table;

 Users      Balance       @rownum := 0       @rownum := @rownum + 1
 User1      5.88              0               1
 User2      -23.41            0               2

How can I echo the value of the last column for the first user (where [@rownum := @rownum + 1] = 1.

Thanks

Answer 1

You need to use the mysqli_* functions in php to connect and execute the query. Once the query is executed you retrieve the first row and then echo the 3rd column.

Something like this should work (Change the mysql params to valid user/name passwords for your host):

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT *, @rownum := @rownum + 1 from
    (
    SELECT (display_name) 'Author',
        IFNULL(ROUND(SUM(balance.meta_value),2),2) 'Balance'
    FROM posts p
    JOIN users u 
        ON p.post_author = u.ID
    JOIN postmeta odd ON p.ID = odd.post_id AND odd.meta_key = 'odd' AND odd.meta_value >= 1.5
    LEFT JOIN postmeta balance 
        ON p.ID = balance.post_id AND balance.meta_key = 'balance'
    WHERE p.post_status = 'publish' and p.post_author = 'User1' 
    GROUP BY u.ID
    ORDER BY Balance DESC
    )x, (SELECT @rownum := 0) r";

if ($result = $mysqli->query($query)) {

    /* fetch object array */
    $row = $result->fetch_row(); // Fetch the first row
    echo $row[2]; // Print the 3rd column

    /* free result set */
    $result->close();
}

/* close connection */
$mysqli->close();
?>

Answer 2

$result = mysql_query("your's sql query");
$row = mysql_fetch_array($result);
echo $row[2];

This is code for your specific question. Read more about here. Whole extension is deprecated so for your homework read this and rewrite my code.