user6645308 user6645308 - 4 months ago 7
PHP Question

MYSQL Index as a variable in a query

I'm trying to get a criteria from 2 Columns and indexing them using this query

$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);


Is there is a way in which I can use the row_index as a variable in the query, like this:

$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, $query1);

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

Answer

You can test for the $ID in a `HAVING clause:

$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, $query1);

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

There is no need to concatenate the variable in the query. If $ID is an integer there is no need for quotes and if it is alpha-numeric just enclose it in single-quotes as PHP will interpolate the variable correctly.

Comments