HOME | PHP Tutorial | CSS
facebook icon
AgerNic.com
WEB DEVELOPER SITE, HTML, CSS, PHP
HTML Tutorial

CSS Tutorial

Display data from SQL database with MySQLi into php - html table


OK, you have a database on phpmyadmin (sql) and you want to display one of table into a table on HTML PHP.

Select Data With MySQLi and display - how to create - example


In the following example selects the id, name and surname columns from the users table and displays it on the page:

Example 1: (MySQLi Object-oriented)

<?php
$servername = "localhost";
$username = "your-username";
$pass = "your-password";
$dbname = "my-DB";

// Create connection
$link = new mysqli($servername, $username, $pass, $dbname);
// Check connection
if ($link->connect_error) {
die("Connection failed: " . $link->connect_error);

echo "<table>";
$sql = "SELECT id, name, surname FROM Users order by rand() limit 4";
$result = $link->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["id"]. " </td><td>" ". $row["name"]. "</td><td> " . $row["surname"]. " </td></tr> ";
}
} else {
echo "0 results";
}

echo "</table>";
$conn->close();
?>

function - num_rows() - checks if there are more than zero rows returned.

If there are more than zero rows returned, the function -fetch_assoc() - puts all the results into an associative array that we can loop through. The - while() loop - loops through the result set and outputs the data from the id, name and surname columns.

Note: SELECT column_name(s) FROM table_name - - or we can use the * character to select ALL columns from a table: SELECT * FROM table_name

MySQLi procedural way: Example - How to create

The following example shows the same as the example above, in the MySQLi procedural way:

Example 2: (MySQLi Procedural)

<?php
$servername = "localhost";
$username = "your-username";
$pass = "your-password";
$dbname = "my-DB";

// Create connection
$link = mysqli_connect($servername, $username, $pass, $dbname);
// Check connection
if (!$link) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "<table>";
$sql = "SELECT id, name, surname FROM Users order by rand() limit 4";
$result = mysqli_query($link, $sql);

if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "<tr><td>" . $row["id"]. " </td><td>" ". $row["name"]. "</td><td> " . $row["surname"]. " </td></tr> ";
}
} else {
echo "0 results";
}

echo "</table>";
$conn->close();
?>

The results -

Display data from SQL, database with MySQLi, php html table Display data from SQL database with MySQLi into php - html table - sql tutorial

2563. SQL Aliases - SQL Tutorial

http://www.sql-tutorial.net/SQL-Aliases.asp

2586. SQL SUM - SQL Tutorial

http://www.sql-tutorial.net/SQL-SUM.asp

2561. ETL - Extract, Transform and Load - SQL Tutorial

http://www.sql-tutorial.net/ETL.asp

3228. SQL - Syntax, sql tutorial
SQL - Syntax sql tutorial
https://www.agernic.com/sql-tutorial/sql-syntax.html

3291. SQL IN Operator - example, sql tutorial
SQL IN Operator - example sql tutorial
https://www.agernic.com/sql-tutorial/sql-in-operator-with-example.html

3295. SQL mysql order by columns, by date, by asc, by desc, random with PHP, sql tutorial
SQL mysql order by columns, by date, by asc, by desc, random with PHP sql tutorial
https://www.agernic.com/sql-tutorial/sqlL-mysql-order-by-columns-asc--desc--random-with-php.html

Your add here
How to get PHP errors to display?
How to prevent sql injection in php mysqli
How do I get PHP errors to display - PHP error_reporting()
mysql_query(): The mysql extension is deprecated and will be removed i
Function eregi_replace() is deprecated in php - How to fix
PHP delete an element from an array
Uncaught Error: Call to undefined function MYSQL_NUM_ROWS() in
Deprecated: mysql_connect() mysql extension is deprecated and will be
mysqli_fetch_array() expects parameter 1 to be mysqli_result boolean g
SQL IN Operator - example
SQL BETWEEN Operator
SQL like syntax and example
SQL WHERE AND & OR multiple conditions
SQL create table primary key autoincrement
PHP mysqli_connect () Function
SQL COUNT(), AVG() and SUM() Functions
Display data from SQL database with MySQLi into php - html table
SQL CREATE DATABASE - example
SQL mysql order by columns, by date, by asc, by desc, random with PHP
PHP mysqli_query() Function - mysqli query
SQL delete row - SQL delete table
SQL update - SQL update select
Insert into SQL - Insert into table SQL - Insert data in sql
Login in PHP - simple login script with PHP MYSQL