Read data from MySQL database
The SELECT statement is used to read data from a data table:
SELECT column_name(s) FROM table_name
We can use the * sign to read all fields in the data table:
SELECT * FROM table_name
To learn more about SQL, visit our SQL tutorials.
UsingMySQLi
In the following example, we read the data of the id, firstname and lastname columns from the MyGuests table of the myDB database and display it on the page:
Example (MySQLi - Object Oriented)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create a connection $conn = new mysqli ( $servername , $username , $password , $dbname ) ; // Check 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 data while ( $row = $result -> fetch_assoc ( ) ) { echo " id: " . $row [ " id " ] . " - Name: " . $row [ " firstname " ] . " " . $row [ " lastname " ] . " <br> " ; } } else { echo " 0 results " ; } $conn -> close ( ) ; ?> The above code is analyzed as follows:
First, we set up the SQL statement to read the three fields id, firstname and lastname from the MyGuests data table. Then we use the modified SQL statement to retrieve the result set from the database and assign it to the copied variable $result.
The function num_rows() determines the returned data.
If multiple pieces of data are returned, the function fetch_assoc() puts the combined set into an associative array and outputs it in a loop. while() loops out the result set and outputs the three field values of id, firstname and lastname.
The following example uses MySQLi's process-oriented approach, and the effect is similar to the above code:
Example (MySQLi - Procedure Oriented)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create a connection $conn = mysqli_connect ( $servername , $username , $password , $dbname ) ; // Check connection if ( ! $conn ) { die ( " Connection failed: " . mysqli_connect_error ( ) ) ; } $sql = " SELECT id, firstname, lastname FROM MyGuests " ; $result = mysqli_query ( $conn , $sql ) ; if ( mysqli_num_rows ( $result ) > 0 ) { // output data while ( $row = mysqli_fetch_assoc ( $result ) ) { echo " id: " . $row [ " id " ] . " - Name: " . $row [ " firstname " ] . " " . $row [ " lastname " ] . " <br> " ; } } else { echo " 0 results " ; } mysqli_close ( $conn ) ; ?> Using PDO (+ preprocessing)
The following examples use prepared statements.
Selected the id, firstname and lastname fields from the MyGuests table and placed them in the HTML table:
Example(PDO)
<?php echo " <table style='border: solid 1px black;'> " ; echo " <tr><th>Id</th><th>Firstname</th><th>Lastname</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 id, firstname, lastname FROM MyGuests " ) ; $stmt -> execute ( ) ; // Set the result set as an associative array $result = $stmt -> setFetchMode ( PDO :: FETCH_ASSOC ) ; foreach ( new TableRows ( new RecursiveArrayIterator ( $stmt -> fetchAll ( ) ) ) as $k => $v ) { echo $v ; } } catch ( PDOException $e ) { echo " Error: " . $e -> getMessage ( ) ; } $conn = null ; echo " </table> " ; ?>
