display data from SQL database into php/ html table

OK I have a database on phpmyadmin (sql) and I want to display one of my tables into a table on HTML/PHP. I have searched online and can not implement this feature, so I’m wondering if someone could help me with the coding on this?

database = 'hrmwaitrose' username = 'root' host = 'localhost' 

NO PW

I would like to display the data from table name employee

Add Comment
4 Answer(s)

You say you have a database on PhpMyAdmin, so you are using MySQL. 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>" . $row['name'] . "</td><td>" . $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.

Add Comment

Here’s a simple function I wrote to display tabular data without having to input each column name: (Also, be aware: Nested looping)

function display_data($data) {     $output = '<table>';     foreach($data as $key => $var) {         $output .= '<tr>';         foreach($var as $k => $v) {             if ($key === 0) {                 $output .= '<td><strong>' . $k . '</strong></td>';             } else {                 $output .= '<td>' . $v . '</td>';             }         }         $output .= '</tr>';     }     $output .= '</table>';     echo $output; } 

UPDATED FUNCTION BELOW

Hi Jack,

your function design is fine, but this function always misses the first dataset in the array. I tested that.

Your function is so fine, that many people will use it, but they will always miss the first dataset. That is why I wrote this amendment.

The missing dataset results from the condition if key === 0. If key = 0 only the columnheaders are written, but not the data which contains $key 0 too. So there is always missing the first dataset of the array.

You can avoid that by moving the if condition above the second foreach loop like this:

function display_data($data) {     $output = "<table>";     foreach($data as $key => $var) {         //$output .= '<tr>';         if($key===0) {             $output .= '<tr>';             foreach($var as $col => $val) {                 $output .= "<td>" . $col . '</td>';             }             $output .= '</tr>';             foreach($var as $col => $val) {                 $output .= '<td>' . $val . '</td>';             }             $output .= '</tr>';         }         else {             $output .= '<tr>';             foreach($var as $col => $val) {                 $output .= '<td>' . $val . '</td>';             }             $output .= '</tr>';         }     }     $output .= '</table>';     echo $output; } 

Best regards and thanks – Axel Arnold Bangert – Herzogenrath 2016

and another update that removes redundant code blocks that hurt maintainability of the code.

function display_data($data) { $output = '<table>'; foreach($data as $key => $var) {     $output .= '<tr>';     foreach($var as $k => $v) {         if ($key === 0) {             $output .= '<td><strong>' . $k . '</strong></td>';         } else {             $output .= '<td>' . $v . '</td>';         }     }     $output .= '</tr>'; } $output .= '</table>'; echo $output; 

}

Add Comment

Look in the manual http://www.php.net/manual/en/mysqli.query.php

<?php $mysqli = new mysqli("localhost", "my_user", "my_password", "world");  /* check connection */ if ($mysqli->connect_errno) {     printf("Connect failed: %s\n", $mysqli->connect_error);     exit(); }  /* Create table doesn't return a resultset */ if ($mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City") === TRUE) {     printf("Table myCity successfully created.\n"); }  /* Select queries return a resultset */ if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {     printf("Select returned %d rows.\n", $result->num_rows);      /* free result set */     $result->close(); }  /* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */ if ($result = $mysqli->query("SELECT * FROM City", MYSQLI_USE_RESULT)) {      /* Note, that we can't execute any functions which interact with the        server until result set was closed. All calls will return an        'out of sync' error */     if (!$mysqli->query("SET @a:='this will not work'")) {         printf("Error: %s\n", $mysqli->error);     }     $result->close(); }  $mysqli->close(); ?> 
Add Comment

refer to http://www.w3schools.com/php/php_mysql_select.asp . If you are a beginner and want to learn, w3schools is a good place.

<?php     $con=mysqli_connect("localhost","root","YOUR_PHPMYADMIN_PASSWORD","hrmwaitrose");     // Check connection     if (mysqli_connect_errno())       {       echo "Failed to connect to MySQL: " . mysqli_connect_error();       }      $result = mysqli_query($con,"SELECT * FROM employee");      while($row = mysqli_fetch_array($result))       {       echo $row['FirstName'] . " " . $row['LastName']; //these are the fields that you have stored in your database table employee       echo "<br />";       }      mysqli_close($con);     ?> 

You can similarly echo it inside your table

<?php  echo "<table>";  while($row = mysqli_fetch_array($result))           {           echo "<tr><td>" . $row['FirstName'] . "</td><td> " . $row['LastName'] . "</td></tr>"; //these are the fields that you have stored in your database table employee           }  echo "</table>";  mysqli_close($con); ?> 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.