table joins
Posted: Fri Oct 14, 2005 4:56 am
Hi,
I have 3 tables to hold vehicle data and images
Vehicle photos
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:
$filter came via post from a list/menu with manufacturer names.
stuck on the third table ..
Thanks
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;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";stuck on the third table ..
Thanks