MYSQL index as a variable in a query

Question

I am trying to get criteria from 2 columns and index them with 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 a way that I can use row_index as a variable in a query, for example:

$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 1

You can check for $IDin the sentence HAVING:

$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 a variable in a query. If it $IDis an integer, there is no need for quotation marks, and if it is alphanumeric, just enclose it in single quotes, since PHP will correctly interpolate the variable.

---

Link for the HAVING clause

Answer 2

, , - .

SELECT ilv.*
FROM (
   SELECT gift.*, @row_num := @row_num + 1 as row_index 
   FROM gift
   WHERE Category = '0' 
   ORDER BY ID ASC
) ilv
WHERE row_index ='".$ID."';

( ).

....

SELECT *
FROM gift
WHERE Category = '0' 
ORDER BY ID ASC
LIMIT $ID, 1;

, MySQL, MyISAM, . "" . , , , , .....

SELECT *
FROM gift
WHERE category='0'
AND id=($ID * $interval);