a joint venture

We've had a little excursion into table joining - extracting data from tables that have a relationship between them (sharing primary and foreign keys that is)

to get some practice, let's extend our earlier customer table, making two more tables so we can let them place orders for products. These tables will be:

Products
productID (int, 11, auto_increment)
productname (varchar, 250)
price (decimal, 25)

and

Orders
orderID (int, 11, auto_increment)
productID (int 11)
customerno (int 11)

the productID and customerno in the orders table are foreign keys to the products and customers tables, and so our customers can have orders for many products, and our products can be in many order records.

Once you've built the tables put some test data into them - let's say 4 customers, 4 products and 4 orders - make sure that only 2 of your customers have placed orders (the other 2 are still thinking about it), and that only 2 of your products appear in any order (the other 2 are just gathering dust)

Once you have your test data in place create 3 SQL queries to show the following data:

1. all orders (show customer name and product name only)
2. all customers (and what they've purchased, if anything)
3. all products (and who purchased them, if anyone)

the first query is a straightforward inner join, the last two will be left and right outer joins

Once you have the SQL working correctly, try building a set of php pages to display each one. For bonus marks, only show the customer name once on the all customers report (with a list of purchase items), and the product name once on the products report (with a list of customer names)

Finally, make an index page that points to each of the 3 reports - upload the lot (and your tables and test data) to the bathurst-tafe server and post a link back to the index in your blog.

---

Image: 'Join Us'
www.flickr.com/photos/16682940@N00/94736480

Cheating

Last week we looked at creating a table, inserting some information and showing the results.

You could have written the code from scratch - or done the smart thing and used phpMyAdmin's 'show php' feature.

This will take you a long way towards most of the things you want your web pages to do, but you might need some help getting phpMyAdmin to do its tricks in the first place - which is where the ilovejackdaniels cheat sheet comes in handy.

This page will list the common select statements and functions - and contains enough information for you to accomplish the following performance criteria:

Write an SQL statement that selectively retrieves data

1. Restrict the number of rows retrieved by placing criteria in the 'where' clause
   

2. Restrict the number of rows retrieved by placing specific criteria in the select statement
   

3. Use comparison operators in the 'where' clause to compare numeric, character, string, date and time data
   

4. Use Boolean operators with the correct precedence
   

5. Use criteria in the 'where' clause to check for a range of values, to select values from a list, and to check for values that match a pattern
   

6. Use SQL syntax to suppress duplicate values from query results
   

7. Take action to exclude null values from a query result
   

Write SQL statements that use functions

1. Use arithmetical operators with the correct precedence
   

2. Use string functions and operators to obtain the required query output
   

3. Use mathematical functions to obtain the required output, where required
   

4. Use date functions to obtain the required output
   

5. Use SQL aggregate functions to obtain the required output

Go on, try it out (you won't regret it)

---

Image: 'Blatent Cheating'
www.flickr.com/photos/81719291@N00/88894036