[RESOLVED] Trying to add a related cars code using PHP/SQL

[Trav-Pro]

$sq = "SELECT * FROM bns_cars WHERE id = (".$_GET['id'].")";
$dat = mysql_query( $sq );  
$nfo = mysql_fetch_assoc( $dat );
$stype = ($nfo['type']);
$sql = "SELECT m.id, m.type, a.path, a.file_name, a.record_id FROM bns_cars m, bns_cars_images a WHERE m.id = a.record_id GROUP BY m.type HAVING m.type = (".$stype.")" ;
 $data = mysql_query( $sql )  
 or die(mysql_error());  
 while($info = mysql_fetch_array( $data )) {
   echo "<a href=http://xxx.com/index.php?module=cars&id={$info['id']}><img src=http://xxx.com/uploads/cars/{$info['path']}/{$info['file_name']} width=170px height=113px /></a>" ; 
 } 

I've two tables , bns_cars and bns_cars_images

Tables : bns_cars
ID : 360 | type : 24
ID : 321 | type : 24
ID : 363 | type : 24
ID : 320 | type : 33

Tables : bns_cars_images
record_id : 360 | path: 2012/10/10 | file_name : example1.jpg
record_id : 360 | path: 2012/10/10 | file_name : example2.jpg
record_id : 360 | path: 2012/10/10 | file_name : example3.jpg
record_id : 360 | path: 2012/10/10 | file_name : example4.jpg
record_id : 321 | path: 2012/10/10 | file_name : example5.jpg
record_id : 363 | path: 2012/10/10 | file_name : example6.jpg
record_id : 320 | path: 2012/10/10 | file_name : example7.jpg

---

My SQL query is working but this is not what i actually want it to do.

My website link will appear like this : http://xxx.com/index.php?module=cars&id=360 ( ID is different of every car )

So i want the SQL to check the type of the current car by using the ID from the URL and then search all types with the type we just got using the current URL and show only one image as echo from each type by comparing the id and record_id.

If i wasn't able to express myself well then i would like to show my desired output

Let's say the type we got is "24" so the output will be

<a href=http://xxx.com/index.php?module=cars&id=360><img src=http://xxx.com/uploads/cars/2012/10/10/example1.jpg width=170px height=113px /></a>
<a href=http://xxx.com/index.php?module=cars&id=321><img src=http://xxx.com/uploads/cars/2012/10/10/example5.jpg width=170px height=113px /></a>
<a href=http://xxx.com/index.php?module=cars&id=363><img src=http://xxx.com/uploads/cars/2012/10/10/example6.jpg width=170px height=113px /></a>

I also understand i am using mysql instead of mysqli and PDO but for this moment i would like to know how this is done by using my mysql and later i will convert it

[penagate]

I assume the URL will contain the type number and not the car ID. (You've given 'id' in your example URL, but I think you actually want to search by type.)

If the type is given:

select img.* from bns_cars car left join bns_cars_images img on img.record_id=car.id where car.type=24 group by img.record_id

Note this will only work in MySQL, in case you're concerned with compatibility with other systems. The 'group by' clause introduces an ambiguity (which record of each group do you expect to be returned?) which MySQL resolves by returning the first record (of each group) by default.

[penagate]

Also, I strongly suggest you get out of this habit as soon as possible:

$sq = "SELECT * FROM bns_cars WHERE id = (".$_GET['id'].")";
$dat = mysql_query( $sq ); 

This is what's called an SQL injection vulnerability. Even if you're not planning to publish this code on the Web, it's a habit you should not form. Packages like PDO make avoiding this vulnerability trivial (by using prepared statements with parameters), which is why they were suggested to you in response to your other question.

[Trav-Pro]

I assume the URL will contain the type number and not the car ID. (You've given 'id' in your example URL, but I think you actually want to search by type.)

If the type is given:

select img.* from bns_cars car left join bns_cars_images img on img.record_id=car.id where car.type=24 group by img.record_id

The URL actually contains the ID of posted car and then i have to use this ID and search into bns_cars to retrieve the type of this current ID, So once i get this type, I actually wanted the query to now search again in the bns_cars using this type and retrieving all the IDs related to the type we just retrieved and then by comparing the IDs to record_id of bns_cars_images, I should now be able to use the fields ID [Of bns_cars to use in my <a href tag] , PATH and FILE_NAME in the HTML code i posted above

[Trav-Pro]

$sq = "SELECT * FROM bns_cars WHERE id = (".$_GET['id'].")";
$dat = mysql_query( $sq );  
$nfo = mysql_fetch_assoc( $dat );
$stype = ($nfo['type']); 

See, This is the part where i store the retrieved type using the current URL id in a variable and then use this variable in the sql query, I'm not quite sure at this time if this approach of mine really makes any sense.

I tried your query but it returned with something unexpected, It just displays the current car again as a picture with a wrong url ID.

Please help

[Trav-Pro]

I've FIXED everything on my own already, Thanks alot for coming up and giving some examples and explanations ... RATED !!! @ penagate

[penagate]

The URL actually contains the ID of posted car and then i have to use this ID and search into bns_cars to retrieve the type of this current ID

OK, I shouldn't have assumed!

select img.* from bns_cars car left join bns_cars_images img on img.record_id=car.id where car.type=(select type from bns_cars where id=360) group by img.record_id;

Glad you came up with a solution anyway.

[penagate]

Another one with no subquery:

select img.* from bns_cars_images img join bns_cars car1 on car1.id=img.record_id join bns_cars car2 on car1.type=car2.type where car2.id=360 group by img.record_id;

[Trav-Pro]

I actually only wanted one picture of each car and the other table bns_cars_images contained a lot of them so i did some minor changes in my INSERT query by adding a main_image field in the first table bns_cars so now i don't really need to connect with the second table to get the image and the rest was so simple to do but your examples were great and i actually started reading about the "Left" "Join" commands lol as before i never had to use them so never was aware of it.