display data from SQL database into php/ html table

61
April 17, 2019, at 4:00 PM

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

Answer 1

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.

Answer 2

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

Answer 3

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();
?>
Answer 4

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);
?>
READ ALSO
Why can null be written with a backslash?

Why can null be written with a backslash?

In all PHP manuals, it says that null, true, and false are internal valuesHowever, nowhere does it say why they can be written with backslash: \null, \false, \true

67
Why does the same php UnityWebRequest does not work on plesk server but works on my localhost?

Why does the same php UnityWebRequest does not work on plesk server but works on my localhost?

I am developing an app in unity and I am currently coding the login functionalityI have also created a database for my users in plesk

35
$_POST does not update value

$_POST does not update value

Currently trying to update a defined value with a form but it does not work for meI did try several things even a function that updates the value with a +1 that works on another page so I am not sure what the problem with my form is

35
How to run PHP file in Sql server 2012 with argument

How to run PHP file in Sql server 2012 with argument

I am able to execute PHP script using EXEC masterdbo

50