PHP MySQL Select Data

Select Data From a MySQL Database

To select data from mysql, you must have some basic knowledge of SQL SELECT query.

The SELECT command is used to select data from Mysql tables.

The following syntax is used to select data from a table with one or more specified columns:

SELECT column_name(s) FROM table_name

You can select ALL columns from the table with the * character:

SELECT * FROM table_name

You can use more options with SELECT command to filter data using WHERE command, getting ascending or descending ordered result using ORDER BY command, limiting the query result using LIMIT command and more.

You can also search for these topics, php Select Data From a MySQL Database, php mysql how to select column_name from table, php mysql how to select all columns from table, php mysql select data and split on pages, php mysql select data into array, php mysql select data from multiple tables, php mysql select large data, php select data from mysql database.

Select Data From MySql With MySQLi and PDO

PHP provides many useful methods and properties to work with database result. We are going to use num_rows property and fetch_assoc() method in this below program.

We will take a sample table called "Employee" and it contains three columns ("id", "name", and "city") and three records.

ID	Name	City
1	Suresh	Paris
2	Babu	London
3	Kumar	New York

Example (MySQLi Object-oriented)

The num_rows property is used to return number of records available in the query result.

The fetch_assoc() method return query results (All rows and cols) as a associative array.

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM Employee";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br /> ID = " . $row["id"]. " , Name = ". $row["name"]. " , City = " . $row["city"] . "<br />";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Output :-

ID = 1 , Name = Suresh , City = Paris
ID = 2 , Name = Babu , City = London
ID = 3 , Name = Kumar , City = New York

Code Explained:

First, we establish a SQL query, which select all columns and rows from the Employee table. The next line of code will conduct the query and place the returned information into a $result variable.

The $result variable contains the query result.

The num_rows property then checks for the return of more than zero rows.

If more than 0 rows are returned, the fetch_assoc() function can be used to place all the results (rows and cols) into an associated array. The while() loops the results of the ID, name and city columns and prints the data of each row one by one.

Example (MySQLi Procedural)

The mysqli_num_rows() function is used to return number of records available in the query result.

The mysqli_fetch_assoc() method return query results (All rows and cols) as a associative array.

The following example displays the same thing as the previous one, but in a MySQLi procedural manner :

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM Employee";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "<br /> ID = " . $row["id"]. " , Name = ". $row["name"]. " , City = " . $row["city"] . "<br />";
    }
} else {
    echo "0 results";
}
mysqli_close($conn);
?>

You can also search for these topics, php Select Data With MySQLi, Example for php MySQLi Object-oriented, Example for php mysqli procedural, Example for php mysqli function num_rows(), php mysql select data mysqli object-oriented, php mysql select data mysqli procedural, php mysql select data with pdo example.

Example (PDO)

The fetchAll() method return query results (All rows and cols) as a associative array.

The following example displays the same thing as the previous one, but in a MySQLi procedural manner :

<?php
try{
// Create connection
$conn = new PDO('mysql:host=localhost;dbname=myDB', "root", "");
// build query and create statement
$stm = $conn->query('SELECT * FROM Employee');
// fetch all rows into array
$rows = $stm->fetchAll();
// iterate over array by index and by name
foreach($rows as $row) {    
    echo "<br /> ID = " . $row["id"]. " , Name = ". $row["name"]. " , City = " . $row["city"] . "<br />";
}
} catch(PDOException $e) {
  echo $sql . "<br />" . $e->getMessage();
}
// Close connection
$conn = null;
?>

Related Links

php regex multiple matches

php abstract class static method

php connect to mysql database

php constructor overloading

php addslashes

php include header and footer

php loop x times

php random string

create xml from array php

php 8 mysql extension

You can also search for these topics, php mysqli Select Data With PDO + Prepared Statements, pdo + mysqli select the prepared statement using php, php mysqli select data Example PDO.

Display MySql Query Results in HTML Table Format

You may also use HTML tables to display the query results in a tabular format:

Example (MySQLi Object-oriented) :- The following program will display the query result in a table format using html tables:

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM Employee";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    echo "<table border=1><tr><th>ID</th><th>Name</th><th>City</th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["id"]. "</td><td>". $row["name"]. "</td><td>" . $row["city"] . "</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}
$conn->close();
?>

Output:-

ID	Name	City
1	Suresh	Paris
2	Babu	London
3	Kumar	New York

Select and Filter Data From a MySQL Database

To filter records, the WHERE clause is utilized.

The WHERE clause only allows records that meet a specific requirement to be extracted.

SELECT column_name(s) FROM table_name WHERE column_name operator value

Select and Filter Data With MySQLi

This example picks the id, name, and city from the "Employee" table where the city is "London", and shows it on the page: The following example is :

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM Employee WHERE city = 'London'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br /> ID = " . $row["id"]. " , Name = ". $row["name"]. " , City = " . $row["city"] . "<br />";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Output :-

ID = 2 , Name = Babu , City = London

You can also search for these topics, php mysql use the where clause, php mysql use the where clause example, how to select and filter data with mysqli using php, php mysql where clause array.

Select and Order Data From a MySQL Database

In order to arrange the results in ascending or descending order, the ORDER BY clause is needed.

By default, the ORDER BY clause orders the records in ascending. The DESC keyword is used to sort the records in ascending order.

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

Select and Order Data With MySQLi

In the example below, The city field will be used to sort the data, table in "Employee" are selected and shown on the page :

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM Employee ORDER BY city";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br /> ID = " . $row["id"]. " , Name = ". $row["name"]. " , City = " . $row["city"] . "<br />";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Output :-

ID = 2 , Name = Babu , City = London
ID = 3 , Name = Kumar , City = New York
ID = 1 , Name = Suresh , City = Paris

Related Links

get message exception php

php mysql delete record

php call function from class

user defined function in php

You can also search for these topics, php mysql use the order by clause, php mysql use the order by clause example, Example for order data mysql using php.

Limit Data Selections From a MySQL Database

The LIMIT clause in MySQL is used to limit how many records are returned.

LIMIT Data highly useful on huge tables to easily implement multi-page results or pagination with SQL. A huge quantity of recordings can have an effect on performance.

Suppose we would like to pick from the "Employee" table all records from 1 - 20 (included)." The SQL query looks like that :

$sql = "SELECT * FROM Employee LIMIT 20";

Once the above SQL query is executed, the first 20 records will be returned.

If you want to pick records within a range, Mysql also has a solution for this : use the OFFSET clause.

"Return only 10 records and start record 16" the following SQL query reads (OFFSET 15) :

$sql = "SELECT * FROM Employee LIMIT 10 OFFSET 15";

In order to get the same outcome you can also use a short syntax :

$sql = "SELECT * FROM Employee LIMIT 15, 10";

When you use a comma, you will notice that the numbers are reversed.

You can also search for these topics, php mysql limit data selections, php mysql limit data selections example, mysql limit data selections using php, check the mysql limit data selection using php.

All Tutorials