The SQL SELECT statement is used to select or retrieve data from a database.
SQL SELECT will allow us to retrieve data in one or more columns of a table in a database.
We will be able to use it with different SQL clauses and declarations like WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, etc.
The SQL SELECT command will work with all relational databases like MySQL, PostgreSQL, etc.
Syntax of a basic SQL SELECT statement
The most basic use that we can make of a SELECT SQL will be to select data in a column of a table without more criteria. In this case, we will use the SQL syntax SELECT column_name FROM table_name.
To select data from multiple tables, use the syntax SELECT column_name1, column_name2 FROM table_name.
Finally, to select all the data in a table, you can use the wildcard * and the following syntax: SELECT * FROM table_name.
Example of using SQL SELECT with phpMyAdmin
To test the SQL SELECT command, we will need a database. I therefore invite you to start your servers (using MAMP, WAMP, etc.) and go to your phpMyAdmin (accessible from the start page of WAMP, MAMP…).
Then, I invite you to create a new database which we will call for example a course. This users table contains 6 columns and eight fields as in the image below:
We will finally be able to perform our SQL SELECT queries.
To do this, we will use the SQL tab of phpMyAdmin.
Let’s start by retrieving the first names from our users table.
To do this, we will write
|
1 2 3 |
SELECT f_name FROM users |
and click on “Go”.
If we want to select / retrieve the first and last names of the users in a single SELECT in SQL, we can do so by specifying the names of the two columns separated by a comma:
|
1 2 3 |
SELECT f_name,l_name FROM users |
Finally, to select everything in a table we can also use the joker * like this:
|
1 2 3 |
SELECT *FROM users |
Use SQL SELECT with PHP and PDO
Often, selecting data with SELECT from phpMyAdmin will not be of much interest.
What we will generally want to do will be to establish a connection to the database in PHP with the PDO extension and recover data from the database to serve it to users.
For that, we will have to create a PHP file which we can call select.php for example. This file will contain the PHP script to connect to the database, retrieve data from the users table and display it in table form.
Here is what it will look like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
<!DOCTYPE html> <html> <head> <title>Cours PHP / MySQL</title> <meta charset='utf-8'> </head> <body> <h1>MySQL databases</h1> <?php $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root"; try{ $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass); $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); / * Select the values in the first and mail columns of the table * users for each entry in the table * / $sth = $dbco->prepare("SELECT f_name, mail FROM users"); $sth->execute(); / * Returns an associative array for each entry in our table * with the names of the columns selected in keys * / $result = $sth->fetchAll(PDO::FETCH_ASSOC); / * print_r allows a readable display of the results, * <pre> makes everything a little more readable * / echo '<pre>'; print_r($result); echo '</pre>'; } catch(PDOException $e){ echo "Erreur : " . $e->getMessage(); } ?> </body> </html> |
Use SQL SELECT with selection criteria
We will be able to select data very precisely by applying selection criteria / filters / clauses in our SQL SELECT queries.
We will be able to use a SELECT with the following clauses and instructions in SQL:
WHERE;GROUP BY;HAVING;UNION | INTERSECT | EXCEPT;ORDER BY;LIMIT;OFFSET.
Attention here: if you use several selection criteria with a SQL SELECT query, be careful to use the different clauses in the order given above because otherwise your query will not work.
Let’s try to perform a more complex SQL SELECT by retrieving the first names and ages only of users under 30 and ordering the results by decreasing age.
Our SQL query will therefore be of the form SELECT f_name, mail FROM users WHERE age <= 30 ORDER BY age DESC:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
<!DOCTYPE html> <html> <head> <title>Cours PHP / MySQL</title> <meta charset='utf-8'> </head> <body> <h1>Bases de données MySQL</h1> <?php $servname = "localhost"; $dbname = "cours"; $user = "root"; $pass = "root"; try{ $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass); $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); /*Sélectionne les valeurs dans les colonnes prenom et mail de la table *users pour chaque entrée de la table*/ $sth = $dbco->prepare("SELECT prenom, age FROM users WHERE age <= 30 ORDER BY age DESC"); $sth->execute(); /*Retourne un tableau associatif pour chaque entrée de notre table *avec le nom des colonnes sélectionnées en clefs*/ $resultat = $sth->fetchAll(PDO::FETCH_ASSOC); /*print_r permet un affichage lisible des résultats, *<pre> rend le tout un peu plus lisible*/ echo '<pre>'; print_r($resultat); echo '</pre>'; } catch(PDOException $e){ echo "Erreur : " . $e->getMessage(); } ?> </body> </html> |
