Limit a MySQL query to an even number of results

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 MySQLi?

Answer

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;
    }
}