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

Question!

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.



Answers

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 cancel out the first row and won't show the first. Now it will display both results. Nice simple solution, BTW.

By : Eric


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 ...)

By : NickZoic


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...



This video can help you solving your question :)
By: admin