PHP MySQL reads data
Read data from a MySQL database
SELECT statement is used to read data from the data table:
SELECT column_name(s) FROM table_name
To learn more about SQL knowledge, please visit our SQL tutorial .
We read the following examples from the id table MyGuests, firstname and lastname columns and data displayed on the page:
Examples (MySQLi - Object Oriented)
<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = new mysqli ($ servername, $ username, $ password, $ dbname);
// Test connection
if ($ conn-> connect_error) {
die ( "Connection failed:" $ conn-> connect_error.);
}
$ Sql = "SELECT id, firstname, lastname FROM MyGuests";
$ Result = $ conn-> query ($ sql);
if ($ result-> num_rows> 0) {
// Output each row of data
while ($ row = $ result-> fetch_assoc ()) {
. Echo "<br> id:" $ row [ "id"] "- Name:" $ row [ "firstname"] "" [lastname "];... $ Row."
}
} Else {
echo "0 results";
}
$ Conn-> close ();
?>
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = new mysqli ($ servername, $ username, $ password, $ dbname);
// Test connection
if ($ conn-> connect_error) {
die ( "Connection failed:" $ conn-> connect_error.);
}
$ Sql = "SELECT id, firstname, lastname FROM MyGuests";
$ Result = $ conn-> query ($ sql);
if ($ result-> num_rows> 0) {
// Output each row of data
while ($ row = $ result-> fetch_assoc ()) {
. Echo "<br> id:" $ row [ "id"] "- Name:" $ row [ "firstname"] "" [lastname "];... $ Row."
}
} Else {
echo "0 results";
}
$ Conn-> close ();
?>
The following example reads all records MyGuests table and displayed in an HTML table:
Examples of (PDO)
<? Php
echo "<table style = 'border: solid 1px black;'>";
echo "<tr> <th> Id </ th> <th> Firstname </ th> <th> Lastname </ th> <th> Email </ th> <th> Reg date </ th> </ tr> ";
class TableRows extends RecursiveIteratorIterator {
function __construct ($ it) {
parent :: __ construct ($ it, self :: LEAVES_ONLY);
}
function current () {
return "<td style = 'width: 150px; border: 1px solid black;'>". parent :: current () "</ td>";.
}
function beginChildren () {
echo "<tr>";
}
function endChildren () {
echo "</ tr>" "\ n".;
}
}
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDBPDO";
try {
$ Conn = new PDO ( "mysql: host = $ servername; dbname = $ dbname", $ username, $ password);
$ Conn-> setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
$ Stmt = $ conn-> prepare ( "SELECT * FROM MyGuests");
$ Stmt-> execute ();
// Set result set is an associative array
$ Result = $ stmt-> setFetchMode (PDO :: FETCH_ASSOC);
foreach (new TableRows (new RecursiveArrayIterator ($ stmt-> fetchAll ())) as $ k => $ v) {
echo $ v;
}
$ Dsn = null;
}
catch (PDOException $ e)
{
echo "Error:" $ e-> getMessage ();.
}
$ Conn = null;
echo "</ table>";
?>
echo "<table style = 'border: solid 1px black;'>";
echo "<tr> <th> Id </ th> <th> Firstname </ th> <th> Lastname </ th> <th> Email </ th> <th> Reg date </ th> </ tr> ";
class TableRows extends RecursiveIteratorIterator {
function __construct ($ it) {
parent :: __ construct ($ it, self :: LEAVES_ONLY);
}
function current () {
return "<td style = 'width: 150px; border: 1px solid black;'>". parent :: current () "</ td>";.
}
function beginChildren () {
echo "<tr>";
}
function endChildren () {
echo "</ tr>" "\ n".;
}
}
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDBPDO";
try {
$ Conn = new PDO ( "mysql: host = $ servername; dbname = $ dbname", $ username, $ password);
$ Conn-> setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
$ Stmt = $ conn-> prepare ( "SELECT * FROM MyGuests");
$ Stmt-> execute ();
// Set result set is an associative array
$ Result = $ stmt-> setFetchMode (PDO :: FETCH_ASSOC);
foreach (new TableRows (new RecursiveArrayIterator ($ stmt-> fetchAll ())) as $ k => $ v) {
echo $ v;
}
$ Dsn = null;
}
catch (PDOException $ e)
{
echo "Error:" $ e-> getMessage ();.
}
$ Conn = null;
echo "</ table>";
?>