> Home > e-Handouts and Assignments > Advanced SQL

Advanced SQL

Column Aliases

If you use one of the SQL "Aggregate Functions" (see the next section), you will need to name the resulting value so you can refer to it in your PHP code (in the associative array that is returned from mysql_fetch_assoc()).

To use the column alias feature of SQL, you put the SQL term AS after the call to the aggregate function, and then follow AS with a column name that you create. Here is a sample of SQL code with a column alias in it (we will discuss the Count() function in the next section):

SELECT Count(ProductId) AS Num_Products 
FROM products

SQL sees the alias as the effective column (field) name. The column (field) name in the query above is Num_Products.

Note: You can use aliases to rename columns in any situation, even if you just don't like the real column names.

Aggregate Functions

SQL has two types of functions:

scalar - returns a single number
aggregate - returns information about data, that is, details about the records selected by the query

The following query assumes that the table Products has at least two columns in it, called Name and Wholesale_Price.

Now look at the page below. This is file aggregate.php:

<?php
error_reporting (E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
include 'config.php';
$db = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
  <title>Aggregate SQL Functions</title>
  <link rel="stylesheet" type="text/css" media="all" href="PHPstyles.css" />
</head>
<body>
  <h3>Some Aggregate Numbers from the Products Table</h3>
    <?php
      $sqlString =
	   "Select Count(products.Name) AS Product_Count,
         Max(products.Wholesale_Price) AS Max_Price,
         Min(products.Wholesale_Price) AS Min_Price,
         Avg(products.Wholesale_Price) AS Avg_Price,
         'PHP is fun!' AS Motivational_Statement
       From products";
      $result = $db->query ($sqlString);
      $rowInfo = $result->fetch_assoc();
       
      while ($rowInfo)
      {
        print "There are " .  $rowInfo["Product_Count"] . " records in the Products table.<br />\n";
        printf("The maximum wholesale price is $%0.2f<br />\n",  $rowInfo["Max_Price"]);
        printf("The minimum wholesale price is $%0.2f<br />\n",  $rowInfo["Min_Price"]);
        printf("The average wholesale price is $%0.2f<br />\n",  $rowInfo["Avg_Price"]);
        print $rowInfo["Motivational_Statement"] . "<br />\n";
        $rowInfo = $result->fetch_assoc();
      }
     ?>
</body>
</html>

You can see this page running on the server.

Please note these details about the above code:

The query uses aliases to create field names that can be used in the PHP code.
The column names being selected are being processed by aggregate functions such as COUNT(), MIN(), MAX(), and AVG().
The last selected value is enclosed in single quotes, and then is given a column alias. This selected value is a simple example of how you can even create field names on the fly, in SQL.

Here is some detail on what these aggregate functions do:

COUNT() returns the number of records in the query.
MIN() and MAX() return the minimum and maximum values in the queried columns.
AVG() returns the average value of all the numbers in the column in the query.

Note: If you use a WHERE clause to limit the records returned in your query, the aggregate functions report on only those records that are selected by the WHERE clause.

Table Joins

A table join uses a single SQL statement to get information (records) from two different tables. SQL returns the results as a single batch of records.

Look at the following PHP page, which is Joins.php:

<?php
error_reporting (E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
include 'config.php';
$db = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
  <title>SQL Joins</title>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
  <link rel="stylesheet" type="text/css" media="all" href="PHPstyles.css" />
</head>

<body>
  <h3>The First SQL Join</h3>
    <?php
      $sqlString = 
        "Select products.Name AS Product_Name,
         suppliers.Name AS Supplier_Name,
         products.Color, products.Quantity_In_Stock, products.Wholesale_Price,
         products.Retail_Price
         From products, suppliers
         Where products.SupplierID = suppliers.SupplierID";
      $result = $db->query ($sqlString);
      $rowInfo = $result->fetch_assoc();

      while ($rowInfo)
       {
         $numfields = $result->field_count;
         for($i=0; $i < $numfields; $i++)
         {
           $fieldinfo = $result->fetch_field_direct($i);
           $fieldname = $fieldinfo->name; 
           $value = $rowInfo[$fieldname];
           print "$fieldname: $value <br />\n";
         }
         print "<br /><br />\n";
         $rowInfo = $result->fetch_assoc();
       }
     ?>
   <h3>Another SQL Join</h3>
    <?php
      $sqlString = 
        "Select products.Name AS Product_Name, 
         suppliers.Name AS Supplier_Name,
         products.Color, products.Quantity_In_Stock, products.Wholesale_Price,
         products.Retail_Price
         From products
         INNER JOIN suppliers
         ON products.SupplierID = suppliers.SupplierID";
      $result = $db->query ($sqlString);
      $rowInfo = $result->fetch_assoc();
       
      while ($rowInfo)
       {
         $numfields = $result->field_count;
         for($i=0; $i < $numfields; $i++)
         {
           $fieldinfo = $result->fetch_field_direct($i);
           $fieldname = $fieldinfo->name; 
           $value = $rowInfo[$fieldname];
           print "$fieldname: $value <br />\n";
         }
         print "<br /><br />\n";
         $rowInfo = $result->fetch_assoc();
       }
     ?>
</body>
</html>

You can see this page running on the server.

Please note these details about the above code:

Every field name in the query is preceded by the table it is in, followed by a period, and then the field name.
In the first query, the From line lists two tables, and the Where line specifies the common value in the two tables that will join the records.
In the second query, the keywords INNER JOIN and ON are used to accomplish the join.
These two methods of making a join are equivalent, as is proved by the two lists of the query recordsets.

Note: The fields compared in a where clause and the ON clause must be of the same data type.

Subqueries

A SQL subquery is a SELECT within a SELECT.

Look at the page below, which is Subqueries.php:

<?php
error_reporting (E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
include 'config.php';
$db = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
  <title>Subqueries</title>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
  <link rel="stylesheet" type="text/css" media="all" href="PHPstyles.css" />
</head>

<body>
  <h3>Products from Plano:</h3>
    <?php
      $sqlString = 
        "Select products.Name AS Product_Name, 
         suppliers.Name AS Supplier_Name,
         products.Color, products.Quantity_In_Stock,
         products.Wholesale_Price,
         products.Retail_Price, suppliers.City
         From products, suppliers
         Where products.SupplierID = suppliers.SupplierID
         AND suppliers.SupplierID IN
          (Select SupplierID
          From suppliers
          Where suppliers.City = 'Plano')";
      $result = $db->query ($sqlString);
      $rowInfo = $result->fetch_assoc();
       
      while ($rowInfo)
       {
         $numfields = $result->field_count;
         for($i=0; $i < $numfields; $i++)
         {
           $fieldinfo = $result->fetch_field_direct($i);
           $fieldname = $fieldinfo->name; 
           $value = $rowInfo[$fieldname];
           print "$fieldname: $value <br />\n";
         }
         print "<br /><br />\n";
         $rowInfo = $result->fetch_assoc();
       }
     ?>
</body>
</html>

You can see this page running on the server.

Please note these details about the above code:

The subquery is enclosed in parentheses.
The subquery is preceded by the SQL IN operator. The IN operator looks for specified value in a series or a list of values.

Note: The subquery is run before the main SELECT runs.

Note the difference between joins and subqueries:

A join maintains the data relationships in the joined tables. The matching records from each table in the join are put into the resulting query recordset.
A subquery limits the records that are put into the query recordset, based on a different table than is in the main SELECT statement.

SQL Views

A view is a saved SQL SELECT statement that "lives" in the database just like a table does. A view may be named, queried, and even updated.

You can create a view in three ways:

In a database program like Microsoft Access, you can simply create a named query. Access sees this query as a view, if you name it.
In other database systems such as MySQL, Microsoft SQL Server, and Oracle, you can use the administrative system (for MySQL, that would be the phpMyAdmin Web-based admin pages) to create an object called a View.
You can execute a "Create View" SQL statement either from within the Admin pages or from a PHP page with a query in it.

As an example of the third method, look at the following page. This is file CreateView.php:

<?php
error_reporting (E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
include 'config.php';
$db = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Create a SQL View</title>
</head>
<body>
  <?php
    error_reporting (0);
    try
    {
      $sqlString = 
        "CREATE OR REPLACE VIEW View_Products AS
         Select products.Name AS Product_Name, suppliers.Name AS Supplier_Name,
         products.Color, products.Quantity_In_Stock, products.Wholesale_Price,
         products.Retail_Price
         From products INNER JOIN suppliers
         ON products.SupplierID = suppliers.SupplierID;";
      $result = $db->query ($sqlString);
      if ($result === false)
      {
          throw new Exception("Database error!");
      }
      echo "<h3>Database operation was successful.</h3>\n";
    }
    catch (Exception $e)
    {
      echo '<h3>Caught exception: ',  $e->getMessage(), "</h3>\n";
      echo '<h3>' . $db->error . "</h3>\n";
    }
   ?>
</body>
</html>

You can see this page running on the jimlink.net server here. (NOTE: You will probably see a database Error Message when you try to run this page. I have shut down most of the create/update privileges for this database login for security reasons. The view has been created previously, so this page does not need to run again.)

Please note these details about the above code:

The SQL keywords CREATE, VIEW, and AS are used.
The name of the view has a prefix "View". This is standard practice among SQL programmers, but is not a technical requirement.
This query will work even in Microsoft Access. The view will be saved in the database as a named query.
If you ever need to change the view, you will need to change the SQL statement to read ALTER VIEW instead of CREATE VIEW. Then you will need to execute the ALTER VIEW statement.
You can see, browse, and alter the view from within the phpMyAdmin admin pages, also.

Also note that a view's query/recordset is generated dynamically, which means that any changes made in the tables queried in the view are reflected in the view each time the view is used in a query.

After a view is created, it can be used in a query just like a table. See the sample page below, which is file QueryView.php:

<?php
error_reporting (E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
include 'config.php';
$db = new mysqli(DBSERVER, DBUSER, DBPWD, DBNAME);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Query a SQL View</title>
</head>
<body>
  <h3>Product Information using View "View_Products"</h3>
  <?php
    error_reporting (0);
    try
    {
      $sqlString = "Select * From View_Products;";
      $result = $db->query ($sqlString);
      if ($result === false)
      {
        throw new Exception("Database error!");
      }
      $rowInfo = $result->fetch_assoc();
      while ($rowInfo)
      {
        foreach ($rowInfo as $fieldname => $value)
        {
          print "$fieldname: $value <br />\n";
        }
        print "<br /><br />\n";
        $rowInfo = $result->fetch_assoc();
      }
    }
    catch (Exception $e)
    {
      echo '<h3>Caught exception: ',  $e->getMessage(), "</h3>\n";
      echo '<h3>' . $db->error . "</h3>\n";
    }
  ?>
</body>
</html>

You can see this page running on the jimlink.net server here.

Please note these details about the above code:

The query's name is used where a table name is normally used in a select statement.
The query's saved code is run by the database engine each time a query is run on the query. This feature means that any table updates are automatically reflected in the resulting recordset.

Stored Procedures

A stored procedure is a series of SQL statements that are saved within a database and which can be called by a PHP query. Only server-based databases currently support stored procedures. SQL Server, Oracle, MySQL, and DB2 all have stored procedures.

Stored procedures are somewhat like views, but they can also include variables, decision branching, and temporary tables.

Stored procedures can centralize and enhance the queries done in a database system, but the same tasks can usually be accomplished with standard SQL statements and the various PHP control statements such as if, while, for, etc.

Richland College Multimedia Learning Center

Digital Media Programming with PHP and MySQL

Advanced SQL

Column Aliases

Aggregate Functions

Table Joins

Subqueries

SQL Views

Stored Procedures