HTML Table Using MySQLi and PHP

HTML table using MySQLi and PHP

Perhaps this is because you have not opened your HTML table tags correctly, its an easy thing to overlook. Also loop the result set inside a white statement:

$sql = "SELECT `name`,`date`,`present`,`website` FROM `dates`";
$results = mysqli_query($conn,$sql);
echo "<table>"; //begin table tag...
//you can add thead tag here if you want your table to have column headers
 while($rowitem = mysqli_fetch_array($results)) {
    echo "<tr>";
    echo "<td>" . $rowitem['name'] . "</td>";
    echo "<td>" . $rowitem['date'] . "</td>";
    echo "<td>" . $rowitem['present'] . "</td>";
    echo "<td>" . $rowitem['website'] . "</td>";*/
    echo "</tr>";
}
echo "</table>"; //end table tag

Dynamic table in HTML using MySQL and php

If you want to display the full contents of the database table as an HTML table, I suggest you make a function that will do all of this dynamically for you. This function should check that the table exists, fetch all the data, and fetch output HTML table with headers.

MySQLi solution

Here is my suggestion using MySQLi. First of all, you must make sure that the table actually exists. Then you can fetch all the data from the table. The object returned by mysqli::query() will have all metadata information about column names which you can use to display the header row. You can use fetch_fields() to iterate over each column metadata. The data can be fetched using fetch_all() method.

<?php

// create global connection using mysqli
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect("localhost", "username", "password", "database", "3306");
$mysqli->set_charset('utf8mb4'); // always set the charset

function outputMySQLToHTMLTable(mysqli $mysqli, string $table)
{
    // Make sure that the table exists in the current database!
    $tableNames = array_column($mysqli->query('SHOW TABLES')->fetch_all(), 0);
    if (!in_array($table, $tableNames, true)) {
        throw new UnexpectedValueException('Unknown table name provided!');
    }
    $res = $mysqli->query('SELECT * FROM '.$table);
    $data = $res->fetch_all(MYSQLI_ASSOC);
    
    echo '<table>';
    // Display table header
    echo '<thead>';
    echo '<tr>';
    foreach ($res->fetch_fields() as $column) {
        echo '<th>'.htmlspecialchars($column->name).'</th>';
    }
    echo '</tr>';
    echo '</thead>';
    // 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="'.$res->field_count.'">No records in the table!</td></tr>';
    }
    echo '</table>';
}

outputMySQLToHTMLTable($mysqli, 'user');

PDO Solution

Using PDO is very similar but you have to pay attention to the differences in the APIs.

To get the table names, you can use fetchAll(PDO::FETCH_COLUMN) instead of array_column(). To get the column metadata, you need to use getColumnMeta() function.

<?php

$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'username', 'password', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

function outputMySQLToHTMLTable(pdo $pdo, string $table)
{
    // Make sure that the table exists in the current database!
    $tableNames = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
    if (!in_array($table, $tableNames, true)) {
        throw new UnexpectedValueException('Unknown table name provided!');
    }
    $stmt = $pdo->query('SELECT * FROM '.$table);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $columnCount = $stmt->columnCount();
    
    echo '<table>';
    // Display table header
    echo '<thead>';
    echo '<tr>';
    for ($i = 0; $i < $columnCount; $i++) {
        echo '<th>'.htmlspecialchars($stmt->getColumnMeta($i)['name']).'</th>';
    }
    echo '</tr>';
    echo '</thead>';
    // 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 '</table>';
}

outputMySQLToHTMLTable($pdo, 'user');

P.S. The table existence check can be optimized with the following code instead:

$tableNames = $pdo->prepare('SELECT COUNT(1) FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=?');
$tableNames->execute([$table]);
if (!$tableNames->fetchColumn()) {
    throw new UnexpectedValueException('Unknown table name provided!');
}

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

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.

PHP / Mysql / Html Table - Group by row

Yes, obviously it is possible. You can join the same table thrice with itself and get the output from that. May be this is what you want:

SELECT a.sid AS sid, b.data AS name, c.data AS age, d.data AS email
FROM 
test a inner join test b on b.sid = a.sid
inner join test c on c.sid = a.sid
inner join test d on d.sid = a.sid
WHERE b.cid = 'name'
AND c.cid = 'age'
AND d.cid = 'email'
GROUP BY a.sid;

And here's the sql fiddle: http://sqlfiddle.com/#!9/1714b/37

PHP: How to display multiple MySQL table records per HTML table row using MySQLi

Introduction

Note: This answer details creating a many-records-to-one-row arrangement. However, this answer can be altered to provide a single-record-to-one-row arrangement.

Separating concerns will help you write cleaner code. Separating concerns will make it easier to maintain your code. Clean code is loosely coupled, unburdened by embedded dependencies. Clean code identifies its dependencies in function signatures and class constructors with the expectation that these needs will be fulfilled externally. Clean code has tight cohesion. This means functions/methods have a single task, and classes have a single objective. Clean code is often reflected in a task that has been broken down and refined (but, not always). Clean code is an ideal I strive for, but no one is perfect.

Try to think of ways to get as much SQL and PHP out of your HTML files. Interpolating variables and showing the return results of functions only can make your HTML much easier to read. Good HTML structure counts, too.

Breaking down the task of dynamically building a <table> based on the results of a SQL query is very possible. Eventually, you may decide to use CSS and divs for styling and responsiveness reasons. This code can be altered to achieve this (after all, you'd just be stacking boxes in rows).

Eventually, creating an OOP class (with custom namespaces) would be great for modularizing your code and getting the vast majority of your symbols (variable names, etc) out of the global namespace.

---

Before we get going: php.ini: include_path

Do you want to setup a logical directory architecture for your project?

Set the include_path inside of php.ini.

If you search your php.ini for the include_path setting, you can set this to one directory, or any group of appropriate directories. This way, you can arrange your files in directories the way you desire, and your include, include_once, require, and require_once statements will still find the files they want to import. You will not have to type absolute paths like/dir/dir/file.php or relative paths like ../../core/database.php. In both cases, you could just specify the filename.

Example:

include 'file.php';     //Finds the file if it is in the include_path.
require 'database.php'; //Finds the file if it is in the include_path.

Note: Keep library files and other pure PHP coding files (etc ...) out of the webroot, or any publicly accessible directories. Keep them logically above the webroot. Set the include_path so you do not have to keep doing ../../blah/foo all of the time.

---

Tasks

1) First, make a function for getting an instance of a mysqli_result object.

/**
 * Returns a string, or
 * throws an UnexpectedValueException, otherwise.
 */
function isString($string)
{
    if (!is_string($string)) {
        throw new UnexpectedValueException("$string must be a string data type.");
    }

    return $string;
}

/**
 * Returns a mysqli_result object, or throws an `UnexpectedValueException`.
 * You can reuse this for other SELECT, SHOW, DESCRIBE or EXPLAIN queries.
 */
function getMySQLiResult(MySQLi $db, $sql)
{
    $result = $db->query(isString($sql));

    if (!($result instanceof mysqli_result)) {
        throw new UnexpectedValueException("<p>MySQLi error no {$db->errno} : {$db->error}</p>");
    } 

    return $result;
}

2) Second, make a function to house your SQL and invoke getMySQLiResult().

/**
 * Make sure you can get the data first.
 * returns a mysqli_result object.
 */
function getInterviews(MySQLi $db)
{
    $sql = "SELECT * FROM `interviews` WHERE `featured` = 1";
    return getMySQLiResult($db, $sql);
}

3) Make a function for building a table data (<td></td>) cell and its content. Put all HTML or data that you need to repeat for each record in here.

/**
 * Returns one database table record a table data cell.
 */
function buildCell(array $record)
{
    return "<td>\n".
                '<img src="' .$record['image']. '" alt="' .$record['title']. '">' ."\n".
                '<h4>' .$record['title']. '</h4>' . "\n" .
                '<hr>' . "\n" .
                '<p class="description">' .$record['description']. '</p>' . "\n" .
                '<div id="hovers">
                     <a href="#" class="button">
                         <span class="contentbut">Read More</span>
                     </a>
                 </div>' . "\n 
            </td>\n";
}

4) Make a function for building table rows. Be wary of partial rows. :-)

First, a little helper function.

/**
 * Returns one <tr></tr> element. Helper.
 */
function makeTr($tds)
{
    return "<tr>\n" .isString($tds). "\n</tr>";
}

Second, the real deal.

function buildTableRow (array $tableRow)
{
    return makeTr(buildCell($tableRow)) . "\n";   //Done!
}

/**
 * Returns a string of multiple <tr></tr> elements,
 * $maxRecords per row.
 */
function buildTableRows(array $tableRows, $numRecords, $maxPerRow)
{
    $rows          = []; // Holds finished groups of <tr>s
    $row           = ''; // Temporary variable for building row of <td>s
    $numCells      = 0;  // Number of cells currently in a row of <td>s.
    $numRows       = (int)($numRecords / $maxPerRow); //Rows to make.
    $numStragglers = $numRecords % $maxPerRow;        // Extra <td>s, partialRow.

    if ($numStragglers !== 0) {  //Check if extra row is needed.
        $numRows += 1;
    }

    foreach ($tableRows as $record)
    {
        $row .= buildCell($record);
        ++$numCells;

        if ($numCells === $numRecords) {  // Builds partial, last row, if needed.
            $rows[] = makeTr($row);
            break;                        // Done!
        }

        if ($numCells === $maxPerRow) {   // Builds full row.
            $rows[]   = makeTr($row);     // Save the row.
            $numCells = 0;                // Start cell counter over.
            $row      = '';               // Start a new row.
        }
    }

    if(count($rows) !== $numRows) {  //Verify all rows were created.
        throw new RuntimeException("Rows (<tr>) for all records were not created!");
    }

    return  implode("\n", $rows) . "\n";  //Return all rows as a string.
}

5) Make a function that spits out the HTML you need on your page. In this case, you only need one (1) substitution to appear in the HTML.

/**
 * returns a set of HTML table rows (<tr></tr>) to fill a <tbody>.
 * or, returns an alternative message.
 */
function drawInterviews(MySQLi $db, $maxPerRow) //PDO is recommened. Dependency injection.
{
    $defaultMessage = "<tr>\n<td>There are no featured interviewers.<td>\n<\tr>\n";

    try {
           if (!is_int($maxPerRow) || $maxPerRow < 1) {
              throw new RangeException("The number of interviews per row must be an integer equal to 1, or greater than 1.");
           }

           //Make a robust connection sequence, or pass it in like above.
           //$db       = new mysqli('host', 'user', 'password', 'dbname');
           $result     = getInterviews($db);
           $numRecords = result->num_rows;

           if ($numRecords < 1) {
               return $defaultMessage;
           }

           if ($numRecords === 1) {
               return buildTableRow($result->fetch_assoc()); 
           }                

           return buildTableRows($result->fetch_all(), $numRecords, $maxPerRow);

    } catch (Exception $e)
         //Something went wrong with the query.
         error_log($e->getMessage());
    } finally { //PHP 5.5+
        $result->free();
    }

    return $defaultMessage;
}

6) Now, have a good HTML <table> structure. Only one interpolation needed. Assuming three <td>s (records) per row ...

Anyway, if you want a table, put a copy of this table "skeleton" inside of academytest.php, somewhere between the header and the footer (i.e. the main <body> of the HTML document).

<table>
    <caption>Featured Interviewers</caption> <!-- Centers above table. -->
    <thead>
        <tr>                  <!-- If needed. -->
            <th>Heading1</th> <!-- If needed. -->
            <th>Heading2</th> <!-- If needed. -->
            <th>Heading3</th> <!-- If needed. -->
        </tr>
    </thead>
    <tfoot></tfoot>           <!-- If needed. Yes, it goes after <thead>. -->
    <tbody>
        <!-- <div id="element1"></div> --> //What goes between here?
        <!-- <div id="content1">       --> //What's this? 
        <?= drawInterviews($db, 3); ?>  <!-- Dependency injection. -->
    </tbody>
</table>    

All of this can be made more modular and reusable (object-oriented, even).

---

Update:

Based on your Dropbox code ...

academytest.php

1) The best thing to do is create a separate PHP file named tbodyFiller.php, or something to that effect. Put all the functions in this file, except for getInterviews() and drawInterviews() which will go into academyLibray.php, isString() which will go into library.php, and getMySQLiResult() which will go in database.php (formerly init.php).

The beginning of academytest.php should look like this:

<?php
//                       academytest.php
require '../../includes/library.php';    //For now, put generic helper functions here. Group them, later.
require_once '../../core/database.php';  //Formerly, init.php. Put getMySQLiResult() in here.
require '../../includes/academyLibrary.php'; //Put the two "interview"  functions here.

$db = getMySQLi();  //Many things are dependent on this being here.

require '../../includes/navigation.php';

/***************** DELETE THESE LINES *****************/
//$sql = "SELECT * FROM interviews WHERE featured = 1";
//$featured = $db->query($sql);
/******************************************************/

In the footer of academytest.php, close the connection to your database.

<!-- ------FOOTER------ -->
<?php
    include '../../includes/footer.php';
    $db->close(); //Ensures $db is available to use in the footer, if necessary.
?>

---

library.php

The beginning of library.php should look like this:

<?php
//                       library.php

/**
 * Returns a string, or
 * throws an UnexpectedValueException, otherwise.
 */
function isString($string)
{
    if (!is_string($string)) {
        throw new UnexpectedValueException("$string must be a string data type.");
    }

    return $string;
}

---

I think init.php should be named database.php. You can learn to use the object oriented constructor (using new) sequence with error checking at your leisure. Eventually, you will want to learn PDO.

Also, make a separate file to hold your credentials. Right now, this is better than hard coding them in to the getMySQLi() function.

dbCreds.php

<?php

//                        dbCreds.php

$host     =  ''; //IP or DNS name: string.
$username =  ''; //Your account: string.
$passwd   =  ''; //The password: string.
$dbname   =  ''; //The database you want to work with: string.

//*************************************************************************
//$port   =  '3306'; //Un-comment and change only if you need a differnt TCP port. 
                     //Also, you would need to add a $port as your last argument in new MySQLi(),
                     //in the getMySQLi() function.

---

database.php

<?php
//                       database.php
/**
 * Returns a mysqli_result object, or throws an `UnexpectedValueException`.
 * You can reuse this for other SELECT, SHOW, DESCRIBE or EXPLAIN queries.
 */
function getMySQLiResult(MySQLi $db, $sql)
{
    $result = $db->query(isString($sql));

    if (!($result instanceof mysqli_result)) {
        throw new UnexpectedValueException("<p>MySQLi error no {$db->errno} : {$db->error}</p>");
    } 

    return $result;
}

function getMySQLi() //This can be improved, but that's not the issue right now.
{
    require_once 'dbCreds.php'; //Choose your own file name. Do not put in public directory.

    $db = new mysqli($host, $username, $passwd, $dbname); //$port would be next.

    if(!($db instanceof MySQLi)){
        throw new UnexpectedValueException("A MySQLi object was not returned during your connection attempt.");
    }

    if(isset($db->connect_error)){
        throw new UnexpectedValueException("The database connection was not established. {$db->connect_errno} : {$db->connect_error}");
    }

    return $db
}  //Using the object form of MySQLi object has side benenfits.

---

academyLibrary.php

The beginning of academyLibrary.php should look like this:

<?php
//                       academyLibrary.php
require 'tbodyFiller.php'; //Put all but four functions in here.

function getInterviews(MySQLi $db)
{
    $sql = "SELECT * FROM `interviews` WHERE `featured` = 1";
    return getMySQLiResult($db, $sql);
}

/**
 * Comments //etc...
 */
function drawInterviews(MySQLi $db, $maxPerRow)
{
    //The code, etc ...
}

If you have not configured your include_path inside of the php.ini, make sure academyLibrary.php and tbodyFiller.php are located in the same directory.

---

navigation.php

We are going to replace the procedural forms of working with MySQL with the object-oriented ones. This is simple, and we do not need to change much. I will not replace your loops or queries at this time, but my advice is to get out of the habbit of putting PHP loops and SQL directly in your HTML. Find a way to use a function or method, like I did for the table's in academytest.php. By this time, you should have enough examples. :-)

Refactoring

I took some time to refactor this file. Here is what I have at the top. Once again, you may wish to create another PHP file, say navLibrary.php, and put these functions into it. In that case you would replace all the functions seen below with one line, require 'navLibrary.php';. Naturally, this way of importing code may depend on configuring your include_path inside of the php.ini.

<?php
//                    navigation.php    

function getPqueryMainData(MySQLi $db)
{
    $sql = "SELECT * FROM `mainmenu` WHERE `parent` = 0";       //pqueryMain
    return getMySQLiResult($db, $sql);
}

function getPqueryData(MySQLi $db)
{
    $sql = "SELECT * FROM `categories` WHERE `parent` = 0";     //pquery
    return getMySQLiResult($db, $sql);
}

function getCquery1Data(MySQLi $db)
{
    $sql = "SELECT * FROM `categories` WHERE `parent` = 1";     //cquery1
    return getMySQLiResult($db, $sql);
}

function getCquery2Data(MySQLi $db, $int)
{
    $sql = "SELECT * FROM `categories` WHERE `parent` = '$int'"; //cquery2
    return getMySQLiResult($db, $sql);
}

//Consider doing at most 3 queries early on.
//Consider using better names for your variables.
//I get that 'p' means "primary", and 'c' means "child", but come on. :-) 

$pqueryMain = getPqueryMainData($db);
$pquery     = getPqueryData($db);
$cquery1    = getCquery1Data($db);
$cquery2    = null;

PHP / MySQLi syntax with HTML table

You have to look around quotes changes. Do like below:-

<th><?php if(!isset($_POST['search'])) { echo "<a href='?orderby=FirstName&order=".$firstOrder."'>First Name</a>"; }?></th>

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!

---

Related Topics