Mysql joins,Attacks,PHP MVC architecture..... !!!!

MySQL LEFT, RIGHT JOIN tutorial

MySQL joins are hard for beginners. At least for me when I was beginner.
I will try to explain the joins in the simplest possible way.

Join in MySQL is a query where you can join one or more tables.

For example we have two tables: products and buyers with the following structures.
Table products:

mysql> SELECT * FROM products;

+----+--------------+--------------+

| id | product_name | manufacturer |

+----+--------------+--------------+

| 1 | Shoes | Company1 |

| 2 | Laptop | Company2 |

| 3 | Monitor | Company3 |

| 4 | DVD | Company4 |

+----+--------------+--------------+

4 rows in set (0.00 sec)

Table buyers:
mysql> SELECT * FROM buyers;

+----+------+------------+----------+

| id | pid | buyer_name | quantity |

+----+------+------------+----------+

| 1 | 1 | Steve | 2 |

| 2 | 2 | John | 1 |

| 3 | 3 | Larry | 1 |

| 4 | 3 | Michael | 5 |

+----+------+------------+----------+

5 rows in set (0.00 sec)

Left Join

mysql> SELECT buyer_name, quantity, product_name FROM buyers LEFT JOIN products ON

buyers.pid=products.id;

+------------+----------+--------------+

| buyer_name | quantity | product_name |

+------------+----------+--------------+

| Steve | 2 | Shoes |

| John | 1 | Laptop |

| Larry | 1 | Monitor |

| Michael | 5 | Monitor |

| Steven | NULL | NULL |

+------------+----------+--------------+

5 rows in set (0.00 sec)

What happened?
Mysql starts with the left table (buyers). For each row from the table buyers mysql scans the table products, finds the id of the product and returns the product name. Then the product name is joined with the matching row from the table buyers. For unmatched rows it returns null.
To make it simpler, the above query is same as (except the unmatched rows are not returned):

mysql> SELECT buyers.buyer_name, buyers.quantity, products.product_name FROM buyer

s,products WHERE buyers.pid=products.id;

+------------+----------+--------------+

| buyer_name | quantity | product_name |

+------------+----------+--------------+

| Steve | 2 | Shoes |

| John | 1 | Laptop |

| Larry | 1 | Monitor |

| Michael | 5 | Monitor |

+------------+----------+--------------+

4 rows in set (0.00 sec)

Right Join

mysql> SELECT buyer_name, quantity, product_name FROM buyers RIGHT JOIN products ON

buyers.pid=products.id;

+------------+----------+--------------+

| buyer_name | quantity | product_name |

+------------+----------+--------------+

| Steve | 2 | Shoes |

| John | 1 | Laptop |

| Larry | 1 | Monitor |

| Michael | 5 | Monitor |

| NULL | NULL | DVD |

+------------+----------+--------------+

5 rows in set (0.00 sec)

What happens here is Mysql starts with the Right table (products). For each id from the table products MySQL scans the left table - buyers to find the matching pid. When it finds the matching pid it returns the buyer_name and the quantity. For unmatched rows it returns null. From my example above it returns NULL for DVD because no one bought DVD.

Introduction

In this Virtual Workshop we will look at retrieving data from a relational database structure, i.e. with multiple tables, using the SQL JOIN Syntax. Most databases have multiple tables to avoid repeating data. i.e. why enter the details of a customer over and over again.
NOTE: For brevity I will only show 5 records in the examples in this page.

Creating Another Table

In order to see how JOINs work we need to create another table and populate it with data. So let's create a new table called 'genres' with the following properties.

genreID	Unique Identifier	01
genre	Music Genre	Heavy Metal
boughtby	Type of person that buys this music	Greasy Haired Bikers

mysql> CREATE TABLE genres (

    -> genreID INT(2) auto_increment primary key,

    -> genre VARCHAR(20),

    -> boughtby VARCHAR(30)

    ->);

Insert the following genres into your genres table. The descriptions for the people that buy the music (boughtby) - I'll leave to you. You could also add other genres that are perhaps more relevant to your music collection.

Pop	Example INSERT: mysql> INSERT INTO genres VALUES ( -> '?', -> 'Heavy Metal', -> 'Greasy Haired Bikers' -> );
Easy Listening
'Classic' Rock
Heavy Metal
Soul
Seventies
Eighties
Hip Hop
Jazz
Guitar Bands

This could (depending on what you enter as descriptions) result in a table like this:

mysql> SELECT *

    -> FROM genres;

+---------+----------------+------------------------------------------------+

| genreID | genre          | boughtby                                       |

+---------+----------------+------------------------------------------------+

|       1 | Pop            | Little girls and adults who should know better |

|       2 | Easy Listening | Crushed velvet wearing lounge lizards          |

|       3 | "Classic" Rock | Middle-aged men reliving their youth           |

|       4 | Heavy Metal    | Wannabe Bikers - who work PT at safeway        |

|       5 | Soul           | White Boys in thin leather ties                |

|       6 | Seventies      | Those not born til 1980                        |

|       7 | Eighties       | Those born in the '70's                        |

|       8 | Hip Hop        | Middle-class Ghetto wannabes                   |

|       9 | Jazz           | Those that *think* they are better             |

|      10 | Guitar Bands   | Those stuck in 1996                            |

+---------+----------------+------------------------------------------------+

10 rows in set (0.00 sec)

Adding a genre to the 'cds' table

Next we need to add a 'genreID' column to our 'cds' table so we can store information about the genre of each cd. This will be an Integer (numeric) field as it will correspond to the unique identifier ('genreID') from the genres table.

mysql> ALTER TABLE cds

    -> ADD genreID INT(2);

Check this has worked by using the describe command.

mysql> DESCRIBE cds;

+--------+-------------+------+-----+---------+----------------+

| Field  | Type        | Null | Key | Default | Extra          |

+--------+-------------+------+-----+---------+----------------+

| cdID   | int(3)      |      | PRI | NULL    | auto_increment |

| artist | varchar(20) | YES  |     | NULL    |                |

| title  | varchar(30) | YES  |     | NULL    |                |

| year   | int(4)      | YES  |     | NULL    |                |

| label  | varchar(20) | YES  |     | NULL    |                |

| bought | date        | YES  |     | NULL    |                |

| tracks | int(2)      | YES  |     | NULL    |                |

| genreID| int(2)      | YES  |     | NULL    |                |

+--------+-------------+------+-----+---------+----------------+

8 rows in set (0.00 sec)

IMPORTANT: As we now have a column called 'genreID' in both tables we need to distinguish which we are talking about. This is why we have been prefixing the column name with the table name to ensure we can tell the difference. For example 'cds.genreID' and 'genres.genreID' are easily distinguishable in our example.
We are now ready to enter the genre type for each cd into our CDs table.
If we decide that our 'Jamiroquai' album is a soul album we need the 'genres.genreID' number from our 'genres' table for the soul entry, i.e. '5'. We must UPDATE the Jamiroquai record in the CDs table so that the 'cds.genreID' column also has a value of '5'.

mysql> UPDATE cds

    -> SET cds.genreID = 5

    -> WHERE (cds.cdID = 2);

If we issue a SELECT all command we can see the effect this has.

mysql> select * from cds;

+------+-------------------+------------------+------+---------+

| cdid | artist            | title            | year | genreID |

+------+-------------------+------------------+------+---------+

|    2 | Jamiroquai        | A Funk Odyssey   | 2001 |     5   |

Continue and enter the rest of the genres into the CDs table.

Beginning with Joins

Before starting with joins we should say a little about what exactly a join is. Basically it is the combining of two rows based on the comparative values in selected columns. This 'super-row' exists only for the duration of the query that creates it. We need to use joins to temporarily create 'complete' records from a database which may split related data across several tables (perhaps as a result of normalisation).

Cross-Join

Syntax:

SELECT

FROM ,

A cross-join between two tables takes the data from each row in table1 and joins it to the data from each row in table2. To give an example lets look at two very simple tables.

id	animal
1	Cat
2	Dog
3	Cow

id	Food
1	Milk
2	Bone
3	Grass

A cross-join on these tables would produce the following result.

1	Cat	1	Milk
1	Cat	2	Bone
1	Cat	3	Grass
2	Dog	1	Milk
2	Dog	2	Bone
2	Dog	3	Grass
3	Cow	1	Milk
3	Cow	2	Bone
3	Cow	3	Grass

Where every row from one table is joined to every row in the other table. We can also see the effect of this by using an SQL cross-join on our tables (although in this example we are asking only to display one column from each table).

mysql> SELECT cds.artist, genres.genre

    -> FROM cds, genres;

You should see every artist associated with every genre. This is obviously not a very useful join, but does specify the need for a join that uses some kind of comparison between the two tables.

The Equi-Join or Inner Join

Syntax:

SELECT

FROM ,

WHERE (Table1.column = Table2.column)

In the equi-join the comparison we are making between two columns is that they match the same value. We can use this method to select certain fields from both tables and only the correct rows will be joined together. So if we were to use this join on the cds and genres tables in our own database (using the CDs that Mark provided in the last virtual workshop as an example).

mysql> SELECT cds.artist, cds.title, genres.genre

    -> FROM cds, genres

    -> WHERE (cds.genreID = genres.genreID);

+------------+------------------------------+----------------+

| artist     | title                        | genre          |

+------------+------------------------------+----------------+

| Jamiroquai | A Funk Odyssey               | Soul           |

| Abba       | Abbas Greatest Hits          | Seventies      |

| Various    | Now 49                       | Pop            |

| westlife   | westlife                     | Pop            |

| Various    | Eurovision Song contest 2001 | Easy Listening |

+------------+------------------------------+----------------+

5 rows in set (0.00 sec)

Obviously your data will be different as you should have different CDs and different genres. If we compare the cross-join and equi-join we can see that the equi-join is just the cross join with a very restrictive WHERE condition, that forces only the rows in the second table RELEVANT to the rows in the first table to be retrieved. This method is fine if all we want to do is look at normalised data in a temporarily flat database view. However if we want to filter this data, we have to start adding more conditions to our WHERE clause and it seems rather redundant to have to specify joining conditions as part of the WHERE condition every time.

The Left Join

The left join is a mechanism used to join tables before we add other conditions such as WHERE etc.
Syntax:

SELECT

FROM

LEFT JOIN

ON Table1.column = Table2.column

Without adding a WHERE clause the Left Join produces the same results as the equi-join example above.

mysql> SELECT cds.artist, cds.title, genres.genre

    -> FROM cds

    -> LEFT JOIN genres

    -> ON cds.genreID = genres.genreID;

+-------------------+------------------------------+----------------+

| artist            | title                        | genre          |

+-------------------+------------------------------+----------------+

| Jamiroquai        | A Funk Odyssey               | Soul           |

| Abba              | Abbas Greatest Hits          | Seventies      |

| Various           | Now 49                       | Pop            |

| westlife          | westlife                     | Pop            |

| Various           | Eurovision Song contest 2001 | Easy Listening |

+-------------------+------------------------------+----------------+

5 rows in set (0.00 sec)

An important thing to note with this particular join is that even if there are no records in the second table (in this case 'genres') data will still be displayed from the first table. Or in other words data from the LEFT of the join will be displayed and is where the term LEFT JOIN comes from. To demonstrate this, UPDATE a genreID from row four (cdID of 5, because we deleted one row in part three) of the cds table (in this case westlife) to a value that doesn't exist in the genres table.

mysql> UPDATE cds

SET cds.genreID = 100

WHERE (cds.cdID = 5);

...and run the query again....

mysql> SELECT cds.artist, cds.title, genres.genre

    -> FROM cds

    -> LEFT JOIN genres

    -> ON cds.genreID = genres.genreID;

+-------------------+------------------------------+----------------+

| artist            | title                        | genre          |

+-------------------+------------------------------+----------------+

| Jamiroquai        | A Funk Odyssey               | Soul           |

| Abba              | Abbas Greatest Hits          | Seventies      |

| Various           | Now 49                       | Pop            |

| westlife          | westlife                     | NULL           |

| Various           | Eurovision Song contest 2001 | Easy Listening |

+-------------------+------------------------------+----------------+

5 rows in set (0.00 sec)

The artist and title are still displayed even though there is no data in the genre (and thus NULL is shown). To further illustrate this we can issue a RIGHT JOIN which is a variation where all the data on the RIGHT side of the join (the second table) is returned regardless of the presence of data from the first table.
Reset row four of the cds table to have the correct genreID value.

mysql> UPDATE cds

    -> SET cds.genreID = 1

    -> WHERE (cds.cdID = 5);

And run the RIGHT JOIN query including genres.genreID.

mysql> SELECT cds.artist, cds.title, genres.genreID, genres.genre

    -> FROM cds

    -> RIGHT JOIN genres

    -> ON cds.genreID = genres.genreID;

+--------------------+------------------------------+---------+----------------+

| artist             | title                        | genreID | genre          |

+--------------------+------------------------------+---------+----------------+

| Various            | Now 49                       |       1 | Pop            |

| westlife           | westlife                     |       1 | Pop            |

| Various            | Eurovision Song contest 2001 |       2 | Easy Listening |

| NULL               | NULL                         |       3 | "Classic" Rock |

| NULL               | NULL                         |       4 | Heavy Metal    |

| Jamiroquai         | A Funk Odyssey               |       6 | Soul           |

| Abba               | Abbas Greatest Hits          |       6 | Seventies      |

| NULL               | NULL                         |       7 | Eighties       |

| NULL               | NULL                         |       8 | Hip Hop        |

| NULL               | NULL                         |       9 | Jazz           |

| NULL               | NULL                         |      10 | Guitar Bands   |

+--------------------+------------------------------+---------+----------------+

19 rows in set (0.00 sec)

Note that where there aren't any cds in a genre then a NULL value is returned. This is because every record of the RIGHT side must be returned at least once by the RIGHT JOIN.

Adding a WHERE Clause to our Join

Now we have the join occurring out with the WHERE clause, we can begin to add other conditions. For example if we want to select only the pop CDs

mysql> SELECT cds.artist, cds.title, genres.genre

    -> FROM cds

    -> LEFT JOIN genres

    -> ON cds.genreID = genres.genreID

    -> WHERE genres.genre = 'pop';

+----------+----------+-------+

| artist   | title    | genre |

+----------+----------+-------+

| Various  | Now 49   | Pop   |

| westlife | westlife | Pop   |

+----------+----------+-------+

2 rows in set (0.00 sec)

The USING Clause

A variation on the Left Join is the 'USING' clause. You can use this if the columns you are carrying out the join on have the same name.
Syntax:

SELECT

FROM

LEFT JOIN

USING ()

In our example we are joining the tables where cds.genreID is the same as genres.genreID thus genreID is the name of a column in BOTH of tables we are using for the join.

mysql> SELECT cds.artist, cds.title, genres.genre

    -> FROM cds

    -> LEFT JOIN genres

    -> USING (genreID);

+-------------------+------------------------------+----------------+

| artist            | title                        | genre          |

+-------------------+------------------------------+----------------+

| Jamiroquai        | A Funk Odyssey               | Soul           |

| Abba              | Abbas Greatest Hits          | Seventies      |

| Various           | Now 49                       | Pop            |

| westlife          | westlife                     | Pop            |

| Various           | Eurovision Song contest 2001 | Easy Listening |

+-------------------+------------------------------+----------------+

5 rows in set (0.00 sec)

Mini Exercise

Practice joining the 'CDs' and 'genre' tables to retrieve different data about the CDs, e.g.

mysql> SELECT cds.artist, genres.boughtby

    -> FROM cds

    -> LEFT JOIN genres

    -> USING (genreID);

+-------------------+------------------------------------------------+

| artist            | boughtby                                       |

+-------------------+------------------------------------------------+

| Jamiroquai        | White Boys in thin leather ties                |

| Abba              | Those not born til 1980                        |

| Various           | Little girls and adults who should know better |

| westlife          | Little girls and adults who should know better |

| Various           | Crushed velvet wearing lounge lizards          |

+-------------------+------------------------------------------------+

5 rows in set (0.00 sec)

Preparing to Join more than two tables

It is also possible to join more than two tables. In order to do this, however, we will need to make a third table - this time an 'artist' table containing the artistID and name. This will use a lot of the techniques we've used in the previous workshops, so you may have to refer back to refresh your memory as to what you are doing, which will serve as more good revision. We are going to create our new table using a five stage process

CREATE the new table
INSERT our artist data using a SELECT DISTINCT query
ALTER TABLE to add a new 'artistID' COLUMN to the cds TABLE
UPDATE the cds.artistID fields to match the values assigned to an artist in the new table
DROP the artist column from the cds TABLE

We'll go through each step in more detail

CREATE the new table

A simple enough revision of what we've done before.

mysql> CREATE TABLE artists (

    -> artistID int(3) auto_increment primary key,

    -> Artist varchar(20)

    -> );

INSERT our artist data using a SELECT DISTINCT query

This uses similar syntax to that in Workshop Four. We are going to insert the artists from the cds table into the artist.Artist column.

mysql> INSERT INTO artists (artists.Artist)

    ->  SELECT DISTINCT cds.artist

    ->  FROM cds;

You can check this has worked by using a (by now) standard SELECT statement.

mysql> SELECT *

    -> FROM artists;

ALTER TABLE to add a new 'artistID' COLUMN to the cds TABLE

As we are going to refer to the artist by their artistID rather than their name, we need to create a column in the cds table to hold that ID.

mysql> ALTER TABLE cds

    -> ADD artistID int(3);

Check this has worked with a describe statement.

mysql> DESCRIBE cds;

+----------+-------------+------+-----+---------+----------------+

| Field    | Type        | Null | Key | Default | Extra          |

+----------+-------------+------+-----+---------+----------------+

| cdID     | int(3)      |      | PRI | NULL    | auto_increment |

| artist   | varchar(20) | YES  |     | NULL    |                |

| title    | varchar(30) | YES  |     | NULL    |                |

| year     | int(4)      | YES  |     | NULL    |                |

| label    | varchar(20) | YES  |     | NULL    |                |

| bought   | date        | YES  |     | NULL    |                |

| tracks   | int(2)      | YES  |     | NULL    |                |

| genreID  | int(2)      | YES  |     | NULL    |                |

| artistID | int(3)      | YES  |     | NULL    |                |

+----------+-------------+------+-----+---------+----------------+

9 rows in set (0.00 sec)

UPDATE the cds.artistID fields without Joins

For those with access to older versions of MySQL this will have to be done by hand. If you are lucky enough to have MySQL 4.0 installed, then you could use UPDATE joins to speed up this stage, but for consistency between versions MySQL I will demonstrate the method common to older and newer versions of MySQL.

mysql> UPDATE cds

    -> SET cds.artistID = 1

    -> WHERE (cds.artist = 'Jamiroquai');

......etc usng all the artistIDs from the artists table. Check that this has worked with a few joins between the cds and artists TABLEs eg

mysql> SELECT artists.Artist, cds.title

    -> FROM artists

    -> LEFT JOIN cds

    -> USING (artistID)

    -> WHERE (cds.artistID = 1);

+------------+----------------+

| name       | title          |

+------------+----------------+

| jamiroquai | A Funk Odyssey |

+------------+----------------+

DROP the artist column from the cds TABLE

As we can retrieve the artist name by using a join and the artistID we can remove the cds.artist column.

mysql> ALTER TABLE cds

    -> DROP artist;

Query OK, 16 rows affected (0.15 sec)

Records: 16  Duplicates: 0  Warnings: 0

We are now ready to join all three tables together.

Joining Three Tables

Before we start with statements let's just recap what we can expect to do. We have a cds table that contains the foreign keys (i.e. values that correspond to primary keys in another table) called cds.genreID and cds.artistID which also exist in the genres and artists tables. A three table join can be achieved using another version of the Equi-Join or Inner Join where we can use the WHERE clause to limit the returned records based on comparing the artistID and the genreID.

mysql> SELECT artists.Artist, cds.title, genres.genre

    -> FROM cds, artists, genres

    -> WHERE (

    -> (cds.artistID = artists.artistID)

    -> AND (cds.genreID = genres.genreID)

    -> );

+-----------------+------------------------------+----------------+

| name            | title                        | genre          |

+-----------------+------------------------------+----------------+

| Jamiroquai      | A Funk Odyssey               | Soul           |

| Abba            | Abbas Greatest Hits          | Seventies      |

| Various         | Now 49                       | Pop            |

| westlife        | westlife                     | Pop            |

| Various         | Eurovision Song contest 2001 | Easy Listening |

+-----------------------------------------------------------------+

The problem with this is, once more, that we already have a fairly complex WHERE clause just to join ALL the records properly. Ideally what we want is to have a LEFT / RIGHT JOIN, but this is a problem as we cannot compare just two tables (Right vs Left). The solution to this is to use a series of joins. This is where one join is made, but rather than being used to evaluate and display the data from it, the result is passed to a second join and THEN the data can be displayed. A good way to think of this is that the first JOIN creates a virtual table (from joining tables one and two) which can then be joined to the third table.
So let us first make a LEFT JOIN between the 'cds' and 'genres' tables.

mysql> SELECT cds.title, genres.genre

    -> FROM cds

    -> LEFT JOIN genres

    -> ON cds.genreID = genres.genreID;

+------------------------------+----------------+

| title                        | genre          |

+------------------------------+----------------+

| A Funk Odyssey               | Soul           |

| Abbas Greatest Hits          | Seventies      |

| Now 49                       | Pop            |

| westlife                     | Pop            |

| Eurovision Song contest 2001 | Easy Listening |

+------------------------------+----------------+

5 rows in set (0.02 sec)

Next we add another JOIN and SELECT the artists.Artist as well. The order in which you SELECT COLUMNs determines how your results will look, so we should place artists.Artist first.

mysql> SELECT  artists.Artist, cds.title, genres.genre

    -> FROM cds

    -> LEFT JOIN genres

    -> ON cds.genreID = genres.genreID

    -> LEFT JOIN artists

    -> ON cds.artistID = artists.artistID;

+------------+------------------------------+----------------+

| name       | title                        | genre          |

+------------+------------------------------+----------------+

| Jamiroquai | A Funk Odyssey               | Soul           |

| Various    | Now 49                       | Pop            |

| westlife   | westlife                     | Pop            |

| Various    | Eurovision Song contest 2001 | Easy Listening |

| Abba       | Abbas Greatest Hits          | Seventies      |

+------------+------------------------------+----------------+

5 rows in set (0.01 sec)

We can now add a WHERE clause to restrict the output.

mysql> SELECT artists.Artist, cds.title, genres.genre

    -> FROM cds

    -> LEFT JOIN genres

    -> ON cds.genreID = genres.genreID

    -> LEFT JOIN artists

    -> ON cds.artistID = artists.artistID

    -> WHERE (genres.genre = 'Pop');

+----------+----------+-------+

| name     | title    | genre |

+----------+----------+-------+

| Various  | Now 49   | Pop   |

| westlife | westlife | Pop   |

+----------+----------+-------+

2 rows in set (0.01 sec)

We could also join a fourth table, imagine we were to repeat the steps above to create a 'label' table. We could issue the a statement to join all four tables and display the results.

mysql> SELECT artists.Artist, cds.title, label.Label, genres.genre

    -> FROM cds

    -> LEFT JOIN genres

    -> ON cds.genreID = genres.genreID

    -> LEFT JOIN artists

    -> ON cds.artistID = artists.artistID

    -> LEFT JOIN label

    -> ON cds.labelID = label.labelID;

+------------+------------------------------+------------+----------------+

| Artist     | title                        | Label      | genre          |

+------------+------------------------------+------------+----------------+

| Jamiroquai | A Funk Odyssey               | Sony soho2 | Soul           |

| Various    | Now 49                       | virgin     | Pop            |

| westlife   | westlife                     | jive       | Pop            |

| Various    | Eurovision Song contest 2001 | EMI        | Easy Listening |

| Abba       | Abbas Greatest Hits          | EMI        | Seventies      |

+------------+------------------------------+------------+----------------+

5 rows in set (0.01 sec)

This method of adding tables and performing JOINS will work only if one TABLE has all the foreign keys. Joins CAN be more complex mixing types etc, but I don't think it is necessary to go into that here.

UPDATE and DELETE Joins

Requires MySQL 4.0 or later.
As MySQL 4.0 has been deemed to be stable enough for production use it is worth mentioning some of the functionality that has been added and significantly this includes UPDATE and DELETE joins. To find out the version of MySQL that you have access to you are usually told when you login at the command-line:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2471 to server version: 4.0.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

But you can also check from the command-line using a 'STATUS' command:

mysql> STATUS

--------------

mysql  Ver 12.18 Distrib 4.0.12, for pc-linux (i586)

Connection id:          2563

Current database:

Current user:           kbrown@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Server version:         4.0.12

If you do indeed have access to MySQL 4.0 then you can continue with this workshop.

UPDATE Joins

Before looking at the syntax let's pause for a moment and think why we would want a to perform a Join as part of a UPDATE statement? Well the answer is to update more than one table using only one statement or indeed to insert the values from one table into another.
The syntax for UPDATE joins is very similar to a normal UPDATE statement only including one of the Joins specified above in much the same way as the select statement did. Thus to use an Equi-Join in the statement, we specify the tables together, what we want to SET and then use a WHERE condition to apply the constraint.

UPDATE ,

SET  = 'new_value'

WHERE

For Example let's temporarily change the values of the 'A Funk Odyssey' album so that it was recorded by 'George Clinton' (see bio if you're confused), and called 'The Funkship Odyssey' (one of the lesser known works ;-). Thus we have to update the 'cds' (to change the title) and the 'artists' table, use one condition to constrain the join (cds.artistID = artists.artistID) and then a final condition to only effect that row (and not all rows).

mysql> UPDATE cds, artists

    -> SET

    -> cds.title = 'The Funkship Odyssey',

    -> artists.Artist = 'George Clinton'

    -> WHERE (artists.artistID = cds.artistID)

    -> AND (cds.cdID = '2');

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

You can use a SELECT join described above to view how this has changed the values.

mysql> SELECT artists.Artist, cds.title

    -> FROM artists

    -> LEFT JOIN cds

    -> USING (artistID)

    -> WHERE (cds.artistID = 1);

+----------------+----------------------+

| name           | title                |

+----------------+----------------------+

| George Clinton | The Funkship Odyssey |

+----------------+----------------------+

1 row in set (0.00 sec)

Of course by replacing 'Jamiroquai' in the 'artists' table it now means that George has recorded all their albums and since George didn't record 'The Funkship Odyssey' we'd better put things back the way they were. This time we will use a LEFT JOIN to UPDATE the values. In a SELECT statement the LEFT JOIN allowed us to join the tables before applying any constraints and in an UPDATE join this is no different. First we make the LEFT JOIN, then use SET to provide new and finally use the WHERE condition to restrict which rows this effects.

mysql> UPDATE cds->LEFT JOIN artists

    -> ON cds.artistID = artists.artistID

    -> SET

    -> cds.title = 'A Funk Odyssey',

    -> artists.name = 'Jamiroquai'

    -> WHERE (cds.cdID = '2');

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

The other purpose of the UPDATE join is to set the value of a column in one table to that of a column in another table. This isn't particularly spectacular as any column value in a table can be set to the value of another column in the same row and as any join results in a 'big virtual table' this can be done also.
To illustrate let's recreate the artist field in the cds table that we deleted above and repopulate the column using an UPDATE join. First modify the cds table:

mysql> ALTER TABLE cds

    -> ADD artist VARCHAR(20);

Next set a value of 'Unknown' for each row.

mysql> UPDATE cds

    -> SET artist = 'Unknown';

Query OK, 9 rows affected (0.00 sec)

Rows matched: 9  Changed: 9  Warnings: 0

mysql> SELECT cds.artist, cds.title

    -> FROM cds;

+---------+------------------------------+

| artists | title                        |

+---------+------------------------------+

| Unknown | A Funk Odyssey               |

| Unknown | Now 49                       |

| Unknown | westlife                     |

| Unknown | Eurovision Song contest 2001 |

| Unknown | Abbas Greatest Hits          |

+---------+------------------------------+

5 rows in set (0.01 sec)

Next we will use an UPDATE Join to merge with the 'artists' table and SET the value of the of cds.artist = artists.Artist. Let's only do one artist (Jamiroquai again) to see how this works.

mysql> UPDATE cds

    -> LEFT JOIN artists

    -> USING (artistID)

    -> SET cds.artists = artists.Artist

    -> WHERE (cds.cdID = '2');

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT cds.artists, cds.title

    -> FROM cds;

+------------+------------------------------+

| artists    | title                        |

+------------+------------------------------+

| Jamiroquai | A Funk Odyssey               |

| Unknown    | Now 49                       |

| Unknown    | westlife                     |

| Unknown    | Eurovision Song contest 2001 |

| Unknown    | Abbas Greatest Hits          |

+------------+------------------------------+

5 rows in set (0.01 sec)

We can rerun the query without the final WHERE condition that constrains the row and all the artists will be correctly identified by the join.

mysql> UPDATE cds

    -> LEFT JOIN artists

    -> USING (artistID)

    -> SET cds.artists = artists.name;

Query OK, 8 rows affected (0.02 sec)

Rows matched: 9  Changed: 8  Warnings: 0

mysql> SELECT cds.artists, cds.title

    -> FROM cds;

+-------------+------------------------------+

| artists     | title                        |

+-------------+------------------------------+

| Jamiroquai  | A Funk Odyssey               |

| Various     | Now 49                       |

| westlife    | westlife                     |

| Various     | Eurovision Song contest 2001 |

| Abba        | Abbas Greatest Hits          |

+-------------+------------------------------+

5 rows in set (0.01 sec)

Notice: These last two examples have included a USING clause as part of the Join.

DELETE Joins

The final Join that I am going to discuss in this workshop is the DELETE Join. This is pretty much a case of 'same again' so I'll only give a quick examples in which I'll delete Westlife who have an artistID = '3' (if only it were that easy ;->.
To DELETE from just the 'cds' table include only the 'cds' table between DELETE and FROM (the join is made after FROM so both tables are needed there).

mysql> DELETE cds

    -> FROM cds, artists

    -> WHERE (cds.artistID = artists.artistID)

    -> AND (cds.artistID = '3');

To DELETE from both tables:

mysql> DELETE cds, artist

    -> FROM cds, artist

    -> WHERE (cds.artistID = artists.artistID)

    -> AND (cds.artistID = '3');

Phew.. I bet you're glad that's over. ;-)
SQL Injection
SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

// a good user's name

$name = "timmy";

$query = "SELECT * FROM customers WHERE username = '$name'";

echo "Normal: " . $query . "
";

// user input that uses SQL Injection

$name_bad = "' OR 1'";

// our MySQL query builder, however, not a very safe one

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";

// display what the new query will look like, with injection

echo "Injection: " . $query_bad;

Normal: SELECT * FROM customers WHERE username = 'timmy'

Injection: SELECT * FROM customers WHERE username = '' OR 1''

The normal query is no problem, as our MySQL statement will just select everything from customers that has a username equal to timmy.

However, the injection attack has actually made our query behave differently than we intended. By using a single quote (') they have ended the string part of our MySQL query

username = ' '

and then added on to our WHERE statement with an OR clause of 1 (always true).

username = ' ' OR 1

This OR clause of 1 will always be true and so every single entry in the "customers" table would be selected by this statement!

name_evil = "'; DELETE FROM customers WHERE 1 or username = '";

// our MySQL query builder really should check for injection

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";

// the new evil injection query would include a DELETE statement

echo "Injection: " . $query_evil;

SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or username = ' '

Prevention

What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.
Lets try out this function on our two previous injection attacks and see how it works.

//NOTE: you must be connected to the database to use this function!

// connect to MySQL

$name_bad = "' OR 1'";

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";

echo "Escaped Bad Injection: 
" . $query_bad . "
";

$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";

echo "Escaped Evil Injection: 
" . $query_evil;

Escaped Bad Injection:

SELECT * FROM customers WHERE username = '\' OR 1\''

Escaped Evil Injection:

SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''

Notice that those evil quotes have been escaped with a backslash \, preventing the injection attack. Now all these queries will do is try to find a username that is just completely ridiculous:

Bad: \' OR 1\'
Evil: \'; DELETE FROM customers WHERE 1 or username = \'

And I don't think we have to worry about those silly usernames getting access to our MySQL database. So please do use the handy mysql_real_escape_string() function to help prevent SQL Injection attacks on your websites

MVC

Abstract

Model View Controller.
This tutorial will take you from the beginning to the end of building a MVC framework. The object is not soley to produce the finished MVC framework, although that will happen, but to demonstrate how MVC works and some of the concepts that lay behind it..

What is MVC?

MVC is a design pattern. A Design pattern is a code structure that allows for common coding frameworks to be replicated quickly. You might think of a design pattern as a skeleton or framework on which your application will be built.
In the MVC framework that is created in this tutorial, several key points will be raised. The first is the frameworks needs a single point of entry, ie: index.php. This is where all access to the site must be controlled from. To ensure that a single point of entry is maintained, htaccess can be utilized to ensure no other file may be accessed, and that we hide the index.php file in the url. Thus creating SEO and user friendly URL's.
It is beyond the scope of this tutorial to show how to set up htaccess and mod_rewrite and more information on these can be gained from the Apache manual. The .htaccess file itself looks like this.

RewriteEngine on

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d

RewriteRule ^(.*)$ index.php?rt=$1 [L,QSA]

The .htaccess file will permit access to the site via urls such as
http://www.example.com/news/show
If you do not have mod_rewrite available, the entry to the site will be the same, except that the URL will contain the values needed such as:
http://www.example.com/index.php?rt=news/show

The Site Structure

In this tutorial several directories are required to hold the various components that make up the MVC framework. The index.php and the .htaccess files will, of course, reside at the top level. We will need a directory to hold the application code, and directories for the model view and controllers. The site structure should look like this:

html
        application
        controller
        includes
        model
        views
.htaccess
index.php

The Index File

As previously mentioned, the index.php file is our single point of access. As such, it provides the ideal space for declaring variables and site wide configurations. It is in this file that we will also call a helper file to initialize a few values. This file will be called init.php and it will be placed in includes directory as shown in the direcory structure. The index file therefore, will begin like this:

/*** error reporting on ***/
error_reporting(E_ALL);

/*** define the site path constant ***/
$site_path = realpath(dirname(__FILE__));
define ('__SITE_PATH', $site_path);

/*** include the init.php file ***/
include 'includes/init.php';

?>

The index.php file so far only sets the error reporting, includes the init file, and defines the site path constant. With this file in place, and the .htaccess file we can begin to build the registry. In this MVC framework, the registry object is passed to other objects and contains site wide variables without the the use globals. To create a new registry object, we use the init.php file in the includes directory.
Of course, to create new object we need to include the registry class definition file. During the building of the MVC framework we will be including the application class files directly. Any PHP class definition files which are used by the model will be autoloaded as they can become quite cumbersome with larger applications. To alleviate some of this PHP has the __autoload function to help us out. After the application includes, the __autoload function will immediately follow to load class definition files automatically when they are required by the system. That is, when the new keyword is used. The class definitions will be stored in a directory called model. The includes/init.php file should now look like this.

/*** include the controller class ***/
include __SITE_PATH . '/application/' . 'controller_base.class.php';

/*** include the registry class ***/
include __SITE_PATH . '/application/' . 'registry.class.php';

/*** include the router class ***/
include __SITE_PATH . '/application/' . 'router.class.php';

/*** include the template class ***/
include __SITE_PATH . '/application/' . 'template.class.php';

/*** auto load model classes ***/
    function __autoload($class_name) {
    $filename = strtolower($class_name) . '.class.php';
    $file = __SITE_PATH . '/model/' . $filename;

    if (file_exists($file) == false)
    {
        return false;
    }
  include ($file);
}

/*** a new registry object ***/
$registry = new registry;

?>

Here is should be noted that the autoload function uses a naming convention for the class definition files to be included. They must all follow the convention of ending in .class.php and the class name must be that of the .class.php file name. So that to create a new "news" object the class definition file name must be news.class.php and the class must be named "news". With these files in place we are well on the way, however our MVC does not do anything yet. In fact, if you tried to access the index.php file now, you would get many errors about missing files. Mostly from the files in the application directory. So, lets begin by creating those files each can be blank or simply contain

?>

The files to create in the application directory are:

controller_base.class.php
registry.class.php
router.class.php
template.class.php

Note that although these files are not autoloaded, we have still maintained the same naming convention by calling the files .class.php

The Registry

The registry is an object where site wide variables can be stored without the use of globals. By passing the registry object to the controllers that need them, we avoid pollution of the global namespace and render our variables safe. We need to be able to set registry variables and to get them. The php magic functions __set() and __get() are ideal for this purpose. So, open up the registry.class.php in the applications directory and put the following code in it:

Class Registry {

/*
* @the vars array
* @access private
*/
private $vars = array();

/**
*
* @set undefined vars
*
* @param string $index
*
* @param mixed $value
*
* @return void
*
*/
public function __set($index, $value)
{
$this->vars[$index] = $value;
}

/**
*
* @get variables
*
* @param mixed $index
*
* @return mixed
*
*/
public function __get($index)
{
return $this->vars[$index];
}

}

?>

With the registry in place, our system is working. It does not do anything or display anything, but we have a functional system. The __set() and __get() magic function now allow us to set variables within the registry and store them there. Now to add the Model and router classes.

The Model

The Model is the "M" in MVC. The model is where business logic is stored. Business logic is loosely defined as database connections or connections to data sources, and provides the data to the controller. As I am a fan of CAV (Controller Action View) we will blur the line between the Model and Controller. This is not strictly how MVC should work, but this is PHP baby. Our database connection is a simple singleton design pattern and resides in the classes directory and can be called statically from the controller and set in the registry. Add this code to the init.php file we created earlier.

/*** create the database registry object ***/
$registry->db = db::getInstance();

?>

Like all registry members, the database is now globally availabe to our scripts. As the class is a singleton we always get the same instance back. Now that registry objects can be created a method of controlling what is loaded is needed.

The Router

The router class is responsible for loading up the correct controller. It does nothing else. The value of the controller comes from the URL. The url will look a like this:
http://www.example.com/index.php?rt=news
or if you have htaccess amd mod_rewrite working like this:
http://www.example.com/news
As you can see, the route is the rt variable with the value of news. To begin the router class a few things need to be set. Now add this code to the router.class.php file in the application directory.

class router {
/*
* @the registry
*/
private $registry;

/*
* @the controller path
*/
private $path;

private $args = array();

public $file;

public $controller;

public $action;

function __construct($registry) {
$this->registry = $registry;
}

So it does not look like much yet but is enough to get us started. We can load the router into the registry also. Add this code to the index.php file.

/*** load the router ***/
$registry->router = new router($registry);

Now that the router class can be loaded, we can continue with the router class by adding a method to set the controller directory path. Add this block of code to the router.class.php file.

/**
*
* @set controller directory path
*
* @param string $path
*
* @return void
*
*/
function setPath($path) {

        /*** check if path i sa directory ***/
        if (is_dir($path) == false)
        {
                throw new Exception ('Invalid controller path: `' . $path . '`');
        }
        /*** set the path ***/
        $this->path = $path;
}

And to set the controller path in the registry is a simple matter of adding this line to the index.php file

/*** set the path to the controllers directory ***/
$router->setPath (__SITE_PATH . 'controller');

With the controller path set we can load the controller. We will create a method to called loader() to get the controller and load it. This method will call a getController() method that will decide which controller to load. If a controller is not found then it will default back to the index. The loader method looks like this.

/**
*
* @load the controller
*
* @access public
*
* @return void
*
*/
public function loader()
{
        /*** check the route ***/
        $this->getController();

        /*** if the file is not there diaf ***/
        if (is_readable($this->file) == false)
        {
                echo $this->file;
                die ('404 Not Found');
        }

        /*** include the controller ***/
        include $this->file;

        /*** a new controller class instance ***/
        $class = $this->controller . 'Controller_';
        $controller = new $class($this->registry);

        /*** check if the action is callable ***/
        if (is_callable(array($controller, $this->action)) == false)
        {
                $action = 'index';
        }
        else
        {
                $action = $this->action;
        }
        /*** run the action ***/
        $controller->$action();
}

The getController method that the loader() method calls does the work. By taking the route variables from the url via $_GET['rt'] it is able to check if a contoller was loaded, and if not default to index. It also checks if an action was loaded. An action is a method within the specified controller. If no action has been declared, it defaults to index. Add the getController method to the router.class.php file.

/**
*
* @get the controller
*
* @access private
*
* @return void
*
*/
private function getController() {

        /*** get the route from the url ***/
        $route = (empty($_GET['rt'])) ? '' : $_GET['rt'];

        if (empty($route))
        {
                $route = 'index';
        }
        else
        {
                /*** get the parts of the route ***/
                $parts = explode('/', $route);
                $this->controller = $parts[0];
                if(isset( $parts[1]))
                {
                        $this->action = $parts[1];
                }
        }

        if (empty($this->controller))
        {
                $this->controller = 'index';
        }

        /*** Get action ***/
        if (empty($this->action))
        {
                $this->action = 'index';
        }

        /*** set the file path ***/
        $this->file = $this->path .'/'. $this->controller . '.php';
}
?>

The Controller

The Contoller is the C in MVC. The base controller is a simple abstract class that defines the structure of all controllers. By including the registry here, the registry is available to all class that extend from the base controller. An index() method has also been included in the base controller which means all controller classes that extend from it must have an index() method themselves. Add this code to the controller.class.php file in the application directory.

Abstract Class baseController {

/*
* @registry object
*/
protected $registry;

function __construct($registry) {
$this->registry = $registry;
}

/**
* @all controllers must contain an index method
*/
abstract function index();
}

?>

Whilst we are in the controller creating mood, we can create an index controller and a blog controller. The index controller is the sytem default and it is from here that the first page is loaded. The blog controller is for an imaginary blog module. When the blog module is specified in the URL
http://www.example.com/blog
then the index method in the blog controller is called. A view method will also be created in the blog controller and when specified in the URL
http://www.example.com/blog/view
then the view method in the blog controller will be loaded. First lets see the index controller. This will reside in the controller directory.

class indexController extends baseController {

public function index() {
    /*** set a template variable ***/
        $this->registry->template->welcome = 'Welcome to PHPRO MVC';

    /*** load the index template ***/
        $this->registry->template->show('index');
}

}

?>

The indexController class above shows that the indexController extends the baseController class, thereby making the registry available to it without the need for global variables. The indexController class also contains the mandatory index() method that ll controllers must have. Within itn index() method a variable named "welcome" is set in the registry. This variable is available to the template when it is loaded via the template->show() method.
The blogController class follows the same format but has has one small addition, a view() method. The view() method is an example of how a method other than the index() method may be called. The view method is loaded via the URL
http://www.example.com/blog/view

Class blogController Extends baseController {

public function index() {
        $this->registry->template->blog_heading = 'This is the blog Index';
        $this->registry->template->show('blog_index');
}

public function view(){

        /*** should not have to call this here.... FIX ME ***/

        $this->registry->template->blog_heading = 'This is the blog heading';
        $this->registry->template->blog_content = 'This is the blog content';
        $this->registry->template->show('blog_view');
}

}
?>

The View

The View, as you might have guessed, is the V in MVC. The View contains code that relates to presentation and presentation logic such as templating and caching. In the controller above we saw the show() method. This is the method that calls the view. The major component in the PHPRO MVC is the template class. The template.class.php file contains the class definition. Like the other classes, it has the registry available to it and also contains a __set() method in which template variables may be set and stored.
The show method is the engine room of the view. This is the method that loads up the template itself, and makes the template variables available. Some larger MVC's will implement a template language that adds a further layer of abstraction from PHP. Added layers mean added overhead. Here we stick with the speed of PHP within the template, yet all the logic stays outside. This makes it easy for HTML monkies to create websites without any need to learn PHP or a template language. The template.class.php file looks like this:

Class Template {

/*
* @the registry
* @access private
*/
private $registry;

/*
* @Variables array
* @access private
*/
private $vars = array();

/**
*
* @constructor
*
* @access public
*
* @return void
*
*/
function __construct($registry) {
        $this->registry = $registry;

}

/**
*
* @set undefined vars
*
* @param string $index
*
* @param mixed $value
*
* @return void
*
*/
public function __set($index, $value)
{
        $this->vars[$index] = $value;
}

function show($name) {
        $path = __SITE_PATH . '/views' . '/' . $name . '.php';

        if (file_exists($path) == false)
        {
                throw new Exception('Template not found in '. $path);
                return false;
        }

        // Load variables
        foreach ($this->vars as $key => $value)
        {
                $$key = $value;
        }

        include ($path);
}

}

?>

Templates

The templates themselves are basically HTML files with a little PHP embedded. Do not let the separation Nazi's try to tell you that you need to have full seperation of HTML and PHP. Remember, PHP is an embeddable scripting language. This is the sort of task it is designed for and makes an efficient templating language. The template files belong in the views directory. Here is the index.php file.

echo $welcome; ?>

Well, that was pretty amazing.. Now for the blog_index.php file.

echo $blog_heading; ?>

And finally the blog_view.php file..

echo $blog_heading; ?>

echo $blog_content; ?>

In the above template files note that the variable names in the templates, match the template variables created in the controller.

Download Source

The full source code for this MVC Framework is available for download here.
http://www.phpro.org/downloads/mvc-0.0.4.tar.gz

Update

Due to the populariity of this tutorial and the framework, a small side project has been spawned that builds on this tutorial and adds some practical functionality. Users are recommended to get the basics in this tutorial and move on to the next level. See more at http://sevenkevins.com and see how simple and easy MVC and application development can be.

Conclusion

It is hoped that you have found some insight into how MVC works whilst reading this tutorial. The MVC Framework you have build here should be used as a guide only, although it is a fully functional implementation, it is left to the user to build on it and take it to new hights. If you are using this MVC in any way, or have corrections or improvements, simply contact us.