Help with INNER JOINS

[alexmark]

Hi all

 

I am new to php and I need to write an inner join to get a form to do what I would like. The problem is I am not sure how to write it. I have 3 tables as follows;

 

manufacturer 2 columns id (which is unique) and manufacturer

 

year 2 columns id (which is unique) and year

 

model 4 columns id (which is unique) year (which corresponds to year id table) manufacturer_id (which corresponds to manufactures id table) and model.

 

I am pulling info by the following code.

 

<tr> 
<td align="right" label for='formyear'>Year: </td> 
<td><select name="formyear"> 
<?php  
$sql="SELECT id,year FROM year"; 
$result =mysql_query($sql); 
while ($data=mysql_fetch_assoc($result)){ 
?> 
<option value ="<?php echo $data['id'] ?>" ><?php echo $data['year'] ?></option> 
<?php } ?> 
</select></td> 
</tr> 
<tr> 
<td align="right" label for='formmake'>Make: </td> 
<td><select name="formmake" id="formmake" onchange="return populateModel(this.value);"> 
<?php  
$sql="SELECT id,manufacturer FROM manufacturer"; 
$result =mysql_query($sql); 
while ($data=mysql_fetch_assoc($result)){ 
?> 
<option value ="<?php echo $data['id'] ?>" ><?php echo $data['manufacturer'] ?></option> 
<?php } ?> 
</select> 
</td> 
</tr> 
<tr> 
<td align="right" label for='formmodel'>Model: </td> 
<td><select name="formmodel" id="formmodel"> 
<?php  
$sql="SELECT id,manufacturer_id,model FROM model WHERE year='1'"; 
$result =mysql_query($sql); 
while ($data=mysql_fetch_assoc($result)){ 
?> 
<option value ="<?php echo $data['id'] ?>" ><?php echo $data['model'] ?></option> 
                                <?php } ?> 

                                </select> 

 

What I would like to happen is that some one selects a year and then a make and then a model.

 

I have been told this can be done via inner joins. How do I write them to work so I can still pull info for theselect list as above?

 

Any help would be trully gratfull as I am at a complete loss here.

[alexmark]

Ok so here is what i have tried and it will not work. As I said before I am new to this and need pointing in teh right direction.

 

<tr>
			<td align="right" label for='formmodel'>Model: </td>
			<td>
                                    <select name="formmodel" id="formmodel">
                                    <?php 
								$query = "SELECT model.model,year.Id,manufacturer.Id, model.manufacturer_id, model.year".
								"FROM model,year,manufacturer". 
	 "INNER JOIN year ON model.year_id = year.Id 
	 INNER JOIN manufacturer ON model.manufacturer_id = manufacturer.id
	 WHERE manufacturer.id = model.manufacturer_id";
  
$result = mysql_query($query) or die(mysql_error());
  
                    
                                       
                                        while($data = mysql_fetch_array($result))
                                    ?>
                                        <option value ="<?php echo $data['id'] ?>" ><?php echo $data['model'] ?></option>
                                    <?php } ?>

[Barand]

Your query will result in two columns with the name "Id" (year.Id and manufacturer.Id). You need to use aliases for those columns.

 

$data['id'] would need to be $data['Id'] - case sensitive - but which Id does it refer to (see comment above)?

 

As you list model in the select, it should be the id of the respective model.