Recently I showed you how to create a basic connection to a MySQL database with PHP and extract information via a query. At the time, I mentioned that once you have the ability to get data from a database, you will likely spend most of your time working on the query, trying to determine what information to retrieve. The query that we started with does the opposite to that:

SELECT * FROM products

Phrased that way, this query retrieves absolutely everything from the table named products: every piece of data, in every column. While your database table is small, containing only a few records, this isn’t a problem… but when it grows to hundreds, thousands, or even millions of rows, the work required to fetch and display all of that information can be significant.

We can immediately start to filter the data we retrieve by adding a WHERE clause:

SELECT * FROM products WHERE price < 50

This retrieves complete data for each record in the products table, but only for records in which the price column has a value of less than 50. (For the sake of this example I am assuming that price is a numerical field).

We can further limit the amount of data we receive by using that very word and a value:

SELECT * FROM products WHERE price < 50 LIMIT 10

This query retrieves the first 10 rows that match our criteria, and only 10; very useful when creating pagination (showing the user a limited number of results, so as not to overwhelm them (or our server)),

We can limit our results to just one record by using the value of the record’s primary key:

SELECT * FROM table WHERE id = 5 LIMIT 1;

(Including the LIMIT 1 is somewhat redundant here; we should only have one record with a value of 5 in its id field, but leaving it in doesn’t do any harm).

You can also cut down on the information you receive by specifying that you only want the data from certain columns, and not others. For example, this statement might be useful to retrieve data for the purposes of creating some navigation:

SELECT id, name FROM entries

In this example, while we are back to gaining information from every row, the only data we will receive is the is id and name value in each row; other information that might be in the table, such as description, will not be retrieved.

By default, data gained from a MySQL database is listed in the order it was entered, typically meaning by the ascending value of the primary key field for each record. We can order by any other field by specifying it:

SELECT * FROM products WHERE price < 50 ORDER BY price

The order of your retrieved data will automatically be sorted by ascending (ASC) value: that is, the rows will be ordered by whatever first appears in the field: alphabetically (a-z) or numerically, (0,  1, 2…). Often, you will want to order products by highest to lowest price instead, in which case your query becomes:

SELECT * FROM products WHERE price < 50 ORDER BY price DESC

You can also chain conditions together by using AND: note that references to text values should be quoted:

SELECT name,description FROM products WHERE price > 50 AND color=’black’ LIMIT 10 ORDER BY price DESC

As you can see, MySQL is a deceptively simple language: you can create very complex queries by chaining easy syntactical rules together. What you can’t do at the moment is to make the query itself dynamic; i.e. changing the data that is returned based on user input. Right now, all of the determinants in the query are hard-coded: in the statement above, you will get information back on all records in the products table that are listed as being black and having a price greater than 50, but the user has no ability to change the ‘50’ value to anything else. We’ll tackle that feature next in this series.

Enjoy this piece? I invite you to follow me at twitter.com/dudleystorey to learn more.