Understanding PHP MySQL Select

In this article, we will explore the PHP MySQL Select statement, as well as how to use it to retrieve specific rows and columns of data.

If you are a beginner or a seasoned programmer, this article is going to provide you with the knowledge you need to know about Php Mysql Select statements so that you can utilize the data from your database in a good way.

Data retrieval and manipulation is an essential skill for businesses, organizations, and individuals alike when it comes to keeping track of and manipulating data.

MySQL database is one of the most popular ways of storing and managing data, and is widely used as a tool for web developers.

Table Of Content-

PHP MySQL Select – What is it?

The PHP MySQL Select statement is used to retrieve data from one or more tables in a MySQL database. It is the most commonly used SQL statement in PHP applications, as it allows you to fetch and display data on web pages.

PHP MySQL SELECT statement can be used to retrieve all the data from a table, or a subset of the data based on specific criteria.

Syntax

The syntax of the PHP MySQL Select statement is as follows:

SELECT column1, column2, column3, ... FROM table_name WHERE condition1 AND condition2 OR condition3 ORDER BY column_name ASC|DESC LIMIT start, count

In above syntax, column1, column2, column3, … represent the columns that we want to retrieve data from. We can use the * (asterisk) character to retrieve all columns from the table. The table_name is the name of the table that we want to retrieve data from.

The WHERE clause is used to specify the conditions that the retrieved data must meet. We can use operators such as =, <, >, <=, >=, and <> to compare values in the table. We can also use logical operators such as AND, OR, and NOT to combine multiple conditions.

The ORDER BY statement is used to sort the retrieved data in ascending (ASC) or descending (DESC) order based on a specific column.

The LIMIT is used to limit the number of rows that are returned by the SELECT statement.

The start parameter specifies the starting row number, and the count parameter specifies the number of rows to retrieve.

PHP MySQL Select Data

You can use the PHP MySQL SELECT statement to select data from one or more tables in a database.

Let’s look at some examples of using the PHP MySQL Select statement:

SELECT column_name(s)
FROM table_name

Or ( * ) is used for selecting all data columns from the table:

SELECT * FROM customers;

This statement retrieves all the data from the customers table.

Retrieving data based on a condition:

SELECT * FROM customers WHERE country='USA';

This statement retrieves all the data from the customers table where the country is USA.

Now retrieve data based on multiple conditions:

SELECT * FROM customers WHERE country='USA' AND city='New York';

This statement retrieves all the data from the customers table where the country is USA and the city is New York.

Retrieving data in ascending order:

SELECT * FROM customers ORDER BY first_name ASC;

This statement retrieves all the data from the customers table and sorts it in ascending order based on the first_name column.

Retrieving a subset of data:

SELECT * FROM customers LIMIT 10, 20;

This statement retrieves 20 rows of data starting from the 11th row in the customers table.

Select Data With MySQLi

Here is an example that SELECTs the columns (id, firstname, lastname, email, age, and gender) from the Users table and displays them on the page as follows:

Example (MySQLi Object-oriented): 

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn = new mysqli($servername, $username, $password, $dbname); // Checking connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT id, firstname, lastname, email, age, gender FROM Users"; $result = $conn->query($sql); if ($result->num_rows > 0) { // Printing data of each row while ($row = $result->fetch_assoc()) { echo "id: " . $row["id"] . "<br>First Name: " . $row["firstname"] . "<br>Last Name: " . $row["lastname"] . "<br>Email: ".$row["email"]."<br>Age: ".$row["age"]."<br>Gender: ".$row["gender"].""; } } else { echo "0 results"; } $conn->close(); ?>

Example Explanation

Firstly, we need to create an SQL query that will select the id, firstname, lastname, email, age, and gender columns from the Users table. Next, the program runs the query and places the results into a variable called $result, which stores the results of the query.

After that, num_rows() checks whether there are more than zero rows returned as a result of the query. A function called fetch_assoc() creates an associative array, which can be used to loop through all the rows returned if there are more than zero returned rows. By using the while() function, we loop through the result set and output the data from the id, firstname, lastname, email, age, and gender columns from the result set.

As an example, the following is a MySQLi procedural way of displaying the same information as shown above:

Example (MySQLi Procedural): 

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Checking connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "SELECT id, firstname, lastname, email, age, gender FROM Users"; $result = mysqli_query($conn, $sql); if (mysqli_num_rows($result) > 0) { // Printing data of each row while ($row = mysqli_fetch_assoc($result)) { echo "id: " . $row["id"] . "<br>First Name: " . $row["firstname"] . "<br>Last Name: " . $row["lastname"] . "<br>Email: ".$row["email"]."<br>Age: ".$row["age"]."<br>Gender: ".$row["gender"].""; } } else { echo "0 results"; } mysqli_close($conn); ?>
There is also the option of putting the results into an HTML table as follows:

Example (MySQLi Object-oriented): 

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn = new mysqli($servername, $username, $password, $dbname); // Checking connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT id, firstname, lastname, email, age, gender FROM Users"; $result = $conn->query($sql); if ($result->num_rows > 0) { echo "<table><tr><th>ID</th><th>Name</th><th>Email</th><th>Age</th><th>Gender</th></tr>"; //Printing data of each row while ($row = $result->fetch_assoc()) { echo "<tr><td>" . $row["id"] . "</td><td>" . $row["firstname"] . " " . $row["lastname"] . "</td> <td>" . $row["email"] . "</td><td>" . $row["age"] . "</td><td>" . $row["gender"] . "</td></tr>"; } echo "</table>"; } else { echo "0 results"; } $conn->close(); ?>

Select Data – PDO + Prepared Statements

Prepared statements with PDO are used in the following example.

An HTML table is created by selecting the fields id, firstname, lastname, email, age, and gender from the Users table, and displaying them in the following way:

Example (PDO): 

<?php echo "<table style='border: solid 1px black;'>"; echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th><th>Email</th><th>Age</th><th>Gender</th></tr>"; class TableRows extends RecursiveIteratorIterator { function __construct($iterable) { parent::__construct($iterable, 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>" . " "; } } $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; 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, email, age, gender FROM Users"); $stmt->execute(); //resulting array to associative $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); foreach (new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $key => $value) { echo $value; } } catch (PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; echo "</table>"; ?>

Example Explanation

In above example first we connect to a MySQL database, retrieve data from a table called “Users“, and display it in an HTML table.
  • An HTML table structure is created with a table header that lists the column names.
  • Next, a custom class called TableRows is defined that extends the RecursiveIteratorIterator class.
  • This custom class is used to iterate over the results of the SELECT query and format them as table cells.
  • The current() method is used to retrieve the current element and format it as a table cell.
  • The beginChildren() method is called when a new row is started and creates a new table row.
  • The endChildren() method is called when the current row is finished and closes the table row.
  • Then we establishes a connection to the MySQL database using PDO (PHP Data Objects) and prepares a SELECT statement to retrieve data from the “Users” table.
  • The execute() method is called to execute the query, and the resulting data is fetched and converted to an associative array using the setFetchMode() method.
  • Finally, the fetched data is iterated using a foreach loop that passes the results to the TableRows class to format them as table cells.
  • The formatted data is then echoed out to the HTML table.
If an error occurs during the database connection or query execution, a catch block handles the exception and displays an error message.
We value your feedback.
+1
0
+1
0
+1
0
+1
0
+1
0
+1
0
+1
0
Subscribe To Our Newsletter
Enter your email to receive a weekly round-up of our best posts. Learn more!
icon
By subscribing, you agree with our privacy policy and our terms of service.

Leave a Reply

Your email address will not be published. Required fields are marked *