shadowline
LIKE and NOT LIKE << Joins >>
Many people refer to a database as a "relational" database. More than one table is in relation to another table. Two smaller pieces of information that make a whole when combined.

There are going to be times when you will want to take a bit of information from two (or more) tables with a certain criteria. Using an address book for example, you may have one table of friends and another table of how much money they owe you. You can set the criteria to find a specific criteria that appears in both tables and produce the results. You are "joining" the information from both tables to create a full result.

Address Database
phone
first_name
ph
Christine516.6785
Chris418.2594
David725.1581
Jennifer458.2158
Lorna942.4827
owes
first_name
money
Chris5
Lorna5
George10
Chris2
Sam10

A popular join is called the INNER JOIN.

$sql = mysql_query("SELECT * FROM phone, owes WHERE phone.first_name=owes.first_name");

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row [1] $row[2] $row[3] <br />";
}

The above example will compares or joins the two tables PHONE and OWES. It will look in the column called FIRST_NAME in the PHONE table and it will look in the column called FIRST_NAME in the OWES table. If any information matches in those two columns in the seperate tables, it will be put into the results.

In total, there are 4 columns of information being looked at. The above example is printing out all of the columns just to show which information is being passed into the results. You may choose exactly which data to use and display as required for a real application.



The important part of an INNER join is that if no matches are found, no results are created.

Another form of join is called the LEFT JOIN. It behaves in the same way as the inner join, but it will produce extra results if there is a match in one table but not the other.

$sql = mysql_query("SELECT * FROM phone LEFT JOIN owes ON phone.first_name=owes.first_name");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] $row[3]<br />";
}

The comma between the table names have been replaced by the words LEFT JOIN. The word WHERE has been replaced by the word ON.

During an inner join, only records matching in both tables would be placed into the results. During a left join, all of the records of the first table will be placed into the results. If the records match in the second table, they will be added into the results otherwise a NULL result will be added.



LIKE and NOT LIKE << Joins >>
shadow

Advertise in the banner along the top, Click here!

Valid XHTML 1.0 Transitional Valid CSS! Text Link Ads
shadow
shadow