Show values from a MySQL database table inside a HTML table on a webpage

Question

I want to retrieve the values from a database table and show them in a html table in a page. I already searched for this but I couldn't find the answer, although this surely is something easy (this should be the basics of databases lol). I guess the terms I've searched are misleading. The database table name is tickets, it has 6 fields right now (submission_id, formID, IP, name, email and message) but should have another field called ticket_number. How can I get it to show all the values from the db in a html table like this:

<table border="1">
  <tr>
    <th>Submission ID</th>
    <th>Form ID</th>
    <th>IP</th>
    <th>Name</th>
    <th>E-mail</th>
    <th>Message</th>
  </tr>
  <tr>
    <td>123456789</td>
    <td>12345</td>
    <td>123.555.789</td>
    <td>John Johnny</td>
    <td>[email protected]</td>
    <td>This is the message John sent you</td>
  </tr>
</table>

And then all the other values below 'john'.

Kutzer

It sounds like you're looking for some introductory tutorials in PHP and MySQL. Have you tried any? A Google search for "PHP MySQL tutorial" returns a lot of results (some better than others, but still a lot). – Voracious

Is this what you're after under the "Display the Result in an HTML Table" w3schools.com/php/php_mysql_select.asp – Polybasite

w3schools.com/php/php_mysql_intro.asp – Siderosis

Possibly relevant answer of mine on a later question. – Danziger

Answer 1

Get the data first and then display it later.

<?php
$con = mysqli_connect("localhost","peter","abc123","my_db");
$result = mysqli_query($con,"SELECT * FROM Persons LIMIT 50");
$data = $result->fetch_all(MYSQLI_ASSOC);
?>

<table border="1">
  <tr>
    <th>Firstname</th>
    <th>Lastname</th>
  </tr>
  <?php foreach($data as $row): ?>
  <tr>
    <td><?= htmlspecialchars($row['first_name']) ?></td>
    <td><?= htmlspecialchars($row['last_name']) ?></td>
  </tr>
  <?php endforeach ?>
</table>

Polybasite

How to handle large results where ""SELECT * FROM Persons" will cause either long waits or out of memory? – Kaifeng

@Kaifeng add LIMIT BY clause and return a portion. Use this basis to paginate the results – Polybasite

Thank you @Jonny. will this resume automatically where it left of or i have to keep track of order. – Kaifeng

@Kaifeng you have to track it. Here's a quick google, I haven't gone through the code: code.tutsplus.com/tutorials/… – Polybasite

Would be better if you didn't have to hard code each column name. – Aryan

See w411 3's newer answer for solution that can display result from any query, without having to hardcode column names. – Danziger

Answer 2

Try this: (Completely Dynamic...)

<?php
$host    = "localhost";
$user    = "username_here";
$pass    = "password_here";
$db_name = "database_name_here";

//create connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = mysqli_connect($host, $user, $pass, $db_name);

//get results from database
$result = mysqli_query($connection, "SELECT * FROM products");

//showing property
echo '<table class="data-table">
        <tr class="data-heading">';  //initialize table tag
while ($property = mysqli_fetch_field($result)) {
    echo '<td>' . htmlspecialchars($property->name) . '</td>';  //get field name for header
}
echo '</tr>'; //end tr tag

//showing all data
while ($row = mysqli_fetch_row($result)) {
    echo "<tr>";
    foreach ($row as $item) {
        echo '<td>' . htmlspecialchars($item) . '</td>'; //get items 
    }
    echo '</tr>';
}
echo "</table>";

Katykatya

Answer 3

Here is an easy way to fetch data from a MySQL database using PDO.

define("DB_HOST", "localhost");    // Using Constants
define("DB_USER", "YourUsername");
define("DB_PASS", "YourPassword");
define("DB_NAME", "Yourdbname");

$dbc = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset-utf8mb4", DB_USER, DB_PASS);

$print = ""; // assign an empty string

$stmt = $dbc->query("SELECT * FROM tableName"); // fetch data
$stmt->setFetchMode(PDO::FETCH_OBJ);

$print .= '<table border="1px">';
$print .= '<tr><th>First name</th>';
$print .= '<th>Last name</th></tr>';

while ($names = $stmt->fetch()) { // loop and display data
    $print .= '<tr>';
    $print .= "<td>".htmlspecialchars($names->firstname)."</td>";
    $print .= "<td>".htmlspecialchars($names->lastname)."</td>";
    $print .= '</tr>';
}

$print .= "</table>";
echo $print;

Metz

Answer 4

Learn more about PHP and the MySQLi Library at PHP.net.

First, start a connection to the database. Do this by making all the string variables needed in order to connect, adjusting them to fit your environment, then creating a new connection object with new mysqli() and initializing it with the previously made variables as its parameters. Now, check the connection for errors and display a message whether any were found or not. Like this:

<?php
$servername = "localhost";
$username = "root";
$password = "yourPassword";
$database = "world";
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($servername, $username, $password, $database);

Next, make a variable that will hold the query as a string, in this case its a select statement with a limit of 100 records to keep the list small. Then, we can execute it by calling the mysqli::query() function from our connection object. Now, it's time to display some data. Start by opening up a <table> tag through echo, then fetch one row at a time in the form of a numerical array with mysqli::fetch_row() which can then be displayed with a simple foreach loop. Don't forget to use <td></td> for each value, and also to open and close each row with echo"<tr>" and echo"</tr>. Finally we close the table.

<?php
$query = "select * from city limit 100;";
$queryResult = $conn->query($query);
echo "<table>";
while ($queryRow = $queryResult->fetch_row()) {
    echo "<tr>";
    forech($queryRow as $value) {
        echo "<td>".htmlspecialchars($value)."</td>";
    }
    echo "</tr>";
}
echo "</table>";

Exterminate

Answer 5

First, connect to the database:

$conn=mysql_connect("hostname","username","password");
mysql_select_db("databasename",$conn);

You can use this to display a single record:

For example, if the URL was /index.php?sequence=123, the code below would select from the table, where the sequence = 123.

<?php
$sql="SELECT * from table where sequence = '".$_GET["sequence"]."' ";
$rs=mysql_query($sql,$conn) or die(mysql_error());
$result=mysql_fetch_array($rs);

echo '<table>
<tr>
<td>Forename</td>
<td>Surname</td>
</tr>
<tr>
<td>'.$result["forename"].'</td>
<td>'.$result["surname"].'</td>
</tr>
</table>';
?>

Or, if you want to list all values that match the criteria in a table:

<?php
echo '<table>
<tr>
<td>Forename</td>
<td>Surname</td>
</tr>';
$sql="SELECT * from table where sequence = '".$_GET["sequence"]."' ";
$rs=mysql_query($sql,$conn) or die(mysql_error());
while($result=mysql_fetch_array($rs))
{
echo '<tr>
<td>'.$result["forename"].'</td>
<td>'.$result["surname"].'</td>
</tr>';
}
echo '</table>';
?>

Silden

Answer 6

Surely a better solution would by dynamic so that it would work for any query without having to know the column names?

If so, try this (obviously the query should match your database):

// You'll need to put your db connection details in here.
$conn = new mysqli($server_hostname, $server_username, $server_password, $server_database);

// Run the query.
$result = $conn->query("SELECT * FROM table LIMIT 10");

// Get the result in to a more usable format.
$query = array();
while($query[] = mysqli_fetch_assoc($result));
array_pop($query);

// Output a dynamic table of the results with column headings.
echo '<table border="1">';
echo '<tr>';
foreach($query[0] as $key => $value) {
    echo '<td>';
    echo $key;
    echo '</td>';
}
echo '</tr>';
foreach($query as $row) {
    echo '<tr>';
    foreach($row as $column) {
        echo '<td>';
        echo $column;
        echo '</td>';
    }
    echo '</tr>';
}
echo '</table>';

Aryan

Yes you're right, very sorry for the confusion. I've clarified and tested to make sure the above runs and is complete. – Aryan

Answer 7

mysql_connect("localhost","root","");
mysql_select_db("database");
$query=mysql_query("select * from studenti");
$x=@mysql_num_rows($query);

echo "<a href='file.html'>back</a>";
echo "<table>";
$y=mysql_num_fields($query);
echo "<tr>";

for($i=0 ,$i<$y,$i++)
{
  $values=mysql_field_name($query,$i);
  echo "<th>$values</th>";
}

echo "</tr>";

while(list($p ,$n $your_table_list)=mysql_fetch_row($query))
{
  print("<tr>\n".
  "<td>$p</td>".
  "</tr>/n");
}
?>

Deanndeanna

Answer 8

<?php
$mysql_hostname = "localhost";
$mysql_user     = "ram";
$mysql_password = "ram";
$mysql_database = "mydb";
$bd             = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Oops some thing went wrong");
mysql_select_db($mysql_database, $bd) or die("Oops some thing went wrong");// we are now connected to database

$result = mysql_query("SELECT * FROM users"); // selecting data through mysql_query()

echo '<table border=1px>';  // opening table tag
echo'<th>No</th><th>Username</th><th>Password</th><th>Email</th>'; //table headers

while($data = mysql_fetch_array($result))
{
// we are running a while loop to print all the rows in a table
echo'<tr>'; // printing table row
echo '<td>'.$data['id'].'</td><td>'.$data['username'].'</td><td>'.$data['password'].'</td><td>'.$data['email'].'</td>'; // we are looping all data to be printed till last row in the table
echo'</tr>'; // closing table row
}

echo '</table>';  //closing table tag
?>

it would print the table like this just read line by line so that you can understand it easily..

Sletten

Note to other users: the mysql_ functions were removed in PHP 7 and deprecated since PHP 5.5. Consider using the mysqli_ functions if dealing with PHP 7 or later. – Tullus

Answer 9

in my opinion this is a clearer version of it, also the table is displayed nicer. And it is easier to read. I have split it into 2 functions so that you only have to call the functions to output the data. So you would need less code and if you want to output the table several times it is no code duplication.

    $query = 'select * from movie';
    $stmt = makeStatment($query);
    outputMySQLToHTMLTable($stmt);

    function makeStatment($query, $array = null)
    {
        try 
        {
            global $con;
            $stmt = $con->prepare($query);
            $stmt->execute($array);
            return $stmt;
        } 
        catch (Exception $e)
        {
            return $e;
        }
    }

    function outputMySQLToHTMLTable($stmt)
    {    
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $columnCount = $stmt->columnCount();
        
        echo '<table class="table">';
        echo '<thead>';
        echo '<tr>';
        for ($i = 0; $i < $columnCount; $i++) {
            $columnMeta = $stmt->getColumnMeta($i);
            echo '<th>'.htmlspecialchars($columnMeta['name']).'</th>';
        }
        echo '</tr>';
        echo '</thead>';
        
        echo '<tbody>';
        // If there is data then display each row
        if ($data) {
            foreach ($data as $row) {
                echo '<tr>';
                foreach ($row as $cell) {
                    echo '<td>'.htmlspecialchars($cell).'</td>';
                }
                echo '</tr>';
            }
        } else {
            echo '<tr><td colspan="'.$columnCount.'">No records in the table!</td> 
   </tr>';
        }
        echo '</tbody>';
        echo '</table>';
    }

Loculus

This code has several issues and shouldn't be recommended. 1. Using global variables is a bad practice by itself, and here it can be seen why: nobody guarantees that someone else has a variable called $con. It should be a function parameter, not a global variable. 2. return $e; makes ZERO sense. There is no fetchAll() method in the Exception object. this try-catch stuff should be removed. 3. Printing HTML from PHP makes it harder to customize. It should be the other way round, HTML with occasional php tags. 4. Making this function to accept PDOstatement makes it less flexible. – Technique

Answer 10

OOP Style : At first connection with database.

<?php
class database
{

 public $host = "localhost";
 public $user = "root";
 public $pass = "";
 public $db   = "db";
 public $link;

 public function __construct()
 {
    $this->connect();
 }

 private function connect()
 {
   $this->link = new mysqli($this->host, $this->user, $this->pass, $this->db);
    return $this->link;
 }

 public function select($query)
 {
    $result = $this->link->query($query) or die($this->link->error.__LINE__);

    if($result->num_rows>0)
    {
      return $result;
    } 
    else 
    {
      return false;
    }
}
?>

Then :

    <?php
        $db = new database();

        $query = "select * from data";
        $result = $db->select($query);

        echo "<table>";
         echo "<tr>";
            echo "<th>Name </th>";
            echo "<th>Roll </th>";
         echo "</tr>";
         while($row = mysqli_fetch_array($result)) 
         {
            echo "<tr>";
            echo "<td> $row[name]</td>";
            echo "<td> $row[roll]</td>";
            echo "</tr>";
         }
       echo "</table>";
 ?>

Packet