Episode 18: Querying the Database

Now that you have some data in the database, you probably want to retrieve it. You use the SELECT statement to choose data that fits your criteria.

Typical syntax for this command is as follows:

SELECT [fieldnames]

AS [alias]

FROM [tablename]

WHERE [criteria]

ORDER BY [fieldname to sort on] [DESC]

LIMIT [offset, maxrows]

You can set numerous other parameters, but these are the most commonly used:

❑ SELECT [fieldnames]: First decide what specific fieldnames you want to retrieve; if you want to see them all, you simply insert *.

❑ AS: You use the alias to group two or more fieldnames together so that you can reference them later as one giant variable. An example would be:

SELECT first_name, last_name AS full_name. . . ORDER BY full_name . . .

You cannot use the AS parameter with the WHERE parameter, because this is a limitation of MySQL. When the WHERE clause is executed, the column value may not be known.

❑ FROM: This is pretty self-explanatory: You just need to name the table or tables you are pulling the data from.

❑ WHERE: List your criteria for filtering out the data, as described in the following section.

❑ ORDER BY: Use this parameter if you want the data sorted on a particular field; if you want the results returned in descending order, add DESC.

❑ LIMIT: This enables you to limit the number of results returned and offset the first record returned to whatever number you choose. An example would be:

LIMIT 9, 10

This would show records 10 through 19. This is a useful feature for showing only a certain number of records on a page, and then allowing the user to click a “next page” link to see more.

For a complete reference, you are advised to—yet again—visit the source at www.mysql.com.

Written by 'Shojib'.