Jump to content

Need help with my MySQL statement - Trying to Join 3 Tables


jbrill

Recommended Posts

Hey guys,

It's been a while since ive been on here but i'm having troubles with a Mysql Join statment I just can't seem to figure out...

 

I have 3 tables invloved here:

1) dynamica_makes ->this table holds the id and make name of the vehicle eg: (1,Honda)

2) dynamica_models -> this table holds the id, an id of the the corresponding make and the model name. eg:(5,1,Civic)

3) dynamica_inventory -> this table holds the complete line of inventory the dealership is selling. it has the following fields: id, make(holds the appropriate make id), model (holds the appropriate model id), year, stock_number, price.

 

now, the problem im running into is i can join two tables (for example, dynamica_makes and dynamica_inventory) so that it shows the make's actual name rather than the id stored in the dynamica_inventory table's column for "make" but i cannot get it to do the same and also join the dynamica_models table.

 

here is the current code:

 

$sqlhead = "SELECT 
						dynamic_vehicles.id, 
						dynamic_vehicles.model, 
						dynamic_vehicles.make, 
						dynamic_vehicles.trim, 
						dynamic_vehicles.year, 
						dynamic_vehicles.vin, 
						dynamic_vehicles.odometer, 
						dynamic_vehicles.color, 
						dynamic_vehicles.interior_color, 
						dynamic_vehicles.motor, 
						dynamic_vehicles.transmission, 
						dynamic_vehicles.drive, 
						dynamic_vehicles.doors, 
						dynamic_vehicles.price,
						dynamic_vehicles.stock_num,
						dynamic_vehicles.sold,

						";

	$sqljoin = "dynamic_makes.make AS makename FROM dynamic_vehicles JOIN dynamic_makes ON dynamic_vehicles.make = dynamic_makes.id ";
	$sqlwhere = " WHERE dynamic_vehicles.make = dynamic_makes.id";
	$sqlitem = " ORDER BY makename ASC";

$prodsql = $sqlhead.$sqljoin.$sqlwhere.$sqlitem;							
$prodresult = mysql_query($prodsql) or die(mysql_error());

	while($prod = mysql_fetch_array($prodresult))
	{
	echo $prod['makename'] ." ";
	echo $prod['model'] ."<br />"; //need to get the modelname here to work and display the ACTUAL model name of the vehicle not the id

	}


 

.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.