Display all MySQL table data in html table

Question

I am trying to display a whole mysql table in an html table. So far I have found the below code which works to display fields:

<?php
   $con=mysqli_connect("example.com","peter","abc123","my_db");
   // Check connection
   if(mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
   }

   $result = mysqli_query($con,"SELECT * FROM Persons");

   echo "<table border='1'>
      <tr>
         <th>Firstname</th>
         <th>Lastname</th>
      </tr>";

   while($row = mysqli_fetch_array($result)) {
      echo "<tr>";
      echo "<td>" . $row['FirstName'] . "</td>";
      echo "<td>" . $row['LastName'] . "</td>";
      echo "</tr>";
   }
   echo "</table>";

   mysqli_close($con);
?>

Is there a way that I can display all columns/rows without having to type all the column names as

echo "<td>" . $row['FirstName'] . "</td>";

Do check the example link in my answer. In addition a message to the down-voter... http://php.net/manual/en/mysqli-result.fetch-array.php do try the code before down-voting... — , Jun 01 '16 at 16:56
As i see there are three idiotic down-voters. Ok maybe a complete example will do the work. Oh in addition thanks to my down-voters for the many comments that explain the fault in my answer. — , Jun 01 '16 at 17:05
if you did not down-vote my friend no need to say sorry, if you did... please try to read the answer first. I always try to put a fiddle or an example here to show that the answer works. — , Jun 01 '16 at 17:12
Oh no I didn't downvote anything, I simply wasn't sure what your comment meant.... I understand what you mean now. — Shane, Jun 01 '16 at 17:15
when i wrote the first comment there where three down-votes. Anyway i don't see how can i share a phpfiddle with mysql example since you must have an account there to save code examples. So... i will post the demo code i used in phpfiddle, do try it if you wish. I think my anwer covers what you want in two lines of code. — , Jun 01 '16 at 17:23
In case the column names have to be on top of the table there is no need to post an answer when on is present already http://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names — , Jun 01 '16 at 17:35

Mojtaba · Accepted Answer · 2016-06-01T17:00:14.687

2

You can do it if you are going to show all columns. Or, you can add your filters conditional.

However, as it seems you are new to web development, I make it in a simple way.

<?php
$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM Persons");

echo "<table border='1'>";

$i = 0;
while($row = $result->fetch_assoc())
{
    if ($i == 0) {
      $i++;
      echo "<tr>";
      foreach ($row as $key => $value) {
        echo "<th>" . $key . "</th>";
      }
      echo "</tr>";
    }
    echo "<tr>";
    foreach ($row as $value) {
      echo "<td>" . $value . "</td>";
    }
    echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

edited Jun 01 '16 at 17:00

answered Jun 01 '16 at 16:42

Mojtaba

4,852
5
21
38

@user3092953. instead of your current while – Mojtaba Jun 01 '16 at 16:43
Doh of course! That's great thanks, how about the column names? – Shane Jun 01 '16 at 16:44
Thanks but still showing the columns twice... columns are as follows: `0 id 1 case_id 2 FILE_NAME 3 doc_name` – Shane Jun 01 '16 at 16:51
@user3092953, updated again. use assoc instead of array – Mojtaba Jun 01 '16 at 16:55
That will probably not help @Mojtaba. If he has two rows, it will cycle through the columns twice. You need to change `foreach ($row as $key => $value) {` to `foreach ($row[0] as $key => $value) {` I believe. And of course add some logic to handle a zero row output. – duper51 Jun 01 '16 at 16:58
Thats worked perfectly @Mojtaba - Thank you!!!!! You dont have any idea how I would decrypt each field before displaying it would you? – Shane Jun 01 '16 at 17:01
What do you mean by decrypt? – duper51 Jun 01 '16 at 17:02
@user3092953. Glad to hear that. Please make it as solved. – Mojtaba Jun 01 '16 at 17:03
@duper51 The data in the table is encrypted – Shane Jun 01 '16 at 17:03
@user3092953. I didn't get your sentence. Are you asking me? what kind of encryption? – Mojtaba Jun 01 '16 at 17:05
Anyone that could answer.... I use a salt to encrypt the data before puting it inside the databse, I know how to decrypt data from the DB however not in this code above? – Shane Jun 01 '16 at 17:07
@user3092953, Update your question. Or, ask for it in another post. It's not difficult to solve – Mojtaba Jun 01 '16 at 17:09
Ok will add a new question – Shane Jun 01 '16 at 17:09
@user3092953, Please put the link of the new question here. Also, please mark this question as solved to prevent taking others time. – Mojtaba Jun 01 '16 at 17:11
@Mojtaba: http://stackoverflow.com/questions/37575004/decrypt-data-from-mysql-database – Shane Jun 01 '16 at 17:15

score 1 · Answer 2 · 2016-06-01T17:33:36.630

You can do something like this

while($row = mysqli_fetch_array($result))
{
echo "<tr><td>" . implode("</td><td>",$row) . "</td></tr>";
}

Example

Edit

In order to try this with a database copy-paste the below code to phpfiddle.org workspace (check MySQLi library in the homepage first).

<?php    
/**
 * Mysqli initial code
 * 
 * User permissions of database
 * Create, Alter and Index table, Create view, and Select, Insert, Update, Delete table data
 * 
 * @package         PhpFiddle
 * @link            http://phpfiddle.org
 * @since           2012
*/    
require "util/public_db_info.php";              
$short_connect = new mysqli($host_name, $user_name, $pass_word, $database_name, $port);
$sql = "SELECT * FROM `books` WHERE title='The Martian'";      
$result = $short_connect->query($sql);   
if (($result) && ($result->num_rows > 0))
{
    echo "<table>";
    //convert query result into an associative array
    while ($row = $result->fetch_array())
    {
        echo "<tr><td>" . implode("</td><td>",$row) . "</td></tr>";
    }
    echo "</table>";
    $result->free();   
}  
$short_connect->close();  
?>

The issue is he wants the column name from the database as well. Your method won't work with mysqli_fetch_assoc, or associated methods. — duper51, Jun 01 '16 at 17:15
is this what YOU understand from this `Is there a way that I can display all columns/rows without having to type all the column names as echo "" . $row['FirstName'] . "";` ??? @duper51 — , Jun 01 '16 at 17:29
Additionally it is not a very good security tactic to throw as headers of your table the MySQL table field-names. @duper51, if you are aware of that then maybe it would be good to mention it. — , Jun 01 '16 at 17:32

score 0 · Answer 3 · answered Oct 16 '17 at 21:43

To get a vertical table like this:

PHP Code:

$idProduct = $conexion->real_escape_string($_GET['planId']);
$result = $conexion->query("SELECT * FROM `g4s_products` WHERE id_product = '".$idProduct."'");
if ($result->num_rows > 0) {
    $html = '';
    while ($row = $result->fetch_assoc()) {
        $html .= '<div class="ppalDiv">' .
            '<p class="subtitleDetails"><b>Main Benefits</b></p>' .
            '<p class="textDetails">' . $row["description"] . '</p>' .
            '</div>' .
            '<div class="ppalDiv">' .
            '<p class="subtitleDetails"><b>Benefits Highlights</b></p>';

        echo $html;

        echo "<table>";
        foreach ($row as $key => $value) {
            echo "<tr>";
            echo "<th>" . $key . ":</th>";
            echo "<td>" . $value . "</td>";
            echo "</tr>";
        }

        echo "</table>";
    }
}
mysqli_close($conexion);

CSS code:

table, th, td {
border: 1px solid black;
border-collapse: collapse;

}

th, td {
    padding: 5px;
    text-align: left;
}

And it is done. Enjoy

Display all MySQL table data in html table

3 Answers3

Linked