WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

PHP Select Data From MySQL

Read data from MySQL database

The SELECT statement is used to read data from the data table:

  SELECT column_name   (  s  )   FROM table_name  

We can use * to read all fields in the data table:

SELECT * FROM table_name

Using MySQLi

In the following example, we read the id, firstname and lastname columns from the MyGuests table of the myDB database and display them on the page:

Instance (MySQLi-Object Oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create 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 result"; } $conn->close(); ?>

The above code is parsed as follows:

First, we set up an SQL statement to read the id, firstname and lastname fields from the MyGuests data table. We then use this SQL statement to fetch the result set from the database and assign it to the variable $ result.

The function num_rows () determines the data returned.

If multiple pieces of data are returned, the function fetch_assoc () puts the join set into the associative array and loops the output. while () loops out the result set and outputs 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-process oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create 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 result"; } mysqli_close($conn); ?>


Using PDO (+ preprocessing)

The following example uses prepared statements.

The id, firstname and lastname fields in the MyGuests table are selected and placed 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 to 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>"; ?>