table joins

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

table joins

Post by Jim_Bo »

Hi,

I have 3 tables to hold vehicle data and images


Vehicle photos

Code: Select all

Vehicle manifacturer

CREATE TABLE manufacturer (
  id int(5) NOT NULL auto_increment,  
  manufacturer varchar(155) NOT NULL default '',
  PRIMARY KEY (id)
) TYPE=MyISAM;

Vehicle information

CREATE TABLE cars (
  id int(5) NOT NULL auto_increment,  
  code varchar(25) NOT NULL default '',
  year int(55) NOT NULL default '',
  manufacturer varchar(155) NOT NULL default '',
  model varchar(155) NOT NULL default '',
  mileage int(55) NOT NULL default '',
  engine varchar(55) NOT NULL default '',
  transmission varchar(55) NOT NULL default '',
  fuel varchar(55) NOT NULL default '',
  colour varchar(55) NOT NULL default '',
  price int(55) NOT NULL default '',
  description text NOT NULL default '',  
  PRIMARY KEY (id)
) TYPE=MyISAM;

Vehicle photos

CREATE TABLE car_images (
  photo_id int(20) unsigned NOT NULL auto_increment,
  photo_filename varchar(25) default NULL,
  car_id int(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (photo_id),
) TYPE=MyISAM;
at first I only used Vehicle information & Vehicle photos tables, but I want to put manufacturer into its own table and reference the id with Vehicle information to get the names. I populate a list/menu from the manufacturer table for use when manufacturers are inserted via cms, also for search by manufacturer.

I have a join from originally when I only used the 2 tables to link 6 images to each vehicle

I guess I have to introduce a 3rd join, thats where im stuck:

Code: Select all

$filter = $_REQUEST['manufacturer'];

	$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM cars WHERE manufacturer='$filter'"),0); 
	$total_pages = ceil($total_results / $max_results);
	$sql = "SELECT c.id, c.year, c.manufacturer, c.model, photo_filename
	FROM cars as c
	LEFT JOIN car_images as p ON p.car_id = c.id
	WHERE manufacturer='$filter'
	GROUP BY c.id 
	LIMIT $from, $max_results";
$filter came via post from a list/menu with manufacturer names.

stuck on the third table ..

Thanks
User avatar
thomas777neo
Forum Contributor
Posts: 214
Joined: Mon Mar 10, 2003 6:12 am
Location: Johannesburg,South Africa

Post by thomas777neo »

Rather use this syntax:

Code: Select all

SELECT table1.field,table2.field,table3.field FROM table1,table2,table3 WHERE table1.field = table2.field AND table2.field = table3.field
If your not planning to compare the tables for unique reference differences, then rather use this syntax. It’s also more SQL friendly, if you have to port your system to another db.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

tried with the above syntax and keeped getting jumbled results.

I managed to get it working using:

Code: Select all

$sql = "SELECT c.id, c.year, c.manufacturer, c.model, p.photo_filename, m.make
                       FROM cars as c
                       JOIN manufacturer as m ON m.id = c.manufacturer
                       LEFT JOIN car_images as p ON p.car_id = c.id
                       WHERE m.make='$filter'
                       GROUP BY c.id
                       LIMIT $from, $max_results";
Is that a pluasable way to go about geting the results?

Cheers
Post Reply