php – MYSQL索引作为查询中的变量

发布时间：2020-12-13 17:01:02 所属栏目：PHP教程来源：网络整理

导读：我正在尝试从2列中获取条件并使用此查询对其进行索引 $query1 = "SET @row_num = 0";$query2 = "SELECT *,@row_num := @row_num + 1 as row_index FROM gift WHERE Category = '0' AND ID ='".$ID."' ORDER BY ID ASC;";mysqli_query($conn,$query1);$retrie

我正在尝试从2列中获取条件并使用此查询对其进行索引

$query1 = "SET @row_num = 0";
$query2 = "SELECT *,@row_num := @row_num + 1 as row_index FROM gift 
           WHERE Category = '0' AND ID ='".$ID."'
           ORDER BY ID ASC;";
mysqli_query($conn,$query1);

$retrieve = mysqli_query($conn,$query2);

是否有一种方法可以将row_index用作查询中的变量,如下所示：

$query1 = "SET @row_num = 0";
$query2 = "SELECT *,@row_num := @row_num + 1 as row_index FROM gift
           WHERE Category = '0' AND row_index ='".$ID."'
           ORDER BY ID ASC;";
mysqli_query($conn,$query2);

解决方法

您可以在HAVING子句中测试$ID：

$query1 = "SET @row_num = 0";
$query2 = "SELECT *,@row_num := @row_num + 1 as row_index FROM gift
           WHERE Category = '0' 
           HAVING row_index = $ID
           ORDER BY ID ASC;";
mysqli_query($conn,$query2);

无需在查询中连接变量.如果$ID是一个整数,则不需要引号,如果它是字母数字,只需将其括在单引号中,因为PHP将正确插入变量.

Reference for HAVING clause

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!