Hudzilla.org - the homepage of Paul Hudson

Contents > Databases Wish List | Report Bug | About Me ]

9.19 Subselects, views, and other advanced functions

This is NOT the latest copy of this book; click here for the latest version.

Although MySQL is undoubtedly the most popular database system for PHP, I really hate it when I read a book on PHP and find that it covers MySQL and nothing else. Yes, I have already discussed PEAR::DB and SQLite, but there are hundreds of thousands of databases out there that use functionality not available in the stable MySQL build, and that is what we will be covering here: subselects, views, and referential integrity.

Before you try using any of these features in your database, please check that it is supported. Subselects, for example, are only possible in MySQL 4.1, and referential integrity is only possible using InnoDB tables in MySQL. PostgreSQL, Microsoft SQL Server, Oracle, etc, all support these features fully. For the purpose of these examples, Microsoft SQL Server 2000 has been used.

To demonstrate the advanced functionality, the following data sets were used:

CREATE TABLE Customers (ID INT PRIMARY KEY, Name CHAR(255));

  INSERT INTO Customers VALUES (1, 'Paul');

  INSERT INTO Customers VALUES (2, 'Ildiko');

  INSERT INTO Customers VALUES (3, 'Andrew');

  INSERT INTO Customers VALUES (4, 'Bernice');

  

  CREATE TABLE Orders (ID INT PRIMARY KEY, Customer INT, Purchase CHAR(255));

  INSERT INTO Orders VALUES (1, 1, 'Socks');

  INSERT INTO Orders VALUES (2, 1, 'Sweater');

  INSERT INTO Orders VALUES (3, 2, 'DVD');

  INSERT INTO Orders VALUES (4, 1, 'TV');

  INSERT INTO Orders VALUES (5, 4, 'Book');

  INSERT INTO Orders VALUES (6, 3, 'Socks');

  INSERT INTO Orders VALUES (7, 4, 'T-shirt');

That gives us two tables, Customers and Orders, with Orders referencing the Customers table.

<< 9.18 MySQL Improved		9.19.1 Subselects >>
Table of Contents

Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.

My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!

Be the first to add a comment to this chapter!

Add comment
Please note that by posting a comment here you are committing it to the public domain. This is important so that others can make use of your code themselves, and also so that I can incorporate helpful notes directly into the main text. Comments are limited to 2000 characters in length.

If you are reporting an error in the content, please tell me directly.