Best way to join multiple tables

[sheeksav]

I am new to php/mysql so please forgive my lack of experience. I am wondering what is the best way method to join 3 or more tables for data to be output on a web page.

 

My web server is running MySQL client version: 5.0.9.

 

I have been successful in joining two tables that look like this:

 

CREATE TABLE beers (
beer_id INT NOT NULL AUTO_INCREMENT,
brewery_id INT NOT NULL,
name VARCHAR (125),
PRIMARY KEY (beer_id));

CREATE TABLE breweries (
brewery_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR (150);
PRIMARY KEY (brewery_id));

 

I successfully joined the two tables using:

 

Select * FROM beers NATURAL JOIN breweries

 

For output on my webpage I used:

 

<? 
				$query = "SELECT * FROM beers NATURAL JOIN breweries ORDER BY name";
				$result = mysql_query($query);
				$num = mysql_num_rows($result);

				mysql_close();


				$i = 0;
				while ($i < $num) {

					$beer = mysql_result($result, $i, "name");
					$brewery = mysql_result($result, $i, "brewery_name");



					?>
                          
                         <div class="post">
                                                  	                                   
                            <div class="info">
                        		<h2><? echo $beer ?></h2>
                                <h3><? echo $brewery ?></h3>
                                 <br />
                                           	                      
                        		</div>
                                
                                <br class="clear" />
                                
                        </div><!--post-->
                        
				<?php
				$i++;
				}								
			?>

 

 

 

 

 

 

What I would like to do next is add a third table which will be created like this:

 

CREATE TABLE categories (
cat_id INT NOT NULL AUTO_INCREMENT,
cat_name VARCAR (125),
PRIMARY KEY (cat_id));

 

And I would like to add to my existing table "beers" a new field called "cat_id".

 

My ultimate objective is to join three tables in a manner so that the field "brewery_id" from table "breweries" , as well as the field "cat_id" from the table "categories", are referenced into the fields of the same name in table "beers". The three tables I am wishing to join now have the following structure:

 

CREATE TABLE beers (
beer_id INT NOT NULL AUTO_INCREMENT,
brewery_id INT NOT NULL,
cat_id INT NOT NULL,
name VARCHAR (125),
PRIMARY KEY (beer_id));

CREATE TABLE breweries (
brewery_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR (150);
PRIMARY KEY (brewery_id));

CREATE TABLE categories (
cat_id INT NOT NULL AUTO_INCREMENT,
cat_name VARCAR (125),
PRIMARY KEY (cat_id));

 

And I wish to output it to my web page using the following:

 

<? 
				$query = "SELECT * FROM beers NATURAL JOIN breweries ORDER BY name";
				$result = mysql_query($query);
				$num = mysql_num_rows($result);

				mysql_close();

				$i = 0;
				while ($i < $num) {

					$beer = mysql_result($result, $i, "name");
					$brewery = mysql_result($result, $i, "brewery_name");
					$category = mysql_result($result, $i "cat_name"); 				

					?>

                        	
                       <div class="post">
                            
                                	                                   
                            <div class="info">
                        		<h2><? echo $beer ?></h2>
                                <h3><? echo $brewery ?></h3>
                                 <br />
                                Category: <? echo $category ?> ;
                                           	                      
                        		</div>
                                
                                <br class="clear" />
                                
                        </div><!--post-->
                        
				<?php
				$i++;
				}								
			?>

 

 

Any help on this issue is greatly appreciated. I am looking for as specific a solution as possible, but please remember I am still a newbie! Much Thanks in advance!

[TOA]

Have you tried anything?

 

Like adding another join onto your first attempt? 

 

 

[fenway]

Yeah, you need joins.  And mysql_result() is crazy slow.