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