SQL SELECT – Selection from a database table – Programming Code Examples
MySQL PHP

SQL SELECT – Selection from a database table5 min read

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

 

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:

 

 

 

Finally, to select everything in a table we can also use the joker * like this:

 

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:

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:

 

 

Leave a Comment