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