PHP Freaks - Tutorial: Data Joins Unions

Membership

Main Menu

Forum Boards

Stats

20 tutorials
74,655 members
732,519 forum posts
13 blog posts

Tutorials

Data Joins Unions

by Barry Andrew on Jun 30, 2008 5:59:59 PM

Cartesian join

This is not a join you will you use very often (although you may use one accidentally, especially when using the FROM A,B syntax for your inner joins and forgetting to put the join condition in the WHERE clause) but it does have its practical uses, as we shall see in the sample application at the end of this tutorial. (Another practical use is generating data)

A cartesian join is produced by omitting the join condition a join. In this case, every row in the first table is joined with every row in the second. For example, we'll just count the number of rows returned rather than list them (for obvious reasons)

Result

+-------+
| total |
+-------+
| 19440 |     24 pupils * 81 choices * 10 subjects
+-------+

Now we will look at UNIONS

Comments

Very useful!
My php for dummies was useless for joins - many thanks!

1. Tom Butterworth on Jul 1, 2008 6:20:06 PM

Barand, could you extend the tutorial to include how to loop the results of the query. I find that when I use a Join , the query will return the first result and then stop. It will not run thru both tables and list all the results. When the 1st match is found, the query then doesnt look thru for other matches which seems to mean that the if stmt following the result is not running correctly .

Ex: $sql11 = "SELECT * FROM `TabA`
INNER JOIN
`TabB` USING (Sid)
WHERE TabA.Eid = $group AND TabA.Month = $month AND TabA.Year = $year ORDER BY TabB.Name asc" ;

$result11 = mysql_query($sql11);
if($myrow11 = mysql_fetch_array($result11))
{ do whatever}
while($myrow11 = mysql_fetch_array($result11);

Thanks !

2. swatisonee on Jul 3, 2008 4:07:11 AM

swatisonee: You'd do it like with all other query results:

$result = mysql_query('SELECT * FROM table1 INNER JOIN table2 ON table1.field1 = table2.field1'); if (mysql_num_rows($result)) { while($row = mysql_fetch_array()) { // do something } } else { echo 'The query returned no results'; }

3. Daniel Egeberg on Jul 3, 2008 5:45:05 AM

Thanks Daniel but I'm afraid I still fail to run thru the loop. I will post on the help forum the code as it runs without using Join and then the one I am working on to replace it using Joins as it is too long to post as a tute. comment.

4. swatisonee on Jul 4, 2008 4:38:28 AM