Page 1 of 1

table joins

Posted: Fri Oct 14, 2005 4:56 am
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

Posted: Fri Oct 14, 2005 5:39 am
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.

Posted: Fri Oct 14, 2005 5:23 pm
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