SELECT Syntax

• The SELECT statement specifies which rows to retrieve from a table.
• It doesn't actually retrieve them; that comes later.
• Different databases offer different advanced features.
• All use the same basic syntax:

  SELECT column_list FROM table [WHERE search_conditions]

• Column_list is the columns you want returned, separated by commas.
• To get all the columns:

  SELECT * FROM table [WHERE search_conditions]

• But as in the INSERT statement, the order in which columns are returned is unpredictable.
• The WHERE clause is optional -- it applies selection criteria.
• Retrieve all the titles that sell for under $10:

  SELECT title FROM title_table WHERE price < 10

• Retrieve the titles containing the word "the":

  SELECT title FROM title_table WHERE title LIKE "the"

• Fancy WHERE clause features differ among databases, potentially making your application non-portable.

Getting the Selected Rows

• Once the SELECT statement has been executed, we must retrieve the actual rows from the table.
• Several ways to do this.
• Get a reference to an array containing the next row of data:

  $aref = $sth->fetch;

• Get an array containing the next row of data:

  @array = $sth->fetchrow_array;

• Both methods return undef if there is no more data.
• Both methods yield arrays whose elements are in the order given in the column_list in the SELECT statement.

Cleaning Up

• SELECT uses some resource such as a buffer in the database.
• After reading the rows from the table, call finish, e.g.:

  $sth->finish;

• Frees the database resources consumed by the SELECT.
• Do not call it before you have read all your data.

Performance is discussed in the Extra for Experts section in this chapter's lab.

Review

What are the two steps for getting data back from your database?