> Home > e-Handouts and Assignments > SQL Queries

SQL Queries

SQL Selects

SQL is the acronym for "Structured Query Language". It is usually pronounced "sequel" by SQL folks, but you may also hear it referred to as "Ess Que El" (S-Q-L).

SQL is a standard programming language that is used to read and change data from a database. All of these database systems use SQL: Microsoft SQL Server, Microsoft Access, Oracle, MySQL, FoxPro, and many others.

A "select" statement in SQL is used to read information from the database. A SQL statement that does something with a database, by the way, is called a "query".

The basic format of a SQL "select" query is:

  Select [field name(s)]
  From [table name(s)]

The SELECT statement can retrieve all of the fields in a table by using the wildcard character, or asterisk (*). Here is an example:

  Select *
  From Orders

The above example would retrieve all of the fields from table "Orders". (And as we will soon see, the above example retrieves these fields from all of the rows in the table.)

It is very important that you note the following information about the SELECT SQL statement:

All fields (in each returned row -- see the "very important" notes below for info about how many rows are returned) will be retrieved from the table if you use the wildcard (*) operator after the keyword "SELECT".
You can specify which fields you want to retrieve by listing them after the keyword "SELECT".

Here is an example of listing which fields you want returned:

  Select OrderID, CustomerID, Order_Date
  From Orders

By the way, many people want to know if they can put comments in their SQL code to explain what the statements are doing. In MySQL and most other databases, the answer is "yes". But in MS Access the answer is "no".

The same query with some comments might look like this:

  -- This is my first SQL query:
  Select OrderID, CustomerID, Order_Date
  From Orders

Please note that the standard SQL "comment" marker is two hyphens (--) followed by a space. (The space is not necessary in all SQL brands, but to be safe, put it in anyway.)

Qualifying a Select Statement

You can "qualify" a SELECT statement with a "WHERE" clause to limit the number of records (rows) that the query returns. Here is a list of the relational operators that you can use:

Operator	Definition
=	Equal to
<>	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to

You can further qualify a SELECT statement by combining conditions with Logical Operators. These logical operators are AND and OR.

Here is an example of a query that uses two conditions to limit the records returned:

Select OrderID, CustomerID, Order_Date
From Orders
Where Order_Date > '2008-01-01'
AND Order_Date < '2009-03-15'

It is very important that you note the following information about the SELECT SQL statement:

All records will be retrieved from the table unless you use a WHERE clause to limit the returned records.
See the above "very important" notes about how many fields are returned by a query.

Please also note that the single-quote characters (') in the query above are needed because MySQL uses them to delimit date values. This is one major area where the different SQL "flavors" among the various database companies differ. MS Access, for instance, uses pound signs (#) to delimit dates.

Also, please note that MySQL expects dates to be in a particular format. This expected format is 'YYYY-MM-DD'.

For more information about the date format, you can refer to this page: http://dev.mysql.com/doc/refman/5.5/en/datetime.html

The LIKE Operator

The LIKE SQL operator is used as a SQL "wildcard" operator when you are looking for a particular text sequence in a field. Use LIKE when you're not sure of the exact text you're looking for. The actual wildcard character in a LIKE clause is

the percent sign ("%") in SQL Server and most other databases; or
The asterisk ("*") in MS Access.

Here is an example:

SELECT *
FROM Customers
WHERE Last_Name LIKE 'Smi%'

In the above example, all of the fields (because we're using the wildcard asterisk) would be retrieved for each record where the last name of the customer is something like 'Smith', 'Smithers', 'Smiley', 'Smirk', etc.

If you are looking for a word or a string of letters within a larger string, use two percent signs in the LIKE test. For example:

WHERE Last_Name LIKE '%th%'

which would find all of the records where 'th' is part of the last name, such as 'Smith', 'Rothchild', etc.

The ORDER BY Clause

If you don't tell the database (in your "select" query) how to order (sort) the returned records (rows of data), the database will decide what order to return the records in. The database's chosen order may or may not be acceptable to you. The order may even look like no order at all to you, although the database will have some reason for its chosen order.

You can control the order of the returned records with an ORDER BY clause in your select statement. The ORDER BY clause in a SQL SELECT statement is used to sort the records that are retrieved. For example:

SELECT *
FROM Customers
ORDER BY Last_Name

The above code would tell the database to return the records in the (alphabetical) order of the field "Last_Name".

You can put more than one field in the ORDER BY clause, like this:

SELECT *
FROM Customers
ORDER BY Last_Name, First_Name

The above code would tell the database to return the records in the order of the field "Last_Name", and then within any group of records with the same last name, in the order of the field "First_Name".

And you can reverse the order of the sorted records by adding the DESC operator (which means "descending"), like this:

SELECT *
FROM Customers
ORDER BY Last_Name DESC, First_Name

If you want to be explicit about the order that a particular field is returned in, and the order is "ascending", you can add the ASC operator to the ORDER BY clause. For instance, the previous query could also be coded like this:

SELECT *
FROM Customers
ORDER BY Last_Name DESC, First_Name ASC

The default order on a field is "ascending" (ASC).

Combining WHERE with ORDER BY

You can, and often will, combine WHERE with ORDER BY in a Select query. When you do, you must put the WHERE clause before the ORDER BY clause. Even though it makes perfect sense to you to put the clauses in either order, SQL demands that you put WHERE first, like this:

SELECT *
FROM Customers
WHERE Last_Name Like 'Smi%'
ORDER BY Last_Name, First_Name

SQL Inserts

There will come a time when you will need to insert some information into a database table with SQL code.

Here is a brief sample of a SQL INSERT for MySQL:

Insert Into Transaction (date, description, amount)
Values ('2009-03-08', 'The Last Mohican', 55.25)

Please note these points about the above query:

The fields that are going to have data inserted into them are listed within a set of parentheses following the table name (in this query, we are inserting data into a Transaction table. The words "Insert" and "Into" are keywords. (A keyword is a special, reserved word used by the SQL language.)
The values themselves are also listed within a set of parentheses, following the "Values" keyword.
The two lists must be in the same order, meaning that the order of the field names must match the order of the values going into those fields, in the two lists.
There are single quotes around the date in the MySQL version of the code. Also, the date in MySQL is in YYYY-MM-DD format.
There are also single quotes around the description. Text values in most SQL systems require single quotes as delimiters.

SQL Updates

There will also come a time when you will need to update some information in a database table with SQL code.

Here is a brief sample of a SQL UPDATE for MySQL:

Update Transaction
Set date = '2007-12-31',
amount = 25.00,
description = 'Spider Man'
Where ID = 4

Please note these points about the above query:

"Update" is another SQL keyword.
The table we are updating is Transaction.
"Set" is another SQL keyword.
The field names and values (with an equal sign operator between them) are listed in a comma-delimited list following the "Set" keyword.
The "Where" keyword tells SQL which record is to receive these data values. This particular example assumes that we have a record in the Transaction table with a value of 4 in the "ID" field.
Very Important: If the "Where" clause were not in this query, every record in the Transaction table would receive these values. Sometimes you may want this kind of action to occur, but be very certain this is what you want!

SQL Deletes

It should come as no surprise that you will some day need to delete some information from a database table with SQL code.

Here is a sample of a SQL DELETE:

Delete from Transaction
Where ID = 4

Please note these points about the above query:

"Delete", "from", and "Where" are SQL keywords.
The Table being affected is Transaction.
The record in this table that has a value of 4 in the ID field will be deleted.
Once this record is deleted, it is gone. If you need to recover it, you will need to (very apologetically, I would think) ask your database administrator to do a restore from a recent backup. This is not generally a career-enhancing move.

Here is Jim's Rule of Survival for Databases: Before you do a SQL Delete, do a SQL Select. This rule helps you make sure you are deleting the record(s) that you want to delete. You can put this rule into practice in a Web page by allowing the user to see the data they are about to delete, and giving the user the choice of "Yes, that's the data I meant" or "Oops. Don't delete this data!"

A sample of what this rule applied to a Web page means is my fake checkbook register program on the jimlink.net server at PHPStuff/checkbook.php. (Note: The server that this page is on might give you a "404: Page not found" error, but the page is really there. Just change the URL so the lower-case "s" in "PHPstuff" becomes an upper-case "S" so it looks like "PHPStuff" and then reload/refresh the browser.) (Also note: You will need a username and password to log in. Use the fake username of "harry" (without the quotes) and the fake password of "potter" (also without the quotes).

Note that when you click one of the "Delete" buttons, you are given the choice of "Delete the Record" or "Oops. Keep this Record". The data that is displayed in the form fields is retrieved from the table by a SQL Select statement.

A Brief Review of Objects

In the rest of the sections of this e-handout, we will be looking at PHP code which uses one of the best database objects available, which is mysqli.

An object in PHP (and in most other programming systems, too) is a code container which helps PHP itself to organize the built-in functions that you use for your programming.

An object contains two kinds of things that you can use:

Functions
Variables

But in the context of an object, these two kinds of things are normally called by different names, although they are still functions and variables. These normally-used names are, respectively:

Methods
Properties

Here is a quick list of the main things you need to know about objects in order to use the mysqli object:

The mysqli object template (technically called a "class") that we will be using for our database programming activities is built in to PHP.
But you need to create one of these objects before you can use it.
You create a built-in object with the new operator.
Please note that new in this context is a reserved word in PHP. You can use it only in the context of creating an object.
You create the object (we'll call ours $dbObject here) like this:
```
$dbObject = new mysqli();
```
But when you actually create the object in your code, you need to provide four arguments (which we will see shortly, below) in this mysqli() function:
1. Database Server
2. User
3. Password
4. Database Name
After you create the object, you use its functions like this:
```
$dbObject->methodName();
```
That funny-looking operator -> tells PHP that the object contains, or owns, the function.

SQL DISTINCT

The DISTINCT keyword in a SQL SELECT statement tells the database to get only one each of the values that are in the field that is modified by DISTINCT. For example:

<?php
  require "config.php";
  $mysqli = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
  /* check connection */
  $db_error = $mysqli->connect_errno;
  if ($db_error)
  {
    printf("<p>Connect failed: %s</p>\n", $db_error);
    exit();
  }
  $query = "SELECT DISTINCT TransDate FROM Transaction ORDER BY TransDate DESC";
  $result = $mysqli->query($query);
  $rowInfo = $result->fetch_assoc();
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Distinct Dates</title>
  <link rel="stylesheet" type="text/css" href="myStyles.css" />
</head>
<body>
  <div id="mainDiv">
    <h1>Display Distinct Dates in the Home Checkbook</h1>
    <table id="dateTable">
    <?php
      while ($rowInfo !== null)
      {
        $dateStr = date("m/d/Y", strtotime($rowInfo["TransDate"]));
        print ("<tr><td>$dateStr</td></tr>");
        $rowInfo = $result->fetch_assoc();
      }  //end while()
    ?>
    </table>
  </div>
</body>
</html>

would retrieve just one each of the transaction dates in the Transaction table, even if there are multiple occurrences of some dates in the table.

You can see the results of the above query here.

Please note that the DISTINCT keyword is needed only if you expect that there are multiple occurrences of some of the information in a table, and you need to find the unique values for some reason. You don't often need this kind of limitation on your information.

Images and Databases

A lot of people ask about how to store images in a database, so let me start off this section with a hint about how to do that. In a word, don't. Images take up too much space in a database. Store the images in an appropriately-named folder on the Web server (for instance: "images"). In the database itself, store only the filename of the image that you want associated with each record.

When you are ready to display the image associated with a record in the database, simply query the image's filename out of the database and construct the src attribute of the <img> tag using the filename that you get from the database.

PHP Queries with mysqli()

On the class server, and/or if you do NOT have an ODBC connection set up to your database, and the database is MySQL, you can use PHP's mysqli() object and functions to connect to, and send queries to, the MySQL database.

These are the functions that you will use for this class.

PHP requires you to use these steps to query a MySQL database with the mysqli() object and functions:

Connect to the database by creating an object of type mysqli, using your log-in credentials.
Check the connection. (Not necessary, but highly recommended.)
Execute a database query.
Get the results of the query, usually row by row.
Do something with each row's data, for instance, display it in the page, or create <option> tags, etc.

Here is a query that the main page of a fake checkbook register program could use with the "mysqli" object and functions:

<?php
  require "loginAndLogout.php";
  require "config.php";
  $dbobject = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
  /* check connection */
  $db_error = $dbobject->connect_errno;
  if ($db_error)
  {
      printf("<p>Connect failed: %s</p>\n", $db_error);
      exit();
  }
  $querystr = "SELECT * FROM Transaction ORDER BY TransDate DESC, Description";
  $recordset = $dbobject->query($querystr);

  $row = $recordset->fetch_assoc();
  while ($row !== null)
  {
    ...  (lots of other code goes here) ...
    $row = $recordset->fetch_assoc();
  }
?>

You can see the fake checkbook register program at my site, in page PHPStuff/checkbook.php, here. (You will need a username and password to log in. Use the fake username of "harry" (without the quotes) and the fake password of "potter" (also without the quotes).

Please note these points about the above code:

This PHP code is in the context of an HTML table. This code constructs some rows in the HTML table.
The constants DBSERVER, DBUSER, DBPWD, and DBNAME are set in file config.php so we can look at this code without my revealing my password to you.
The query is created as a string, and then used in the $dbobject->query($querystr) function. The argument to $dbobject->query() is the query string.
When the query is run, $dbobject->query() returns a record set object which holds the rows of data that were returned. In our code, this object is saved in variable $resultset.
The function $resultset->fetch_assoc(); gets one row (record) of data from the query results, and puts that row into an associative array, which is stored in variable $row.
When $resultset->fetch_assoc(); has retrieved all of the rows of data from the record set, it returns null, so that is the value that is tested in the while() statement to end the loop.

The config.php File

In order to hide your username and password from prying eyes in case someone else needs to look at your PHP pages, you should use an include file to store this sensitive information. In the sample code that we are looking at, my include file is config.php.

Here is a "sanitized" (meaning: the sensitive information has been replaced or removed) version of the config.php page, which you can copy for your own use:

<?php
  define('DBSERVER','localhost');
  define('DBUSER','YourUserName');  // e.g.:  'jamlin73_classdb'
  define('DBPWD','YourPassword');   // whatever your login password is, in quotes
  define('DBNAME','YourDatabaseName');   // e.g.: 'jamlin73_classdb'
?>

Make sure that you leave the 'DBSERVER' definition as it is. The database server is actually 'localhost'.

And make sure you change the other three definitions to have your own login information and database name. The definitions do have quotes around them.

By the way, in case you're wondering what these define(...) statements do, since we haven't talked about them before: A define(...) statement creates a PHP constant value. It is a slightly more efficient way of putting values into your PHP pages which you don't want to have changed later in the code, rather than using variables to store the values. You could use variables, but constants are better in a situation like this one.

Formatting Codes in the printf( ) Family of PHP Functions

The formatting codes such as %d in the code above, and in many of the database examples that we will see in coming weeks, are special place-holding characters which tell printf( ) and sprintf( ) what kind of information to put into the formatted string at the place where the formatting code is.

The three most commonly-used formatting codes are these:

%d -- for integer (whole) numbers (sometimes called "decimal" numbers)
%f -- for floating-point numbers, meaning numbers with a decimal point in them
%s -- for strings

If you want to see the complete list of formatting codes, there is a list on the reference page for sprintf( ) at php.net.

Processing Sequence

Here is the same diagram that you saw earlier in the course, which shows the general sequence of events that occurs when your PHP page loads into a browser. I figure this is a good time to review it again.

Populating a Select List with a Single Page

One really interesting thing you can do with a query is to populate an HTML select list. Here is an example. This first example uses the same page for both the HTML form and the PHP action processing. This is file selectMemo2.php:

<?php
  require "config.php";
  $dbobject = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
  /* check connection */
  $db_error = $dbobject->connect_errno;
  if ($db_error)
  {
      printf("<p>Connect failed: %s</p>\n", $db_error);
      exit();
  }
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <link rel="stylesheet" type="text/css" href="myStyles.css">
  <title>Select a Checkbook Transaction</title>
</head>
<body>
  <div id="mainDiv">
  <?php
    // If the form has been submitted, get the record that was selected:
    if (isset($_POST["memo_picked"]))
    {
      $transID = $_POST["memo_picked"];
      print("<h3>Transaction Details</h3>\n");
      print("Transaction ID: " . $transID);
      $querystr = sprintf("SELECT * FROM Transaction WHERE ID = %d", $transID);
      $resultset = $dbobject->query($querystr);
      $rowInfo = $resultset->fetch_assoc();
      $descr = $rowInfo["Description"];
      $amount = $rowInfo["Amount"];
      $transDate = $rowInfo["TransDate"];
      $displayDate = date("m/d/Y", strtotime($transDate));
      print ("<br>Date: " . $displayDate);
      print ("<br>Description: " . $descr);
      printf ("<br>Amount: \$%1.2f", $amount);
    }
    // If the form has not been submitted, display the form:
    else
    {
  ?>
    <form action="selectMemo2.php" method="post">
      Pick a Checkbook Transaction:
      <select name="memo_picked">
        <option value="0">-- Select a Memo Description --</option>
  <?php
        $querystr = "SELECT * FROM Transaction ORDER BY Description";
        $resultset = $dbobject->query($querystr);
        $rowInfo = $resultset->fetch_assoc();
        while ($rowInfo != null)
         {
            $ID = $rowInfo["ID"];
            $descr = $rowInfo["Description"];
            $transDate = $rowInfo["TransDate"];
            $displayDate = date("m/d/Y", strtotime($transDate));
            print ("      <option value='" . $ID . "'>" . $descr . "  " . $displayDate . "</option>\n");
            $rowInfo = $resultset->fetch_assoc();
         }
  ?>
      </select>
      <br />
      <br />
      <input type="submit" value=" Display Transaction Details " />
    </form>
  <?php
    }
  ?>
  </div>
</body>
</html>

You can see the above code running on the jimlink.net server here.

Please note these points about the above code:

This PHP code is in the context of an HTML form. This code constructs some <option> tags in the <select> list.
The queries are in the mysqli->query( ) functions, using the object that was created earlier in the page, $dbobject. The first argument to $dbobject->query( ) is the query string.
The SELECT (SQL) statement in the second query retrieves the checkbook (memo) descriptions and the record IDs from the Transaction table.
The function $resultset->fetch_assoc() gets one row (record) of data from the query results, and puts that row into an associative array, which is stored in variable $rowInfo. This function returns null when there are no more records to retrieve.
The code
```
$rowInfo = $resultset->fetch_assoc();
while ($rowInfo != null)
```
is used to loop through the records in the result set from the query. In this code, the loop is adding <option> tags to the <select> tag.
There are four PHP code sections in the page. Even the opening and closing curly braces for some of the PHP code blocks in the page are in separate PHP code sections.

Populating a Select List with Two Pages

This second example uses two separate PHP pages: One is for the HTML form (which also uses PHP code, to build the <select> list), and the second page is for the PHP action page. The first page is file selectMemo3.php:

<?php
  require "config.php";
  $dbobject = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
  /* check connection */
  $db_error = $dbobject->connect_errno;
  if ($db_error)
  {
      printf("<p>Connect failed: %s</p>\n", $db_error);
      exit();
  }
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <link rel="stylesheet" type="text/css" href="myStyles.css">
  <title>Select a Checkbook Transaction</title>
</head>
<body>
  <div id="mainDiv">
  <form action="selectMemo3Action.php" method="post">
    Pick a Checkbook Transaction:
    <select name="memo_picked">
      <option value="0">-- Select a Memo Description --</option>
  <?php
        $querystr = "SELECT * FROM Transaction ORDER BY Description";
        $resultset = $dbobject->query($querystr);
        $rowInfo = $resultset->fetch_assoc();
        while ($rowInfo != null)
         {
            $ID = $rowInfo["ID"];
            $descr = $rowInfo["Description"];
            $transDate = $rowInfo["TransDate"];
            $displayDate = date("m/d/Y", strtotime($transDate));
            print ("      <option value='" . $ID . "'>" . $descr . "  " . $displayDate . "</option>\n");
            $rowInfo = $resultset->fetch_assoc();
         }
  ?>
    </select>
    <br />
    <br />
    <input type="submit" value=" Display Transaction Details " />
  </form>
  </div>
</body>
</html>

The action page is selectMemo3Action.php:

<?php
  require "config.php";
  $dbobject = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
  /* check connection */
  $db_error = $dbobject->connect_errno;
  if ($db_error)
  {
      printf("<p>Connect failed: %s</p>\n", $db_error);
      exit();
  }
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <link rel="stylesheet" type="text/css" href="myStyles.css">
  <title>Select a Checkbook Transaction</title>
</head>
<body>
  <div id="mainDiv">
  <?php
    // If the form has been submitted, get the record that was selected:
    if (isset($_POST["memo_picked"]) && $_POST["memo_picked"] > 0)
    {
      $transID = $_POST["memo_picked"];
      print("<h3>Transaction Details</h3>\n");
      print("Transaction ID: " . $transID);
      $querystr = sprintf("SELECT * FROM Transaction WHERE ID = %d", $transID);
      $resultset = $dbobject->query($querystr);
      $rowInfo = $resultset->fetch_assoc();
      $descr = $rowInfo["Description"];
      $amount = $rowInfo["Amount"];
      $transDate = $rowInfo["TransDate"];
      $displayDate = date("m/d/Y", strtotime($transDate));
      print ("<br>Date: " . $displayDate);
      print ("<br />Description: " . $descr);
      printf ("<br />Amount: \$%1.2f", $amount);
    }
    else
    {
    	echo "<h3>You didn't select anything.  Please go back and do so.</h3>\n";
    }
  ?>
  </div>
</body>
</html>

You can see the above code running on the jimlink.net server here.

Please note these points about the above code:

The PHP code in the first page is also in the context of an HTML form. This code constructs some <option> tags in the <select> list.
The queries are in the $dbobject->query($query); functions. The first argument to $dbobject->query( ) is the query string.
The SELECT (SQL) statement in the second query retrieves the checkbook (memo) descriptions and the record IDs from the Transaction table.
The function $resultset->fetch_assoc() gets one row (record) of data from the query results, and puts that row into an associative array, which is stored in variable $rowInfo. This function returns null when there are no more records to retrieve.
The code
```
$rowInfo = $resultset->fetch_assoc();
while ($rowInfo != null)
```
is used to loop through the records in the result set from the query. In this code, the loop is adding <option> tags to the <select> tag.
The second page, which is the action page, also uses some PHP code. In the action page, the PHP code displays the details from the transaction which was selected in the form page.

Richland College Multimedia Learning Center

Digital Media Programming with PHP and MySQL

SQL Queries

SQL Selects

Qualifying a Select Statement

The LIKE Operator

The ORDER BY Clause

Combining WHERE with ORDER BY

SQL Inserts

SQL Updates

SQL Deletes

A Brief Review of Objects

SQL DISTINCT

Images and Databases

PHP Queries with mysqli()

The config.php File

Formatting Codes in the printf( ) Family of PHP Functions

Processing Sequence

Populating a Select List with a Single Page

Populating a Select List with Two Pages