This week only. Pastebin PRO Accounts Christmas Special! Don't miss out!Want more features on Pastebin? Sign Up, it's FREE!
Guest

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

By: a guest on Feb 26th, 2012  |  syntax: None  |  size: 1.16 KB  |  views: 32  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. //assume we already have a database connection
  2.  
  3. $query = "SELECT id
  4.             FROM movies
  5.            WHERE publish = 1
  6.              AND showimage = 1
  7.         ORDER BY date DESC
  8.            LIMIT 6";
  9.  
  10. $result = $connection->query($query);
  11.  
  12. while ($row = $result->fetch_assoc()) {
  13.     //do some stuff
  14. }
  15.        
  16. // row counter
  17. $counter = 1;
  18. // loop through each row
  19. while($row = $result->fetch_assoc()) {
  20.     // If there is more than one row remaining
  21.     // OR if the current row can be divided by two
  22.     if (($result->num_rows - $counter) > 1 || ($counter % 2)) {
  23.         // result code for even rows
  24.         $counter++;
  25.     } else {
  26.         // break out of the loop
  27.         break;
  28.     }
  29. }
  30.        
  31. $result->num_rows
  32.        
  33. $result->num_rows
  34.        
  35. SELECT size INTO @count FROM (SELECT COUNT(*) as size FROM (SELECT * FROM table LIMIT 6) l) t;
  36. SET @count = @count - (@count % 2);
  37. PREPARE stmt_limit FROM 'SELECT * FROM table LIMIT ?';
  38. EXECUTE stmt_limit USING @count;
  39. DEALLOCATE PREPARE stmt_limit;
  40.        
  41. while($row1 = $result->fetch_assoc() && $row2 = $result->fetch_assoc()) {
  42.     do_something_with($row1);
  43.     do_something_with($row2);
  44. }
  45.        
  46. do_something_with($row1);
  47. do_something_with($row2);
clone this paste RAW Paste Data