我有以下MySQL查询;
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
生成下表;
Users Balance @rownum := 0 @rownum := @rownum + 1
User1 5.88 0 1
User2 -23.41 0 2
如何回显第一个用户最后一列的值(其中 [@rownum := @rownum + 1] = 1。
谢谢
$result = mysql_query("your's sql query");
$row = mysql_fetch_array($result);
echo $row[2];
这是针对您的特定问题的代码。阅读更多关于这里的信息。整个扩展已被弃用,因此为了您的家庭作业,请阅读此内容并重写我的代码。
您需要使用 php 中的 mysqli_* 函数来连接和执行查询。执行查询后,检索第一行,然后回显第三列。
这样的事情应该可以工作(将 mysql 参数更改为主机的有效用户名/名称密码(:
<?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();
?>