PHP / Mysql Joins

Andyb

Posted on 23 Jan 2010, 21:08:22

Access: Member
Total Posts: 18
Joined: 2008-05-16

I have been slowly building my PHP/Mysql skills and just cannot get my head around the concept of PHP/Mysql Joins, I can see the great benefits just can't master the concept.

I have read loads of books on the subject and this is one area that I am struggling !

Anyone offer a simple tutorial ?

Thanks,

AndyB

#1729

Chris

Posted on 24 Jan 2010, 02:42:31

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

[img]http://network-13.com/images/jointables.jpg[/img]

Take a look at this example. These are 3 tables used by the news system.

news30_story = news articles
news30_users = user accounts
news30_access = access levels user accounts are assigned to

Say you wanted to select an article from the news table. Specifically, you want to select the story, date it was posted, and the author of the article. To select those fields would be quite easy like so

Code:


<?php
require_once 'db.php';
require_once 'config.php';
// we need to include these

$newsdata = DataAccess::fetch("
SELECT shortstory, story, timestamp, author FROM news30_story
");

print_r($newsdata);
?>

This would output

[img]http://network-13.com/images/jointables_1.jpg[/img]

Which is what we wanted except the author field is the unique ID of the author, not the actual authors name. To fix this we use an inner join like so.

Code:


<?php
require_once 'db.php';
require_once 'config.php';
// we need to include these

$newsdata = DataAccess::fetch("
SELECT shortstory, story, timestamp, news30_users.user AS author_name FROM news30_story
INNER JOIN news30_users ON news30_story.author = news30_users.uid");

print_r($newsdata);
?>

What we're basically telling the server to do here is select data from the news table, but at the same time select data from the user table where the news30.story.author = news30_user.uid. This should now output

[img]http://network-13.com/images/jointables_3.jpg[/img]

This is just joining one table, you can of course join multiple tables.

The news system is setup like so.

news30.access.uid -> news30_users.access -> news30_story.author

If you wanted to select data from those 3 tables, you could use 3 different select queries and it would show like this

[img]http://network-13.com/images/jointables_2.jpg[/img]

Using joins we can get data from the same tables with 1 select query like so

Code:


<?php
require_once 'db.php';
require_once 'config.php';
// we need to include these

$newsdata = DataAccess::fetch("
SELECT story, timestamp, news30_users.user AS author_name, news30_access.name AS access_name FROM news30_story
INNER JOIN news30_users ON news30_story.author = news30_users.uid
INNER JOIN news30_access ON news30_users.access = news30_access.uid
");

print_r($newsdata);
?>

This should output

[img]http://network-13.com/images/jointables_4.jpg[/img]

This is exactly the same as the previous join example except a new line has been added telling the server to join the news30_access table as well.

Hopefully this should clear up using joins a bit and if you still can't wrap your head around it or are not sure about something let me know.

#1730

Last edited by Chris at 2010-01-24 02:45:46 Reason:

Andyb

Posted on 02 Feb 2010, 19:48:15

Access: Member
Total Posts: 18
Joined: 2008-05-16

Wow Chris,

That's exacltly what I was looking for, will need to have a play once I have a few minutes.

Will let you know if I am struggling,

Thanks,

Andy B.

#1809

Calamity

Posted on 21 Feb 2010, 02:14:53

Access: Member
Total Posts: 33
Joined: 2008-04-11

Can i ask why you use "DataAccess::fetch"?

#1936

Chris

Posted on 24 Feb 2010, 22:58:56

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

Custom class written for the news script.

#1956