using php to display mysql table correctly on html

lawnmowerman · Apr 29, 2021

Ok man, here is my requirement, I have a simple database that consist of 9 rows, my goal is to create a table that is first display the first 3 numbers in a 3 row tables, then display a blank row. and continue fetching my 4th row from database and display it on the 5th row in html. and repeat the same procedures. how should i go about it?

the table should display like this.

Data

ID

Number

1	xxxxx
2	xxxxx
3	xxxxx

4	xxxxx
5	xxxxx
6	xxxxx

7	xxxxx
8	xxxxx
9	xxxxx

but my code below is only display everything in one table without the blank row to seperate the rows in between.

HTML:

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

@davidktw , can help me on this? thanks.

davidktw · Apr 29, 2021

In modern web technologies where Ajax is available to you, all you need to do is have your first page simply display your main makeup or layout of your table, which could be index.php
Then populate your table in a dynamic manner, simply firing Ajax web requests say

Code:

/get_data.php?offset=<starting_id>&limit=<no_of_rows>

Your get_data.php basically is a controller that take in the 2 query parameters and then issue an SQL statement like this

SQL:

SELECT * FROM data_table t WHERE t.id > [starting_id] ORDER BY t.id LIMIT [no_of_rows]

get_data.php may return a json data that is in the following form

JSON:

{
  result: "OK",
  data: [
    {
        id: 1,
        number: ...
    }, {
        id: 2,
        number: ...
    }, {
        id: 3,
        number: ...
    },
  ]
}

For the first 3 rows, the request will be

Code:

/get_data.php?offset=0&limit=3

Since you got back the last record has an "id" value return, store it in a global variable and use it for the next Ajax call, thus the 2nd call will be

Code:

/get_data.php?offset=3&limit=3

3rd call will be

Code:

/get_data.php?offset=6&limit=3

This technique will be flexible for any number of rows per request you wish, and the offset is flexible depending on what is returned.

Building the table is very simply using table API

https://www.w3schools.com/jsref/met_table_insertrow.asp
But nothing stop you from using simple DOM building techniques like this

JavaScript:

let table = document.createElement('table');
let tbody = document.createElement('tbody');
let row   = document.createElement('tr');
table.appendChild(tbody);
tbody.appendChild(row);

Or it also be

JavaScript:

let table = document.createElement('table');
table.innerHTML = "<thead><td>ID</td><td>NUMBER</td></thead><tbody></tbody>";
let body = table.querySelector('tbody');
let row = body.insertRow(0);
let col1 = row.insertCell(0);
let col2 = row.insertCell(1);
col1.innerHTML = "1";
col2.innerHTML = "xxxxxx";

lawnmowerman · Apr 29, 2021

davidktw said:
In modern web technologies where Ajax is available to you, all you need to do is have your first page simply display your main makeup or layout of your table, which could be index.php
Then populate your table in a dynamic manner, simply firing Ajax web requests say

Code:

/get_data.php?offset=<starting_id>&limit=<no_of_rows>

Your get_data.php basically is a controller that take in the 2 query parameters and then issue an SQL statement like this

SQL:

SELECT * FROM data_table t WHERE t.id > [starting_id] ORDER BY t.id LIMIT [no_of_rows]

get_data.php may return a json data that is in the following form

JSON:

{ result: "OK", data: [ { id: 1, number: ... }, { id: 2, number: ... }, { id: 3, number: ... }, ] }

For the first 3 rows, the request will be

Code:

/get_data.php?offset=0&limit=3

Since you got back the last record has an "id" value return, store it in a global variable and use it for the next Ajax call, thus the 2nd call will be

Code:

/get_data.php?offset=3&limit=3

3rd call will be

Code:

/get_data.php?offset=6&limit=3

This technique will be flexible for any number of rows per request you wish, and the offset is flexible depending on what is returned.

Building the table is very simply using table API

https://www.w3schools.com/jsref/met_table_insertrow.asp

davidktw, thanks man for your prompt response, I am picking the php method, because it the language that i am familiar with, and this is the basic table I am trying out, based on this basic block, I have to style the table with non uniform row and column, so you are suggesting AJAX is better language for this type of job? gotta have a quick look at AJAX, never learn it before.

btw, for my current PHP case, is making the while loop to take in mod 3 check a good enough workaround? thanks again.

davidktw · Apr 29, 2021

I am basically exposing you to the realm of web technologies out there at their fundamentals. You can pick them up and try all sorts of way to do what you need to do.

If you are asking for the year 2000 earlier days of using PHP, then basically each time you refresh the same page, you need to fetch more and more data to populate and recreate the entire page

First time, the SQL statement will be

SQL:

SELECT * FROM t ORDER BY t.id LIMIT 3

2nd time will be

SQL:

SELECT * FROM t ORDER BY t.id LIMIT 6

3rd time will be

SQL:

SELECT * FROM t ORDER BY t.id LIMIT 9

How you remember the number of rows to fetch from the database from one refresh to another, can be either
remembered using a SESSION variable at the server side, or simply carried over from one web request to another via the query parameter or even the cookie works

lawnmowerman · Apr 29, 2021

Ok, time to pick up new language now, If I wanna use bootstrap formatting table, I can't be using the dom or js formatting right?

davidktw · Apr 29, 2021

lawnmowerman said:
Ok, time to pick up new language now, If I wanna use bootstrap formatting table, I can't be using the dom or js formatting right?

Bootstrap is merely a styling framework. How you create your DOM is a different topic. The styling applied to your document will apply as accordingly as long as you built the DOM structure that way the style is to be applied.

lawnmowerman · Apr 29, 2021

davidktw said:
I am basically exposing you to the realm of web technologies out there at their fundamentals. You can pick them up and try all sorts of way to do what you need to do.

If you are asking for the year 2000 earlier days of using PHP, then basically each time you refresh the same page, you need to fetch more and more data to populate and recreate the entire page

First time, the SQL statement will be

SQL:

SELECT * FROM t ORDER BY t.id LIMIT 3

2nd time will be

SQL:

SELECT * FROM t ORDER BY t.id LIMIT 6

3rd time will be

SQL:

SELECT * FROM t ORDER BY t.id LIMIT 9

How you remember the number of rows to fetch from the database from one refresh to another, can be either
remembered using a SESSION variable at the server side, or simply carried over from one web request to another via the query parameter or even the cookie works

yeah, I know there is a limit way of accessing the mysql, but this is too old school, and leceh, the accessing of the row has to be flexible (it is not always in multiple of 3), I will check out the ajax method from scratch now. come up here again after I try changing my code using ajax. thanks. man

davidktw · Apr 29, 2021

BTW I may have misunderstood your earlier question. I thought you need some form of way to slowly build your table. What you want is a visual separation.

In that case, just do what you are doing, fetching the entire result set from the database, then use your "mod 3" approach and insert the blank row

PHP:

echo "<tr colspan=2><td>&nbsp;</td></tr>"

when the "row % 3 == 0" is true.

lawnmowerman · Apr 29, 2021

davidktw said:
Bootstrap is merely a styling framework. How you create your DOM is a different topic. The styling applied to your document will apply as accordingly as long as you built the DOM structure that way the style is to be applied.

yes, i understand the bootstrap css. I am using bootstrap because I have the layout that I like in my web project. I have to study the DOM, js stuff from scrath now. thanks man.

the bootstrap i m using is this.
/**
* Template Name: Eterna - v2.2.1
* Template URL: https://bootstrapmade.com/eterna-free-multipurpose-bootstrap-template/
* Author: BootstrapMade.com
* License: https://bootstrapmade.com/license/
*/

davidktw · Apr 29, 2021

What I have recommended to you are still relevant, but those could be something more advance in comparison. These techniques are more network friendly, especially when your table could be super large.

lawnmowerman · Apr 29, 2021

davidktw said:
What I have recommended to you are still relevant, but those could be something more advance in comparison. These techniques are more network friendly, especially when your table could be super large.

while($row = $result->fetch_assoc()) {

echo "<tr>";
echo "<td>" . $row["id"] . "</td>";
echo "<td>" . $row["number"] . "</td>";
echo "</tr>";
}

where do I fit in the modulos check in this while loop? is it a inner loop?
when the "row % 3 == 0" is true.

my table is not super big, it is just 3 column max, and 15 row max.

lawnmowerman · Apr 29, 2021

davidktw said:
BTW I may have misunderstood your earlier question. I thought you need some form of way to slowly build your table. What you want is a visual separation.

In that case, just do what you are doing, fetching the entire result set from the database, then use your "mod 3" approach and insert the blank row

PHP:

echo "<tr colspan=2><td> </td></tr>"

when the "row % 3 == 0" is true.

not it is slowly displaying the table, it is one shot display once it is load, btw could show the code of modulus 3 inside the while loop? I can't get it right, I did double while loop, it doesnt work. i got this error

Parse error: syntax error, unexpected token ">" at this line echo "<tr colspan=2><td> </td></tr>"

Code:

while($row = $result->fetch_assoc()) {
  
     echo "<tr>";
     echo "<td>" . $row["id"] . "</td>";
     echo "<td>" . $row["number"] . "</td>";
     echo "</tr>";
    while (row % 3 == 0){
               echo "<tr colspan=2><td>&nbsp;</td></tr>"
    }
}

davidktw · Apr 29, 2021

lawnmowerman said:
not it is slowly displaying the table, it is one shot display once it is load, btw could show the code of modulus 3 inside the while loop? I can't get it right, I did double while loop, it doesnt work.

Code:

while($row = $result->fetch_assoc()) { echo "<tr>"; echo "<td>" . $row["id"] . "</td>"; echo "<td>" . $row["number"] . "</td>"; echo "</tr>"; while (row % 3 == 0){ echo "<tr colspan=2><td> </td></tr>" } }

Not while, it is “if (row_id % 3 == 0)”

Not I am giving you is not exact answer, go appreciate the idea and decide what you want to use to calculate the modulo, use the ID value from the database, or use your own counter that you create to count the number of rows written in each iteration

lawnmowerman · Apr 29, 2021

davidktw said:
Not while, it is “if (row_id % 3 == 0)”

Not I am giving you is not exact answer, go appreciate the idea and decide what you want to use to calculate the modulo, use the ID value from the database, or use your own counter that you create to count the number of rows written in each iteration

got it, trying it now, row_id is better choice. thanks man!

lawnmowerman · Apr 29, 2021

davidktw said:
Not while, it is “if (row_id % 3 == 0)”

Not I am giving you is not exact answer, go appreciate the idea and decide what you want to use to calculate the modulo, use the ID value from the database, or use your own counter that you create to count the number of rows written in each iteration

cool man, it works now with this code. thanks. i am trying other table manipulating tricks. will ask you here again tomorrow if I need any helps. good nite. cheers.

Code:

while($row = $result->fetch_assoc()) {
                            
                        echo "<tr>";
                        echo "<td>" . $row["id"] . "</td>";
                        echo "<td>" . $row["number"] . "</td>";
                        echo "</tr>";       
                        if ($row["id"] % 3 == 0){
                            echo "<tr colspan=2><td>&nbsp;</td></tr>";
                        }

davidktw · Apr 29, 2021

lawnmowerman said:
cool man, it works now with this code. thanks. i am trying other table manipulating tricks. will ask you here again tomorrow if I need any helps. good nite. cheers.

Code:

while($row = $result->fetch_assoc()) { echo "<tr>"; echo "<td>" . $row["id"] . "</td>"; echo "<td>" . $row["number"] . "</td>"; echo "</tr>"; if ($row["id"] % 3 == 0){ echo "<tr colspan=2><td> </td></tr>"; }

Sure

Feel free to read up on using CSS table styling to create table too.
https://css-tricks.com/complete-guide-table-element/

lawnmowerman · Apr 29, 2021

davidktw said:
Sure
Feel free to read up on using CSS table styling to create table too.
https://css-tricks.com/complete-guide-table-element/

Got it, definately checkout this reading! thanks for saving my days.
it was a confusing code for me in the first place as I mixed up php interacting with mysql database at the same time have to take care of the css style format. so now I already understand how do I control the table formatting and can decide what at which row i can add in row using the if row id statement, it is good now,
I need to do table manipulation and formatting, under while loop using if statement and display necessary label with td and tr.
get back on this thread tomorrow. cheers. thanks.

lilycll · Apr 29, 2021

lawnmowerman said:

Ok man, here is my requirement, I have a simple database that consist of 9 rows, my goal is to create a table that is first display the first 3 numbers in a 3 row tables, then display a blank row. and continue fetching my 4th row from database and display it on the 5th row in html. and repeat the same procedures. how should i go about it?

the table should display like this.

Data

ID

Number

1	xxxxx
2	xxxxx
3	xxxxx

4	xxxxx
5	xxxxx
6	xxxxx

7	xxxxx
8	xxxxx
9	xxxxx

but my code below is only display everything in one table without the blank row to seperate the rows in between.

HTML:

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

@davidktw , can help me on this? thanks.

You can add few lines inside the while($row...) loop immediately at the start of the while loop:

IF ($row % 3 == 0) {
echo "<tr><td colspan=2>dummy empty line</td></tr>"

You should avoid using the database $row['id'] for the mod check as the value may not necessary to be in running sequence.

With combination of CSS you can adjust the height of the row.
}

lawnmowerman · Apr 30, 2021

lilycll said:
You can add few lines inside the while($row...) loop immediately at the start of the while loop:

IF ($row % 3 == 0) {
echo "<tr><td colspan=2>dummy empty line</td></tr>"

You should avoid using the database $row['id'] for the mod check as the value may not necessary to be in running sequence.

With combination of CSS you can adjust the height of the row.
}

I see your method is always another way of displaying the table. I use the row Id to add the empty row because the row id is fix and is always in running order. It is good now. Thanks for your suggestions. Cheers.

lilycll · Apr 30, 2021

lawnmowerman said:
I see your method is always another way of displaying the table. I use the row Id to add the empty row because the row id is fix and is always in running order. It is good now. Thanks for your suggestions. Cheers.

In real world, the ID column in the database may not necessary be in running order, due to unforseen database issue or other reasons. Another possibility could be someone might have deleted a particular row data intentionally, accidentally or for whatever reason. For example, try delete row 3 with ID 3 and you will see the effect.

One work around is to include the MySQL (if this is the database you are using) row_number() in your SQL query statement and use that sequential value. example:
SELECT row_number(), ID, data FROM mytable

using php to display mysql table correctly on html

Banned

Arch-Supremacy Member

Banned

Arch-Supremacy Member

Banned

Arch-Supremacy Member

Banned

Arch-Supremacy Member

Banned

Arch-Supremacy Member

Banned

Banned

Arch-Supremacy Member

Banned

Banned

Arch-Supremacy Member

Banned

Senior Member

Banned

Senior Member