What is the php equivalent of @row:=@row+1

I have a MySQL statement that works successfully within the SQL database playground. It queries and returns every 10th row in my location_values table irrespective of any gaps in the ID’s.

It looks like this:

MySQL

set @row:=-1;

SELECT location_values.*
FROM
    location_values
    INNER JOIN
    (
        SELECT id
        FROM
            (
                SELECT @row:=@row+1 AS rownum, id 
                FROM
                    (
                        SELECT id FROM location_values ORDER BY id
                    ) AS sorted
            ) as ranked
        WHERE rownum % 10 = 0
    ) AS subset
        ON subset.id = location_values.id

working db-fiddle

Problem: I would like to convert this statement above and run it in a PHP query instead but I am having problems doing so.

As it currently stands my custom PHP function continues to print the entire table as opposed to every 10th row as per the MySQL statement. I suspect due to not interpreting set @row:=-1; and @row:=@row+1 within PHP.

My PHP function

function get_incidents() {

$row = -1;

$query = query("

SELECT location_values.*
FROM
    location_values
    INNER JOIN
    (
        SELECT id
        FROM
            (
                SELECT $row + 1 AS rownum, id 
                FROM
                    (
                        SELECT id FROM location_values ORDER BY id
                    ) AS sorted
            ) as ranked
        WHERE rownum % 10 = 0
    ) AS subset
        ON subset.id = location_values.id");


confirm($query); //passes in the global $connection;



while ($row = fetch_array($query)) {


$incidents = <<<DELIMETER



<tr>

    <td>{$row['id']}</td>
    <td>{$row['name']}</td>
    <td>{$row['lat']}</td>
    <td>{$row['lng']}</td>


</tr>



DELIMETER;

echo $incidents;


} // end of while loop    





} // end of function 

I am newish to PHP and would be grateful for any steering as to how best refactor my PHP function to achieve my goal of returning every 10th row irrespective of gaps in the ID’s.

Answer

MySQL >8.0

You can achieve the same task using a window function. If I am not mistaken the correct way to do it would be as follows:

SELECT t.* FROM (SELECT *, Row_number() OVER() AS rn FROM location_values) t 
WHERE t.rn % 10 = 1

https://www.db-fiddle.com/f/tb2nLZ6dkDPaQxkc6My9Yg/2

MySQL <8.0

You can emulate the window function using session property, but this is not always reliable.

Method 1 (can only be executed once; don’t recommend):

SELECT t.*
FROM
    (SELECT *, @position:=((SELECT ifnull(@position, 0)) + 1) AS rn FROM location_values) t
WHERE t.rn % 10 = 1

Method 2:

SELECT location_values.*
FROM location_values,
    (SELECT @row_number:=0) AS temp
WHERE (@row_number:=@row_number + 1) % 10 = 1

https://www.db-fiddle.com/f/tb2nLZ6dkDPaQxkc6My9Yg/3