How to Limit Number of Items per Row in While Loop using PHP/MySQLi

Submitted by: 
Language: 
Visitors have accessed this post 419 times.

In this tutorial, I'm going to show you how to limit number of item per row in while loop from MySQL Table using PHP/MySQLi. This tutorial is beneficial when you are presenting large number of items especially those that have images.

Creating our Database

First step is to create our database.
1. Open phpMyAdmin.
2. Click databases, create a database and name it as "sample".
3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.

  1. CREATE TABLE `user` (
  2. `userid` INT(11) NOT NULL AUTO_INCREMENT,
  3. `firstname` VARCHAR(30) NOT NULL,
  4. `lastname` VARCHAR(30) NOT NULL,
  5. PRIMARY KEY(`userid`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

limit

Inserting Sample Data into our Database

Next is to insert sample data into the database that we have created. We are going to use this when we select our data.
1. Click "sample" database that we have created.
2. Click SQL and paste the code below.

  1. INSERT INTO `user` (`userid`, `firstname`, `lastname`) VALUES
  2. (1, 'neovic', 'devierte'),
  3. (2, 'lee', 'ann'),
  4. (3, 'julyn', 'divinagracia'),
  5. (4, 'tintin', 'demapanag'),
  6. (5, 'dee', 'tolentino'),
  7. (6, 'jaira', 'jacinto'),
  8. (7, 'jason', 'flor'),
  9. (8, 'queen', 'melicor'),
  10. (9, 'joy', 'rael'),
  11. (10, 'faith', 'rael'),
  12. (11, 'roy', 'butron'),
  13. (12, 'leah', 'flores'),
  14. (13, 'jub', 'limsiaco'),
  15. (14, 'janna', 'atienza'),
  16. (15, 'desire', 'osorio'),
  17. (16, 'rox', 'infante');

Creating our Sample Table

Last step is to create our sample table with or limiting code. Also, for you to understand the code faster, I've created 2 sample tables.

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <title>How to Limit Number of Items per Row from MySQL Table in PHP/MySQLi</title>
  5. </head>
  6. <body>
  7. <h2>Limit By 4 per row</h2>
  8. <table border=1>
  9. <thead>
  10. <th>Name</th>
  11. <th>Name</th>
  12. <th>Name</th>
  13. <th>Name</th>
  14. </thead>
  15. <tbody>
  16. <?php
  17. $conn = mysqli_connect("localhost", "root", "", "sample");
  18.  
  19. if (!$conn) {
  20. die("Connection failed: " . mysqli_connect_error());
  21. }
  22.  
  23. $inc=4;
  24. $query=mysqli_query($conn,"select * from user");
  25. while($row=mysqli_fetch_array($query)){
  26. $inc = ($inc == 4) ? 1 : $inc+1;
  27. if($inc == 1) echo "<tr>";
  28.  
  29. ?>
  30. <td><?php echo $row['firstname']; ?> <?php echo $row['lastname']; ?></td>
  31.  
  32. <?php
  33. if($inc == 4) echo "</tr>";
  34. }
  35. if($inc == 1) echo "<td></td><td></td><td></td></tr>";
  36. if($inc == 2) echo "<td></td><td></td></tr>";
  37. if($inc == 3) echo "<td></td></tr>";
  38. ?>
  39. </tbody>
  40. </table>
  41.  
  42. <h2>Limit By 3 per row</h2>
  43. <table border=1>
  44. <thead>
  45. <th>Name</th>
  46. <th>Name</th>
  47. <th>Name</th>
  48. </thead>
  49. <tbody>
  50. <?php
  51. $conn = mysqli_connect("localhost", "root", "", "sample");
  52.  
  53. if (!$conn) {
  54. die("Connection failed: " . mysqli_connect_error());
  55. }
  56.  
  57. $inc=3;
  58. $query=mysqli_query($conn,"select * from user");
  59. while($row=mysqli_fetch_array($query)){
  60. $inc = ($inc == 3) ? 1 : $inc+1;
  61. if($inc == 1) echo "<tr>";
  62.  
  63. ?>
  64. <td><?php echo $row['firstname']; ?> <?php echo $row['lastname']; ?></td>
  65.  
  66. <?php
  67. if($inc == 3) echo "</tr>";
  68. }
  69. if($inc == 1) echo "<td></td><td></td></tr>";
  70. if($inc == 2) echo "<td></td></tr>";
  71. ?>
  72. </tbody>
  73. </table>
  74. </body>
  75. </html>

And that ends our tutorial. Hoping that this might be useful to someone. Happy coding :)


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe>
  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.