Display Data from SQL Database into PHP/ HTML Table

display data from SQL database into php/ html table

PHP provides functions for connecting to a MySQL database.

$connection = mysql_connect('localhost', 'root', ''); //The Blank string is the password
mysql_select_db('hrmwaitrose');

$query = "SELECT * FROM employee"; //You don't need a ; like you do in SQL
$result = mysql_query($query);

echo "<table>"; // start a table tag in the HTML

while($row = mysql_fetch_array($result)){   //Creates a loop to loop through results
echo "<tr><td>" . htmlspecialchars($row['name']) . "</td><td>" . htmlspecialchars($row['age']) . "</td></tr>";  //$row['index'] the index here is a field name
}

echo "</table>"; //Close the table in HTML

mysql_close(); //Make sure to close out the database connection

In the while loop (which runs every time we encounter a result row), we echo which creates a new table row. I also add a to contain the fields.

This is a very basic template. You see the other answers using mysqli_connect instead of mysql_connect. mysqli stands for mysql improved. It offers a better range of features. You notice it is also a little bit more complex. It depends on what you need.

Please note that "mysql_fetch_array" is now deprecated since PHP 5.5.0, and it was removed in PHP 7.0.0. So please take a look in "mysqli_fetch_array()" instead.

Showing sql data in HTML table using php

Try using mysqli_query($connection, $query) instead of mysql_query($query) and mysqli_fetch_array($result) instead of mysql_fetch_array($result).

Notice the $connection variable which is necessary with the new mysqli.

You also need to use the new mysqli_connect. For example, try,

$connection = mysqli_connect( 'localhost, 'root', 'password', 'movies');

I assume 'test' is a table in the 'movie' database.

Display data from SQL database into php / html table

use of below code.Your problem was printing the values

<?php

$mysqli = NEW mysqli('localhost','username','password','database');

require('/home/database/public_html/wp-load.php');
$id = get_the_ID();

$resultSet = $mysqli->query("SELECT * FROM sweepstake_data WHERE item_id = $id");

if($resultSet->num_rows !=0){

echo "<table>"; // start a table tag in the HTML

    while($rows = $resultSet->fetch_assoc())
    {
        $description = $rows['description'];
        $links = $rows['links'];
        $category = $rows['category'];
        $eligibility = $rows['eligibility'];
        $start_date = $rows['start_date'];
        $end_date = $rows['end_date'];
        $entry_frequency = $rows['entry_frequency'];
        $prizes = $rows['prizes'];
        $victory_prizes = $rows['victory_prizes'];
        $additional_comments = $rows['additional_comments'];

        echo   "<tr><td>";
        $description != "" ? "<p>Name: $description<br />" : "" ;
         echo "<tr><td>";
        echo $links != "" ? "Link: <a href=$links>Click here</a> <br />" : "" ;
        echo "<tr><td>";
        echo $category != "" ? "Category: $category<br />" : "" ;
         echo "<tr><td>";
         echo $eligibility != "" ? "Eligibility: $eligibility<br />" : "" ;
        echo "<tr><td>" ;
         echo $start_date != "" ? "Start date:$start_date<br />" : "" ;
        echo "<tr><td>" ;
        echo $end_date != "" ? "End date: $end_date<br />" : "" ;
        echo "<tr><td>";
        echo $entry_frequency != "" ? "Entry frequency: $entry_frequency<br />" : "" ;
       echo  "<tr><td>";
       echo $prizes != "" ? "Prizes: $prizes<br />" : "" ;
        echo "<tr><td>";
        echo $victory_prizes != "" ? "Victory prizes: $victory_prizes<br />" : "" ;
        echo "<tr><td>";
        echo $additional_comments != "" ? "Additional comments: $additional_comments<br />" : "" ;

    }

echo "</table>"; //Close the table in HTML

}else {
    echo "No results.";
}

?>

How can I display data from MYSQL database using HTML table from a PHP file?

You are looping the table tag in while loop that's why multiple tables are getting created. Try doing like this

echo "<table>";
    echo "<tr>";
    echo "<th>ID</th>";
    echo "<th>First Name</th>";
    echo "<th>last Name</th>";
    echo "<th>User Name</th>";
    echo "<th>Email</th>";
    echo "<th>Password</th>";
    echo "<th>Description</th>";
    echo "<th>Delete</th>";
    echo "<th>Edit</th>";
    echo "</tr>";
while ($row=mysqli_fetch_object($print)) {
    echo "<tr>";
    echo "<td><h2>$row->fname</h2></td>";
    echo "<td><h2>$row->lname</h2></td>";
    echo "<td><h2>$row->uname</h2></td>";
    echo "<td><h2>$row->email</h2></td>";
    echo "<td><h2>$row->paswd</h2></td>";
    echo "<td><h2>$row->descrip</h2></td>";
    echo "<td><h2><a style='color:white' href='./admin_remove.php?poistettava=$row->id'>Delete</a></h2></td>";
    echo "<td><h2<a href='./admin_edit.php?editable=$row->id'>Edit</a></h2></td>";
    echo "</tr>";
 }
    
echo "</table>";

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

Example taken from W3Schools: PHP Select Data from MySQL

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

It's a good place to learn from!

Displaying SQL Query in HTML table (php)

I've been looking at how I would display SELECT (select COUNT() from
tasks) , (select count() from quotes) into the following format
(HTML)

You can just run the queries query, and use the result of the first to create the first row of the table, then the result of the second to create the second row. Since COUNT queries always return exactly 1 row when there's no GROUP BY, it's quite simple to do really:

$sql1 = "SELECT COUNT(*) FROM tasks";
$result1 = $conn->query($sql1);
if ($row = $result1->fetch_array()) $taskCount = $row[0];
else $taskCount = "error";

$sql2 = "SELECT COUNT(*) FROM quotes";
$result2 = $conn->query($sql2);
if ($row = $result2->fetch_array()) $quoteCount = $row[0];
else $quoteCount = "error";

?>
<table style="width:100%">
  <tr>
    <th>Table</th> 
    <th>Count</th> 
  </tr>
  <tr>
    <td>Tasks</td>
    <td><?php echo $taskCount; ?></td>
  </tr>
  <tr>
    <td>Quotes</td>
    <td><?php echo $quoteCount; ?></td>
  </tr>
</table>

---

Another way, if you want the HTML structure to be less repetitive / dependent on the tables queries, is to UNION the SELECTs into a single query:

$sql = "SELECT 'Tasks' AS 'table', COUNT(*) as 'count' FROM tasks";
$sql .= " UNION ";
$sql .= "SELECT 'Quotes' AS 'table', COUNT(*) as 'count' FROM quotes";
$result = $conn->query($sql);
?>

<table style="width:100%">
  <tr>
    <th>Table</th> 
    <th>Count</th> 
  </tr>
<?php
while ($row = $result->fetch_assoc()) { ?>
  <tr>
    <td><?php echo $row["table"]; ?></td>
    <td><?php echo $row["count"]; ?></td>
  </tr>
<?php 
}
?>
</table>

Display data from database table into html table inside a php file

Please use mysqli_fetch_assoc insted of mysqli_fetch_row
and remove before and after from table name in query

<?php
include 'connect.php';
$query = ("SELECT * FROM transaction");
$result = mysqli_query($conn, $query);
?>
<!DOCTYPE html>
<html lang="en">
<body>
 <form class="form-inline my-2 my-lg-0" action="search2.php" method="POST">
        <input class="form-control mr-sm-2" type="text" placeholder="Search" aria-label="Search">
        <button class="btn btn-outline-success my-2 my-sm-0" type="submit" name="search">Search</button>
      </form>
    </div>
  </nav>
<!-- Table -->
<h1>Sales</h1>
          <div class="table-responsive">
            <table class="table table-striped table-sm">
              <thead>
                <tr>
                  <th>Customer ID</th>
                  <th>Date</th>
                  <th>Outlet</th>
                  <th>Employee ID</th>
                  <th>Product ID</th>
                  <th>Quantity</th>

                </tr>
              </thead>
              <tbody>
                <?php while ($row = mysqli_fetch_assoc($result)) :?>
    <tr>
      <th scope="row"><?php echo $i; ?></th>
        <td><?php echo $row['customer_id']; ?></td>
        <td><?php echo $row['date']; ?></td>
        <td><?php echo $row['outlet_id']; ?></td>
        <td><?php echo $row['emp_id']; ?></td>
        <td><?php echo $row['Prod_id']; ?></td>
        <td><?php echo $row['quantity']; ?></td>
    </tr>
<?php
if (!$result) {
  die ("Database access failed: " .mysql_error());
}
endwhile; ?>
              </tbody>
            </table>
          </div>

</body>
</html>

---

Related Topics