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>johnny@example.com</td>
    <td>This is the message John sent you</td>
  </tr>
</table>

And then all the other values below 'john'.

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>

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>";

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;

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>";

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>';
?>

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>';

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");
}
?>

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

Answer 9

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>";
 ?>