Advertisement
Guest User

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

a guest
Feb 26th, 2012
59
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.16 KB | None | 0 0
  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);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement