Need help with php mysql query results

[TiwstedNy]

I need help with a query result. I have one database with 3 tables which are categories, pictures and vehicles. I need to do a query to select each vehicle along with its assocated pictures. Now the problem I have been running into is that since a vehicle can have up to 12 photos I get 12 different rows for the same vehicle in my query.

 

This is my query. I also tried the JOIN statment but it produces the same results.

 

$query  = "SELECT * FROM vehicles, pictures WHERE pictures.veh_id = vehicles.veh_id AND vehicles._sold = 'STOCKED' ORDER BY vehicles._make ASC";

 

Now the problem with this is lets say I have an AUDI RS8 in the database and it has 7 photos assoicated with it. When i run this query i get 7 diffrent rows for the AUDI RS8 because it has 7 photos. I kust need each vehicle in the database to populate once BUT with all its assoicate pics.

 

Any help with be greatly appericated. Thank you

[ginerjm]

First this is not a problem with query results, but with a query design. You want to join x number of records to just one record which is a pretty tough task. Tell me, are you just going to take the query results and generate a web page? If so, you can handle the 7 records as you loop thru them, keeping track of when you reach a new product and displaying the 'header' info for that product, but only showing the image name/image as you hit each successive record. Does that make sense?

[dalecosp]

I'd even go farther and say it's a problem with database design, or application design overall.  But nothing's every quite perfect, is it?

ginerjm makes some sense ... so you have too many rows ... just show the other info once and each photo in turn.

Another thought, which might be expensive, is to do one query for the car data and a second query for all the photos; put them in an array and handle this array in your PHP logic.  You generally would prefer to not hit the DB multiple times, but if you can't change the design of the data, you can change the design of the queries.

[TiwstedNy]

I am using it to populate a text file that will be used for a datafeed. dalecosp the problem with doing two queries is that the datafeed must be alphabeticalize by the vehicle make and my picture table only has veh_id to assicate which picture belongs to which vehicle.  How would I make the vehicles and the images correspond with each other using two queries and make sure the right images line up with the right vehicle.

[Psycho]

Um, I have to completely disagree with dalecosp. The "issue" you are having has nothing to do with a bad database design or with the query at all. When you have a one-to-many relationship in your database between tables and you do a normal JOIN query between those two table you will always get your results like that. There will be multiple records for each child element so the parent values will be duplicated. For example, here is what the results might look like when doing a query on a States table joining a Cities table:

state | city
 CA      Los Angeles
 CA      San Diego
 CA      Burbank
 CA      Anaheim
 TX      Austin
 TX      Dallas
 TX      San Antonio

This is exactly how the results should be returned. The trick is in knowing how to process those results - not in changing the results. There are many ways to achieve what you want. One option is to create a "flag" variable that is used to check when the parent value is changed. Another is to dump the results into a multidimensional array. The process you should take will be dependent upon exactly what you are doing. I typically use the flag variable method, but when the logic for creating the output becomes more difficult I may rely upon creating an output array first.

 

Here is an example script for illustrative purposes that should get you pointed in the right direction

//Get the DB results
$query  = "SELECT *
           FROM vehicles
           JOIN pictures ON pictures.veh_id = vehicles.veh_id
           WHERE vehicles._sold = 'STOCKED'
           ORDER BY vehicles._make ASC";
$result = mysql_quer($query);

//Process the DB results
$current_vehicle = false; //Flag variable
while($row = mysql_fetch_assoc($result))
{
    //If different vehicle from last record, display vehicle info
    if($current_vehicle != $row['vehicle_id'])
    {
        //Display vehicle name only for first record
        echo "<b>{$row['vehicle_name']}</b><br>\n";
        //Set flag
        $current_vehicle = $row['vehicle_id'];
    }

    //Display each vehicle picture
    echo "<img src=\"$row['image_src']\" />\n";
}

EDIT: Note that I used a normal (i.e. INNER) JOIN in that query. If it is possible for vehicles to exist without any associated pictures, then you should use a LEFT JOIN

 

EDIT#2: Just in case someone responds with the suggestion of using GROUP_CONCAT. That MySQL function will allow you to get one record for each vehicle with all the associated images for the vehicle as a single concatenated value. I specifically did not suggest that because it doesn't scale well and, I believe, has a limitation on the total string length (Verified that the default is 1024 characters. It can be increased, but is a server-side configuration which you might not have access to change on a shared host and increasing it can have some negative consequences). Depending on how much of the file path you are including in the images that could be a problem for vehicles for many pictures. But, if your picture values will not be excessively long and vehicles will not have large number of pictures it might be an easier solution.

Edited June 24, 2013 by Psycho

[ginerjm]

As Psycho says - it's in the process, not the query. His method is the way to go, as I also suggested.