Create HTML table from sql table

Question

I have a table in a SQL database with the following fields: ID, Name, Email, University, Languages, and Experience. I want to create an html table that fetches data from SQL and outputs the last 10 results? How would I do that?

I'm sorry if this is a very simple question, I have very little knowledge in PHP and SQL.

Here's the code I have right now that just displays the name and not in a table:

    <html>
    <head>
        <title>Last 5 Results</title>
    </head>
    <body>
        <?php
            $connect = mysql_connect("localhost","root", "root");
            if (!$connect) {
                die(mysql_error());
            }
            mysql_select_db("apploymentdevs");
            $results = mysql_query("SELECT * FROM demo");
            while($row = mysql_fetch_array($results)) {

                echo $row['Name'] . "</br>";


            ?>
    </body>
</html>

Duplicate of outputting values from database into html table PHP, PHP create HTML table from databse, and about a dozen others. — Ken White
– Ken White, Commented Aug 13, 2012 at 23:13

TofferJ · Accepted Answer · 2018-06-14 17:54:38Z

12

Here is something that should help you to create the table and get more knowledge of php and mysql.

Also you should move your connection logic and query to the beginning of your process, thus avoiding errors while loading the page and showing a more accurate error than just the mysql_error.

Edit: If your ids are incrementing, then you could add the ORDER BY clause,
change: SELECT * FROM demo LIMIT 10 to: SELECT * FROM demo LIMIT 10 ORDER BY id

<html>
    <head>
        <title>Last 10 Results</title>
    </head>
    <body>
        <table>
        <thead>
            <tr>
                <td>Id</td>
                <td>Name</td>
            </tr>
        </thead>
        <tbody>
        <?php
            $connect = mysql_connect("localhost","root", "root");
            if (!$connect) {
                die(mysql_error());
            }
            mysql_select_db("apploymentdevs");
            $results = mysql_query("SELECT * FROM demo LIMIT 10");
            while($row = mysql_fetch_array($results)) {
            ?>
                <tr>
                    <td><?php echo $row['Id']?></td>
                    <td><?php echo $row['Name']?></td>
                </tr>

            <?php
            }
            ?>
            </tbody>
            </table>
    </body>
</html>

edited Jun 14, 2018 at 17:54

TofferJ

4,8141 gold badge43 silver badges52 bronze badges

answered Aug 13, 2012 at 23:07

Josejulio

1,2941 gold badge17 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

asprin Over a year ago

You would also need ORDER BY id DESC to get the latest results

Josejulio Over a year ago

Assuming that he has his ID in AUTO_INCREMENT, then yes, gonna edit the post, thanks.

David Over a year ago

I'm not one to constantly bring up why you should avoid mysql_* commands, but when you're using them in answers to those who are just learning the language, it is potentially counter-productive.

Algoman Over a year ago

instead of <?php echo ... ?> you can use <?= ... ?>

Algoman Over a year ago

also, you should not simply echo data from a database, because it might (accidentally or even maliciously) contain things that mess with the generated html document It could contain an & or an < which would just break everything... but it might even contain javascript which would then be executed with elevated rights on your users' machines. The code is vulnerable to script injection attacks. solution: echo htmlspecialchars($row['Name']);

petermeissner · Accepted Answer · 2014-11-12 15:57:41Z

6

I got very annoyed pasting together the same code over and over again to build HTML tables from SQL queries.

So, here we go with a function that takes mysqli results and pastes them together to an plain simple HTML table that has column names according to those in the database:

function sql_to_html_table($sqlresult, $delim="\n") {
  // starting table
  $htmltable =  "<table>" . $delim ;   
  $counter   = 0 ;
  // putting in lines
  while( $row = $sqlresult->fetch_assoc()  ){
    if ( $counter===0 ) {
      // table header
      $htmltable .=   "<tr>"  . $delim;
      foreach ($row as $key => $value ) {
          $htmltable .=   "<th>" . $key . "</th>"  . $delim ;
      }
      $htmltable .=   "</tr>"  . $delim ; 
      $counter = 22;
    } 
      // table body
      $htmltable .=   "<tr>"  . $delim ;
      foreach ($row as $key => $value ) {
          $htmltable .=   "<td>" . $value . "</td>"  . $delim ;
      }
      $htmltable .=   "</tr>"   . $delim ;
  }
  // closing table
  $htmltable .=   "</table>"   . $delim ; 
  // return
  return( $htmltable ) ; 
}

Example Usage:

$DB = new mysqli("host", "username", "password", "database");
$sqlresult = $DB->query( "SELECT * FROM testtable LIMIT 1 ;" ) ; 

echo sql_to_html_table( $sqlresult, $delim="\n" ) ;

answered Nov 12, 2014 at 15:57

petermeissner

13k7 gold badges68 silver badges65 bronze badges

4 Comments

Agent Lu Over a year ago

Im confused where you put this in your html file. Is the top part in a php section and the bottom also within a php?

petermeissner Over a year ago

Yes, of cause, exactly as you are saying. This is only the php-code that you still have to put between <?php ... ?>. For best practice the first code block would go into a separate file and made available via include 'file/path/my_fiel.php'.

Frank Conijn - Support Ukraine Over a year ago

Look awesome! +1. But I don't understand the $counter = 22;?

petermeissner Over a year ago

Hmmm, reading 6 years old code ... Good question, though, I think this is part could be improved: $counter makes the code behave different for the first line of results ($counter===0) by using <th> instead of <td> elements compared to all other lines. One could name this variable $is_first_line and make it a boolean to make it more readable.

Brandon · Accepted Answer · 2015-06-05 19:33:32Z

4

Calling table( $result ); on your query results will generate a html based table regardless of the size of the sql array you feed it. I hope this helps :)

<?php

function table( $result ) {
    $result->fetch_array( MYSQLI_ASSOC );
    echo '<table>';
    tableHead( $result );
    tableBody( $result );
    echo '</table>';
}

function tableHead( $result ) {
    echo '<thead>';
    foreach ( $result as $x ) {
    echo '<tr>';
    foreach ( $x as $k => $y ) {
        echo '<th>' . ucfirst( $k ) . '</th>';
    }
    echo '</tr>';
    break;
    }
    echo '</thead>';
}

function tableBody( $result ) {
    echo '<tbody>';
    foreach ( $result as $x ) {
    echo '<tr>';
    foreach ( $x as $y ) {
        echo '<td>' . $y . '</td>';
    }
    echo '</tr>';
    }
    echo '</tbody>';
}

edited Jun 5, 2015 at 19:33

answered Jun 5, 2015 at 7:53

Brandon

5302 gold badges7 silver badges21 bronze badges

Comments

mdup · Accepted Answer · 2012-08-13 23:18:47Z

1

You might be interested in fetching with mysql_fetch_assoc() (so that you get the data in an associative array : keys=>value). In the keys are your column names ; so, for each line, you can loop through each column (with array_keys()) and print its value.

$results = mysql_query("SELECT * FROM demo");
while($row = mysql_fetch_assoc($results)) {
    foreach (array_keys($row) as $column) {
        echo $row[$key] . "</br>";
    }
}

(After that, you can cache array_keys($row) in a variable which is only set once, because its value won't change while you run through the results.)

answered Aug 13, 2012 at 23:18

mdup

8,6773 gold badges34 silver badges34 bronze badges

Comments

Paul Carlton · Accepted Answer · 2013-11-05 19:03:29Z

Even though it's been a while I'm going to put my 2 cents in: use functions (even better - classes). Creating tables from mysql results is a very common task.

<!DOCTYPE html>
<html>
    <head><title>Create Tables from MySQL using functions</title></head>
<body>
<?php
db_connect();
// assuming you have an auto increment id as the first column
$result = mysql_query("SELECT * FROM demo ORDER BY 1 DESC LIMIT 10");
print createTable(array_result($result));
?>
</body>
</html>

<?php 
/**
 * Quick mysql result function
 *
 * @param $result
 * @return array
 */
function array_result($result)
{
    $args = array();
    while ($row = mysql_fetch_assoc($result)) {
        $args[] = $row;
    }
    return $args;
}



/**
 * Connect to db
 * 
 * @param string $db_host
 * @param string $db
 */
function db_connect($db_host = "localhost", $db = "apploymentdevs")
{
    $connect = mysql_connect($db_host,"root", "root");
    if (!$connect) {
        die(mysql_error());
    }
    mysql_select_db($db);
}

/**
 * Create a table from a result set
 *
 * @param array $results
 * @return string
 */
function createTable(array $results = array())
{
    if (empty($results)) {
        return '<table><tr><td>Empty Result Set</td></tr></table>';
    }

    // dynamically create the header information from the keys
    // of the result array from mysql
    $table = '<table>';
    $keys = array_keys(reset($results));
    $table.='<thead><tr>';
    foreach ($keys as $key) {
        $table.='<th>'.$key.'</th>';
    }
    $table.='</tr></thead>';

    // populate the main table body
    $table.='<tbody>';
    foreach ($results as $result) {
        $table.='<tr>';
        foreach ($result as $val) {
            $table.='<td>'.$val.'</td>';
        }
        $table.='</tr>';
    }
    $table.='</tbody></table>';
    return $table;
}

Sgarz · Accepted Answer · 2012-08-13 23:07:20Z

0

I hope you know how to make a table in HTML, with <table>, <tr> and <td>.

Fix a table with that in your while-loop and use "SELECT * FROM demo LIMIT 10" as SQL-query.

answered Aug 13, 2012 at 23:07

Sgarz

3313 silver badges17 bronze badges

Collectives™ on Stack Overflow

Create HTML table from sql table

6 Answers 6

5 Comments

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related