Outline ·
[ Standard ] ·
Linear+
PHP How to split mysql data fetching using php, mysql, php and html
TSjayko
|
Apr 29 2021, 08:55 PM, updated 3y ago
|
|
Ok, here is my requirement, I have a simple database that consist of 18 rows, my goal is to create a table that is first display the first 3 numbers in a 3 row tables, then display bank row. and continue fetching my 4th row from database and display it on the 5th row in html. and repeat the same procedures for every 3 row of data, add a blank row in html. how should i go about it? this is my code, but it displayed all the 18 rows at once, without adding the blank row in between. CODE
<!DOCTYPE html> <html lang="en"> <head> <title>Sample PHP Database Application</title> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0-alpha.5/css/bootstrap.min.css"> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0-alpha.5/js/bootstrap.min.js"></script> </head> <body>
<?php //Change the password to match your configuration $link = mysqli_connect("localhost", "root", "", "123");
// Check connection if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } echo "<br>"; $sql = "SELECT * FROM numbers"; $result = $link->query($sql); echo "<div class='container'>"; echo "<div class='row-fluid'>"; echo "<div class='col-xs-6'>"; echo "<div class='table-responsive'>"; echo "<table class='table table-hover table-inverse'>"; echo "<tr>"; echo "<th>Data</th>"; echo "</tr>"; echo "<tr>"; echo "<th>ID</th>"; echo "<th>Number</th>"; echo "</tr>"; if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { echo "<tr>"; echo "<td>" . $row["id"] . "</td>"; echo "<td>" . $row["number"] . "</td>"; echo "</tr>"; } } else { echo "0 results"; } echo "</table>"; echo "</div>"; echo "</div>";
// Close connection mysqli_close($link); ?>
</body> </html>
This post has been edited by jayko: Apr 29 2021, 09:22 PM
|
|
|
|
casablanca2
|
Apr 29 2021, 09:05 PM
|
|
in your while loop, if mod 3 = 0, echo empty row
|
|
|
|
TSjayko
|
Apr 29 2021, 09:21 PM
|
|
QUOTE(casablanca2 @ Apr 29 2021, 09:05 PM) in your while loop, if mod 3 = 0, echo empty row can you write the code here? while($row = $result->fetch_assoc() && %3 == 0) it doesn't work for me. thanks sifu This post has been edited by jayko: Apr 29 2021, 09:30 PM
|
|
|
|
FlierMate
|
Apr 30 2021, 12:43 AM
|
|
QUOTE(jayko @ Apr 29 2021, 09:21 PM) can you write the code here? while($row = $result->fetch_assoc() && %3 == 0) it doesn't work for me. thanks sifu Did you mean this? CODE $counter = 0; if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { if ($counter % 3 == 0) { echo "<tr>"; echo "<td></td>"; echo "<td></td>"; echo "</tr>"; }; echo "<tr>"; echo "<td>" . $row["id"] . "</td>"; echo "<td>" . $row["number"] . "</td>"; echo "</tr>"; $counter++; } } else { echo "0 results"; } https://qrcodeonline.net/jayko/
|
|
|
|
SleepingDragon
|
Apr 30 2021, 01:54 AM
|
Getting Started
|
QUOTE(FlierMate @ Apr 30 2021, 12:43 AM) CODE $counter = 0; if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { if ($counter % 3 == 0) { echo "<tr>"; echo "<td></td>"; echo "<td></td>"; echo "</tr>"; }; echo "<tr>"; echo "<td>" . $row["id"] . "</td>"; echo "<td>" . $row["number"] . "</td>"; echo "</tr>"; $counter++; } } else { echo "0 results"; } https://qrcodeonline.net/jayko/This looks correct. The TS might need to put additional condition in the IF statement inside the while loop to prevent the first blank line (before the first record) from appearing.
|
|
|
|
kevinlim001
|
Apr 30 2021, 08:22 AM
|
|
any reason you want to do like that? 1) for the display purpose because of small screen? 2) to carter for potential performance issue?
If 1) then you might in the right track for eager fetch technique. Just some tuning on the display side code. If 2) your SQL need to be dynamic for the lazy fetch technique. This is to carter potential performance issue if the table consist of large data.
|
|
|
|
TSjayko
|
Apr 30 2021, 03:50 PM
|
|
QUOTE(FlierMate @ Apr 30 2021, 12:43 AM) Did you mean this? CODE $counter = 0; if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { if ($counter % 3 == 0) { echo "<tr>"; echo "<td></td>"; echo "<td></td>"; echo "</tr>"; }; echo "<tr>"; echo "<td>" . $row["id"] . "</td>"; echo "<td>" . $row["number"] . "</td>"; echo "</tr>"; $counter++; } } else { echo "0 results"; } https://qrcodeonline.net/jayko/yes, that is right, thanks shifu, that is what i mean, btw, I didt display the table as the forum itself dont have builtin table feature, but you took the effort to use the qrcodeonline web to display it properly, two thumbs up for your help. thanks again shifu. cheers.
|
|
|
|
TSjayko
|
Apr 30 2021, 03:52 PM
|
|
QUOTE(kevinlim001 @ Apr 30 2021, 08:22 AM) any reason you want to do like that? 1) for the display purpose because of small screen? 2) to carter for potential performance issue? If 1) then you might in the right track for eager fetch technique. Just some tuning on the display side code. If 2) your SQL need to be dynamic for the lazy fetch technique. This is to carter potential performance issue if the table consist of large data. this is just a small db, assignment, not a live production enviroment, so dont worry about the fetching issue, it won't hit that kind of performance issue, thanks for pointing that potential issue, shifu.
|
|
|
|
TSjayko
|
Apr 30 2021, 03:55 PM
|
|
QUOTE(SleepingDragon @ Apr 30 2021, 01:54 AM) This looks correct. The TS might need to put additional condition in the IF statement inside the while loop to prevent the first blank line (before the first record) from appearing. what condition need to be specificied for that if statement? is it this ($counter > 0) && ($counter % 3 == 0)?
|
|
|
|
SleepingDragon
|
May 1 2021, 02:42 AM
|
Getting Started
|
QUOTE(jayko @ Apr 30 2021, 03:55 PM) what condition need to be specificied for that if statement? is it this ($counter > 0) && ($counter % 3 == 0)? Yes. Assuming the syntax is correct.
|
|
|
|
Grammar Police
|
May 2 2021, 11:11 AM
|
|
if you dont want to use #2 method
you can just create a variable $n to to remember where you are
$n =+ 1 for every loop when $n hits 3, add your empty row and reset it to 0
|
|
|
|
TSjayko
|
May 3 2021, 08:47 PM
|
|
QUOTE(SleepingDragon @ May 1 2021, 02:42 AM) Yes. Assuming the syntax is correct. alright, got it, thanks for your input!
|
|
|
|
TSjayko
|
May 3 2021, 08:48 PM
|
|
QUOTE(Grammar Police @ May 2 2021, 11:11 AM) if you dont want to use #2 method you can just create a variable $n to to remember where you are $n =+ 1 for every loop when $n hits 3, add your empty row and reset it to 0 sure, it is simple way of remember the row count. thanks.
|
|
|
|