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
This question is related to
php
html
mysql
phpmyadmin
xampp
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.
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;
}
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;
}
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);
?>
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();
?>
Source: Stackoverflow.com