MySQL Join Tutorial

MySQL Join Tutorial In mySQL is a posibility to make joins. A join query is the joining of two (or more!) tables in one query. For example there are two tables:

Tabel cars
- ID
- name

Tabel colors
- ID
- color
- car_ID

In the first table are all the cars. In the second table there are all posible colors for the cars. Since a car can have more colors we'll make a join to see wich colors are available for a car.

First we are going to fetch all cars:

MYSQL

SELECT
*
FROM
cars

As you can see, i don't write the query in one line as you might be used, but over several lines. This is a bit useless in simple queries as this one, but if you have got some complex queries, this can improve the readability of your query. Get used to doing this when you are writing complex queries to keep things simple for yourself (and other coders).

MYSQL

SELECT
*
FROM
cars
JOIN
colors
ON
colors.car_ID=cars.id

This query looks a lot like the first one, but we added a few lines. At first, after the JOIN, we state wich table we want to join with. In this case the "colors" table. Then is the difficult peace... After the ON, we state on wich columns the tables should join. Every color has a reference to a car by the "car_ID" column. Every car has an ID, and these two columns are the link between the two tables. These are the columns we want to join.

In complex queries, always point to the table you're talking about. For example; if you use "WHERE id=3", MySQL doesn't know wich ID you're talking about. Both tables have a column "ID", so you have to say to MySQL wich table you mean. To do this, put the name of the table in front of the column name.

In case of table names that are very long, you can abbreviate there names to keep things simple and clean. This is done like so:

MYSQL

SELECT
*
FROM
cars AS cr
JOIN
colors AS cl
ON
cl.car_ID=cr.id

As you can see in above query, i state that MySQL should name the table cars "cr" from now one, and the table colors should be names "cl". This can save you many typing and keeps things nice and clear.

Acquiring PHP MySQL
PHP and MySQL are already packaged in Linux and Mac OS X OSs. However, most PHP developer are actually using Windows when developing PHP applications. This is also true to myself. I have Mandrake Linux 10 but Ill just use it for testing purposes of my finished scripts...
Alternating Rows: Mysql
The first thing we need to do is connect to the database. I always put my db connection in a universal file, that way I can just change the variables once if I need to; but for the sake of the tutorial, im putting it in this file.
PHP User Stats
In this tutorial I am going to teach you how to make a User Stats menu in PHP and MySQL which displays: Unique hits, Unique hits today, Total hits, Total hits today. If you dont have MySQL you can get one for free here.
PHP/MySQL Tutorial System
This Tutorial will run you through the basics of building your own Tutorial CMS!
PHP Navigation and Connecting to a MySQL Database
PHP Navigation and Connecting to a MySQL Database
Alternating Rows: Arrays
The first thing we do here is echo out the html, again I wont go over this because theres really nothing to explain, except the css. We have two css classes, one for each of the alternating rows.
Create a Contact Page with HTML and PHP
I have made a tutorial about how to install EasyPhp and now i come with an example. Today we learn how to create a Contact Page in HTML and PHP very easy! Download the next files to exercise on them:
Register Login Script
The following tutorial will teach you how to add a login/register script to your site. This tutorial requires 6 files, and PHP with MySQL installed.
Design an Online Chat Room with PHP and MySQL
In this article, you will learn how to design and develop a simple online chat room with PHP and MySQL. This tutorial explains every steps of the development, including both database design and PHP programming. Basic computer skills and knowledge of HTML and PHP are required. Ok, lets begin now. ....
Dynamic PHP Google Sitemap
This tutorial will show You how to generate google sitemaps based on Your site MySQL structure.