Subselects, views, and other advanced functions

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 versions of MySQL after 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 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.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Subselects >>

Previous chapter: MySQL Improved

Jump to:

Home: Table of Contents