PHP MySQL SELECT is used to access records from MySQL database.
Contents
Selecting Database Entries in MySQL
<?php
echo "<table>";
echo "<tr><th>Id</th><th>Name</th><th>Surname</th></tr>";
class table_rows extends recursive_array_iterator {
function __construct($it) {
parent::__construct($it, self::LEAVES_ONLY);
}
function current_row() {
return "<td>" . parent::current(). "</td>";
}
function begin_children() {
echo "<tr>";
}
function end_children() {
echo "</tr>" . "\n";
}
}
$server = 'server';
$user = 'user';
$pass = 'pass';
$db = 'db';
try {
$con = new PDO("mysql:host=$server;dbname=$db", $user, $pass);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//prepare the statement
$statement = $con->prepare("SELECT user_id, name, surname FROM users");
// make the query array associative
$statement->execute();
$result = $statement->setFetchMode(PDO::FETCH_ASSOC);
//iterate through the queried data
foreach(newtable_rows(newrecursive_array_iterator($stmt->fetchAll())) as $k => $v) {
echo $v;
}
} catch(PDOException $e) {
//set up an error message
echo "Error message: " . $e->getMessage();
}
$con = null;
echo "</table>";
?>The query example above selects specific entries from the database. Alternatively, you can use an asterisk (*) to select every column in the table.
Note: Storing all SQL statements in one line of code ensures they function properly.
Make MySQL Select Database Entries Using MySQLi
In the example below, we select the user_id, name and surname columns in the users table and display it on our web page:
<?php
$server = 'server';
$user = 'user';
$pass = 'pass';
$db = 'db';
// establish connection
$con = new mysqli($server, $user, $pass, $db);
// see if connection has been established properly
if ($conn->connect_error) {
die("Failed to connect: " . $con->connect_error);
}
$sql = "SELECT user_id, name, surname FROM users";
$result = $con->query($sql);
if ($result->num_rows > 0) {
// display data from the query
while($row = $result->fetch_assoc()) {
echo "User Id: " . $row["user_id"]. " - Name: " . $row["name"]. " - Surname: " . $row["surname"]. "<br/>";
}
} else {
echo "0 results";
}
$con->close();
?>First, we had to write the PHP SQL query selecting the user_id, name and surname columns in the users table. The next code line executed an PHP SQL query and placed the requested data into an array we call $result.
Once that is done, num_rows() was run through our array of results to see if any variables were returned. If any rows are present in the result set, the fetch_assoc() function places all of them in an associative array that can easily be looped. A while() loop is used to go through the array of results. It displays the results from the user_id, name and surname columns.
The example below shows how you can perform a PHP MySQL query using the procedural method in MySQLi:
<?php
server = 'server';
$user = 'user';
$pass = 'pass';
$db = 'db';
// establish connection
$con = mysqli_connect($server, $user, $pass, $db);
// see if the connection has been established properly
if (!$con) {
die("Failed to connect: " . mysqli_connect_error());
}
$sql = "SELECT user_id, name, surname FROM users";
$result = mysqli_query($con, $sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
// display data from the query
echo "User Id: " . $row["user_id"]. " - Name: " . $row["name"]. " - Surname: " . $row["surname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($con);
?>The next example shows how to display database entries as a table.
<?php
$server = 'server';
$user = 'user';
$pass = 'pass';
$db = 'db';
// establish connection
$con = new mysqli($server, $user, $pass, $db);
// see if the connection has been established properly
if ($con->connect_error) {
die("Failed to connect: " . $conn->connect_error);
}
$sql = "SELECT user_id, name, surname FROM users";
$result = $con->query($sql);
if ($result->num_rows > 0) {
echo "<table><tr><th>User Id</th><th>Name</th></tr>";
while ($row = $result->fetch_assoc()) {
// display data from the query
echo "<tr><td>".$row["user_id"]."</td><td>".$row["name"]." ".$row["surname"]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$con->close();
?>PDO and Prepared Statements for PHP MySQL Selection
We are going to use prepared statements to make MySQL select database entries.
Select the user_id, name and surname columns in the users table and display it in a table:
<?php
echo "<table>";
echo "<tr><th>Id</th><th>Name</th><th>Surname</th></tr>";
class table_rows extends recursive_array_iterator {
function __construct($it) {
parent::__construct($it, self::LEAVES_ONLY);
}
function current_row() {
return "<td>" . parent::current(). "</td>";
}
function begin_children() {
echo "<tr>";
}
function end_children() {
echo "</tr>" . "\n";
}
}
$server = 'server';
$user = 'user';
$pass = 'pass';
$db = 'db';
try {
$con = new PDO("mysql:host=$server;dbname=$db", $user, $pass);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//prepare the statement
$statement = $con->prepare("SELECT user_id, name, surname FROM users");
// make the query array associative
$statement->execute();
$result = $statement->setFetchMode(PDO::FETCH_ASSOC);
//iterate through the queried data
foreach(newtable_rows(newrecursive_array_iterator($stmt->fetchAll())) as $k => $v) {
echo $v;
}
} catch(PDOException $e) {
//set up an error message
echo "Error message: " . $e->getMessage();
}
$con = null;
echo "</table>";
?>