Limit a MySQL query to an even number of results (using PHP and mysqli)

Question

Hi,

I have a bit of PHP code which I need to return an even number of results from a MySQL database. I'm using the mysqli extension to execute my query.

My code is approximately this at the moment:

//assume we already have a database connection

$query = "SELECT id 
            FROM movies 
           WHERE publish = 1
             AND showimage = 1
        ORDER BY date DESC
           LIMIT 6";

$result = $connection->query($query);

while ($row = $result->fetch_assoc()) {
    //do some stuff
}

As you can see, I'm limiting the query to 6 rows, but in some conditions, fewer will be returned. If only 3 rows are returned, I want to throw away the last row and only keep 2.

How can I do this in the MySQL query or in mysli?

Thanks in advance.

Answer 1

I would probably do this in PHP, rather than SQL. In the while-loop, keep a counter, and when you get out of the loop, check if the counter. If it's odd, throw away the results of the last iteration.

Answer 2

I'd imagine something like this:

// row counter
$counter = 1;
// loop through each row
while($row = $result->fetch_assoc()) {
    // If there is more than one row remaining 
    // OR if the current row can be divided by two
    if (($result->num_rows - $counter) > 1 || ($counter % 2)) {
        // result code for even rows
        $counter++;
    } else {
        // break out of the loop
        break;
    }
}

Answer 3

Another option is asking for a COUNT and alter the query according to the result:

SELECT COUNT(*) FROM movies WHERE publish = 1 AND showimage = 1

Answer 4

I'm not a PHP person (its been years since I've looked at it) but....

Ross mentions it but you want to use

$result->num_rows

in some manner within your loop that processes the results to keep an even number of rows.

Answer 5

$maxResults = $result->num_rows; if( ($maxResults % 2) = 1) $maxResults--;

When counter is same as $maxResults, break out of the loop.

Answer 6

My answer is mysql only, although probably not the best, but maybe kind of cool. :-)

SELECT size INTO @count FROM (SELECT COUNT(*) as size FROM (SELECT * FROM table LIMIT 6) l) t;
SET @count = @count - (@count % 2);
PREPARE stmt_limit FROM 'SELECT * FROM table LIMIT ?';
EXECUTE stmt_limit USING @count;
DEALLOCATE PREPARE stmt_limit;

Steps:

1. In the first statement i put how many results you've really got in the @count variable
2. In the second statement i decrement the variable with one if it's uneven
3. Prepare statement with your query and placeholder for limit
4. Execute the baby
5. Deallocate, don't know if this is necessary

But who knows, this is maybe faster then the php solutions...

Answer 7

The simplest way would be to just grab the rows two at a time:

while($row1 = $result->fetch_assoc() && $row2 = $result->fetch_assoc()) {
    do_something_with($row1);
    do_something_with($row2);
}

(Not a PHP person, but cargo-culting the syntax from the other answers ...)

Answer 8

Sharkey,

Your solution will work perfectly but you need to modify the syntax a little. See below.

while($row1 = $result->fetch_assoc()) && ($row2 = $result->fetch_assoc())) {

do_something_with($row1); 
do_something_with($row2); 

}

The other way your second row will cancell out the first row and won't show the first. Now it will display both results. Nice simple solution, BTW.