Description
“PHP Select Data From MySQL” chapter allows to select the data from the database. MySQL SELECT statement allows to fetch zero or multiple rows from database tables or views. The SELECT statement is one among mostly used MySQL queries.
The SELECT statement returns a result that comprises of rows and columns, also called as a Result Set.
Syntax
SELECT field1,field2,…fieldN table_name1,table_name2…[WHERE Clause]
[OFFSET M][LIMIT N]
[OFFSET M][LIMIT N]
The SELECT statement has several clauses:
-
SELECTselects that table columns in which the data to be inserted. -
FROMdenotes the table from which the data is retrieved. -
JOINretrieves data from various tables depending on join conditions. -
WHEREclause applies filters to rows to grab the data. -
GROUP BYbrings the rows together to apply combination functions on each cluster. -
HAVINGfilters group depending on groups defined by GROUP BY clause defined. -
ORDER BYdefines result set order that is returend. -
LIMITobliges different rows that are returned.
Example
MySQLi (Object-oriented)
[php]
<!DOCTYPE html>
<html>
<body>
<?php
$HostName = "localhost";
$UserName = "root";
$Password = "password";
$dbname = "accounts";
// Create connection
$connection = new mysqli($HostName, $UserName, $Password, $dbname);
// Check connection
if ($connection->connect_error)
{
die("Connection failed: " . $connection->connect_error);
}
$sql = "SELECT id, First, Last FROM users2";
$result = $connection->query($sql);
if ($result->num_rows > 0)
{
// output data of each row
while($row = $result->fetch_assoc())
{
echo "id: " . $row["id"]. " – Name: " . $row["First"]. " " . $row["Last"]. "<br>";
}
}
else
{
echo "0 results";
}
$connection->close();
?>
</body>
</html>
[/php]
The output looks like below in phpmyadmin with all the selected column values.
MySQLi (Procedural)
[php]
<!DOCTYPE html>
<html>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "Student";
// Create connection
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
//Selecting the details
$sql = "SELECT id, First, Last FROM users2";
$result = mysqli_query($connection, $sql);
if (mysqli_num_rows($result) > 0)
{
// output data of each row
while($row = mysqli_fetch_assoc($result))
{
echo "id: " . $row["id"]. " – Name: " . $row["First"]. " " . $row["Last"]. "<br>";
}
}
else
{
echo "0 results";
}
mysqli_close($connection);
?>
</body>
</html>
[/php]
Points
- SELECT statement fetch the data from database tables or views.
- Result set is the result with columns and rows.