0

I'm trying to do 2 things.

1) Get the amount of rows in this query

SELECT
    COUNT(*)
FROM
    `my_table`
WHERE
    `column_1` = 152
AND
    `column_2` = 42
ORDER BY
    `column_3`

As you can see that is no problem ^^

2) Determine the number within the range of rows that is returned by id

Ex: ID 765 is Item 4 of 7 where column_1 = 152 and column_3 = 42

Does anyone have any basic solutions to this problem with almost pure MySQL? I'd like to avoid iterating through all the rows and setup a counter to increment until it matches current id like this:

$sql = '
    SELECT
        *
    FROM
        `my_table`
    WHERE
        `column_1` = 152
    AND
        `column_2` = 42
    ORDER BY
        `column_3`
';
$query = mysqli_query($sql);

$current_id = 2523;
$i = 1;

while ($row = mysqli_fetch_assoc($query)) {
    if ($row['id'] == $current_id) {
        $current_position = $i;
    }
    $i++;
}

print 'Current position in range is: '. $current_position;

Also please don't worry about the actual syntax, I won't be using this exact script, but you get the logic that I'd like to avoid using. If anyone has a better solution, please let me know. Thanks in advance!!

Armin
  • 1,736
  • 4
  • 19
  • 35
  • Is [this](http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) what you're looking for? – mister martin Apr 29 '14 at 21:53
  • When I have done pagination in PHP with mySql I generally use the LIMIT portion of the SQL statement to give the starting row and max row count. Is that what you are trying to do? – dboals Apr 29 '14 at 23:39

0 Answers0