Use PHP to Display MySQL Results in HTML Table

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

Use PHP to Display MySQL Results in HTML Table

Get the data and column names from the same result set

  <?php
  $i = 0;
  $colNames = array();
  $data = array();
  while($row = ***_fetch_assoc($res)) //where $res is from the main query result not schema information
  {
     //get the column names into an array $colNames
     if($i == 0) //make sure this is done once
     {
        foreach($row as $colname => $val)
           $colNames[] = $colname;
     }

     //get the data into an array
     $data[] = $row;

     $i++;
  }

 ?>

UPDATE: Suggested by @YourCommonSense to replace the above code and it worked, simple and shorter - A WAY TO GET THE COLUMN NAMES/ARRAY KEYS WITHOUT LOOPING THROUGH LIKE I DID

  $data = array();
  while($row = mysql_fetch_assoc($res))
  {
     $data[] = $row;
  }

  $colNames = array_keys(reset($data))

Continued as before: Print the table

 <table border="1">
 <tr>
    <?php
       //print the header
       foreach($colNames as $colName)
       {
          echo "<th>$colName</th>";
       }
    ?>
 </tr>

    <?php
       //print the rows
       foreach($data as $row)
       {
          echo "<tr>";
          foreach($colNames as $colName)
          {
             echo "<td>".$row[$colName]."</td>";
          }
          echo "</tr>";
       }
    ?>
 </table>

Test Result

You can see how I separated the data retrieval from table generation. They are dependent of each other now and you can test your table generation without the database by populating the arrays with static data

You can also make them into separate functions.

Adding mysql data in to HTML table

Change your if loop like this

if ($result->num_rows > 0) {
    echo "<table><tr><th>Temperature</th><th>Acidity</th><th>Ammonia</th><th>Nitrite</th><th>Nitrate</th><th>Phosphate
    </th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["Temperature"]. "</td><td>" . $row["Acidity"]."</td><td>" . $row["Ammonia"]."</td><td>" . $row["Nitrite"]."</td><td>" . $row["Nitrate"]."</td><td>" . $row["Phosphate"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

But make sure the value inside these $row["Temperature"] array should be correct column name

And change your sql query to this

$sql = "SELECT * FROM waterparams";

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 MySQL query result as HTML tables per row

Go like this...and place your data accordingly..

<table>
<tr>
<th> <!--your table headers -->
...
</th>
 while($row = $result->fetch_assoc()) {
<tr>
echo '<td>'.$row["btitle"].'</td>';
echo '<td>'.$row["basin"].'</td>'; //your table data
echo '<td>'.$row["busurl"].'</td>';
...
</tr>
}
</table>

MySQL-PHP display data separately in html table

Check also a column name in a $line:

foreach ($line as $col_name => $col_value) {
    if ($col_name != 'order_id') {
        echo "\t\t<td>$col_value</td>\n";
    }
}

Display all MySQL table data in html table

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

Displaying results from mysql database in a html/php table

Try this one, if it works for you..

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

       if ($result->num_rows > 0) {
     // output data of each row

     while($row = $result->fetch_assoc()) {
       echo "<tr>";
       echo "<td>". $row["first_name"] . "</td>";
       echo "<td>". $row["last_name"] . "</td>";
       echo "<td>". $row["email"] . "</td> " ;
       echo "</tr>";
    }
}
echo "</table>";

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>

---

Related Topics