MySQL Select Statement using PHP with Source Code

What is MySQL Select Statement?

The MySQL Select statement is used in Selecting one or more records in MySQL database.

MySQL Select Statement using PHP in MySQLi Procedural, MySQLi OOP, and PDO. 

Syntax:

SELECT * from tableName;

or

SELECT column1,column2  from tableName;

The asterisks(*) symbol in our first example syntax represent all column in the database table.

In the second example, we specify the column name of a table.

Example using MySQLi (Object-Oriented):

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqltuts";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// sql to Select record table
$sql = "SELECT `LNAME`, `FNAME`, `ADDRESS` from `tblpeople`";
$results = mysqli_query($conn, $sql);
echo "

";
?>
<table border='1'>

<tr>
<th>LAST NAME</th>
<th>FIRST NAME</th>
<th>ADDRESS</th>
</tr>
<tbody>
<?php
if (mysqli_num_rows($results) > 0) { // output data of each row
while ($row = mysqli_fetch_assoc($results)) {
echo "


<tr>
<td>" . $row["LNAME"] . "</td>
<td>" . $row["FNAME"] . "</td>
<td>" . $row["ADDRESS"] . "</td>
</tr>

";
}
} else {
echo "0 results";
}

?>
</tbody>
</table>
<?php
mysqli_close($conn);
?>

Code explanation:

We just simply Select the Last name, First Name and Address of a person from our table, then we execute the query and store the results to the “$result” variable.

Next, using the num_rows function check if the result is greater than zero. And if the result is greater than zero, the fetch_assoc() function will store the result into an associative array so that we will be able to loop through the records.

The While Loop Loops through the result set and display the result inside the table data because we use table to display the result.

Example using MySQLi(Procedural):

<?PHP
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqltuts";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// sql to Insert record table
$sql = "SELECT `LNAME`, `FNAME`, `ADDRESS` from `tblpeople`";
$results = mysqli_query($conn , $sql);
echo "

";
?>
<table border='1'>

<tr>
<th>LAST NAME</th>
<th>FIRST NAME</th>
<th>ADDRESS</th>
</tr>
<tbody>
	<?php
if (mysqli_num_rows($results) > 0) {// output data of each row
while($row = mysqli_fetch_assoc($results)) {
echo "


<tr>
<td>" . $row["LNAME"] . "</td>
<td>" . $row["FNAME"] . "</td>
<td>" . $row["ADDRESS"] . "</td>
</tr>

";
    }
} else {
    echo "0 results";
}

?>
</tbody>
</table>
<?php
mysqli_close($conn);
?>

Example using PDO:

$sql = "SELECT `LNAME`, `FNAME`, `ADDRESS` from `tblpeople`";
$query = $conn->prepare( $sql );
$query->execute();
$results = $query->fetchAll( PDO::FETCH_ASSOC );

?>

foreach( $results as $row ){
echo "";
}
}
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
echo "
<table border="1">
<tbody>
<tr>
<th>LAST NAME</th>
<th>FIRST NAME</th>
<th>ADDRESS</th>
</tr>
<tr>
<td>" . $row["LNAME"]. "</td>
<td>" . $row["FNAME"]. "</td>
<td>" . $row["ADDRESS"]. "</td>
</tr>
</tbody>
</table>
";
?>

The Output of the Code above looks as shown below.

Summary

In summary, we have discussed here how to use MySQL select statement using PHP in MySQLi Procedural, MySQLi OOP, and PDO. 

If you have any questions or suggestions about this tutorial using PHP and MySQLi or PDO, please feel free to leave a comment below.