Retrieve Information from a MySQL Database - dummies

By Steve Suehring, Janet Valade

The only purpose in storing information in a MySQL database is to have it available when you need it. A database lives to answer questions. What products are for sale? Who are the customers? How many customers live in Indiana? What do the customers buy?

Many questions are answered by retrieving data from the database. For instance, to find out how many customers live in Indiana, you can retrieve all customer records where the field named state contains IN.

Very often, you ask these kinds of questions in a PHP script and display the answer in a web page. In a PHP script, you might retrieve all the records for Indiana customers and display a list of their names and addresses on a web page.

To answer specific questions, you use the SELECT query. You can ask precise, complex, and detailed questions with a SELECT query. The simplest SELECT query is

SELECT * FROM tablename

This query retrieves all the information from the table. The asterisk (*) is a wildcard meaning all the columns.

The SELECT query can be much more selective. SQL words and phrases in the SELECT query can pinpoint the information needed to answer your question. Here are some tricks you can make the SELECT query perform:

  • You can request only the information (the columns) that you need to answer your question. For instance, you can request only the first and last names to create a list of customers.

  • You can request information in a particular order. For instance, you can request that the information be sorted in alphabetical order.

  • You can request information from selected objects (the rows) in your table. For instance, you can request the first and last names for only those customers whose addresses are in Florida.

In MySQL 4.1, MySQL added the capability to nest a SELECT query inside another query. The nested query is called a subquery. You can use a subquery in SELECT, INSERT, UPDATE, or DELETE statements or in SET clauses. A subquery can return a single value, a single row or column, or a table, which is used in the outer query. All the features of SELECT queries can be used in subqueries.