whats wrong with this join statement?

[jbrill]

hey

just looking for some quick help with this join statement

 

thnx

 

$sql = mysql_query("SELECT make.id, make.make FROM dynamic_vehicles, dynamic_makes WHERE vehicles.id = make.id");
while($make = mysql_fetch_array($sql))

{

	echo '<li><a href="#" title="'.$make['make'].'">'.$make['make'].'</a></li> ';
}

[Caberman]

You need to alias the tables. Try this:

 

$sql = mysql_query("SELECT make.id, make.make FROM dynamic_vehicles AS vehicles, dynamic_makes AS make WHERE vehicles.id = make.id");

 

Cheers.

[jbrill]

PERFECT! thanks!

[jbrill]

actually, one more thing...

 

here is the code right now:

$sql = mysql_query("SELECT make.id, make.make,vehicles.active FROM dynamic_vehicles AS vehicles, dynamic_makes AS make WHERE vehicles.id = make.id ORDER BY make.make ASC");
while($make = mysql_fetch_array($sql))

{
	if($make['active']==1)
		{
			echo '<li><a href="#" title="'.$make['make'].'">'.$make['make'].'</a></li> ';
		}
}

 

 

I have a field in the "dynamic_vehicles" table called "active" if active is "1" then i want it to show the models that are active and not the other ones.

I tried adding "AND vehicles.active='1' " but it did not work

[Caberman]

Could you post your new WHERE clause? I presume it looks like:

 

WHERE vehicles.id = make.id AND vehicles.active = '1'

 

Also, what error do you get? Also, I usually like to run the queries directly against the DB to make sure you are able to get results and aren't confusing queries that return no results with a PHP problem.

 

Cheers.

[jbrill]

here is the code i jsut ran in phpmyadmin

 

SELECT make.id, make.make,vehicles.active FROM dynamic_vehicles AS vehicles, dynamic_makes AS make WHERE vehicles.id = make.id AND vehicles.active='1' ORDER BY make.make ASC

 

this is showing all the models in the "models" table i need to only show the model IF a vehicle in the "vehicles" table is active=1

[Caberman]

Neither of your tables are 'models'. Do you mean Make?

[jbrill]

sorry i meant "make"

[treehugger88]

Swap to using the other table name.

 

SELECT make.id, make.make,vehicles.active FROM dynamic_vehicles AS vehicles, dynamic_makes AS make WHERE vehicles.id = make.id AND MAKE.active='1' ORDER BY make.make ASC

[jbrill]

noope, now im getting an sql error:

 

MySQL said:

#1054 - Unknown column 'make.active' in 'where clause'

[treehugger88]

huh. then there isnt a field names active in the dynamic_makes table and you did want to check vehicles.active for sure.

 

Caberman should have told you to use a join the right way. try this

 

SELECT make.id, make.make,vehicles.active

FROM dynamic_vehicles AS vehicles, dynamic_makes AS make

INNER JOIN make ON vehicles.id=make.id

WHERE vehicles.active='1'

ORDER BY make.make ASC

 

not sure... does that work better?

[jbrill]

nope, that didnt work either, any other suggestions guys?

[BlueSkyIS]

what "didn't work?" can we see the updated code?

[jbrill]

Well basically I need the to check against dynamic_vehicles  and only show the "model" from the dynamic_models table if the vehicle is set to active=1

 

here is the code so far:

$sql = mysql_query("SELECT make.id, make.make,vehicles.active FROM dynamic_vehicles AS vehicles, dynamic_makes AS make WHERE vehicles.id = make.id AND vehicles.active='1' ORDER BY make.make ASC");
while($make = mysql_fetch_array($sql))	
	{	
		echo '<li><a href="#" title="'.$make['make'].'">'.$make['make'].'</a></li> ';		
	}

[sasa]

can you post structure of your tables

[BlueSkyIS]

i suggest that you check for SQL errors using  mysql_query($sql) or die(mysql_error())

[jbrill]

this is the error im getting: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dynamica/public_html/test/includes/header.php on line 27

 

here is the tables structure:

 

dynamic_vehicles:

id,date_added,cat,make,model,year,vin,odometer,color,interior_color,motor,transmission,drive,doors,passengers,description,warranty,options,price,stock_num,active,sold,featured

 

dynamic_make:

id, make

 

[BlueSkyIS]

i suggest that you check for SQL errors using  mysql_query($sql) or die(mysql_error())

[jbrill]

k i check it and got this: Unknown column 'make.id' in 'field list'

[BlueSkyIS]

so now you know your SQL is wrong.

[jbrill]

ok, so i got some models being displayed, but they are not the proper ones that are set to active=1

 

here is my code

$sql = mysql_query("SELECT make.id, make.make,vehicles.active FROM dynamic_vehicles AS vehicles, dynamic_makes AS make WHERE  vehicles.active='1' AND vehicles.id = make.id GROUP BY vehicles.make ORDER BY make.make ASC") or die(mysql_error());
while($make = mysql_fetch_array($sql))

{
	if($make['active']==1) {
	echo '<li><a href="#" title="'.$make['make'].'">'.$make['make'].'</a></li>';
		}
}