PHP, MySQL: Need to show Parent name only once.. almost there but still a problem

Question

Hi all,

I have a parent category that holds all Cars names, denoted by parent_name in table "parent". For each of these parents, there could be any number of car models & they all go in table called "model". Each of these models can have any number of images & refereced via the model_id as the Foreign Key. My task is to show all the Parent Name only once (as in a group) and at the same time, list all the models under that Parent with just 1 corresponding image. The parent_name should not be shown more than once.

MY EXPERIMENTS:

I basically tried to write 2 queries. One was to left join "parent" table on "models" & use GROUP BY parent_id and then in the while loop, write another query to fetch only 1 image using by joining the models & images tables by using model_id field. But doing this lists only 1 Model, even though there are multiple models. So I tried to use GROUP BY parent_id, model_id. Using this does show all the models but at the same time, also repeats showing the parent_name & I need the parent_name to show only once throughout the page. You can say that I am trying to GROUP the model_name under the parent & show all the models under a single parent and I am showing only 1 image of the model. If somehow I can avoid showing the parent_name multiple times, the issue would be solved.

Following are my table schemas:

//Table parent

parent_id   parent_name
    1        Par1
    2       Par2


//Table model

model_id    parent_id   model_name
    1            1       Model1
    2           2       Model2
    3           1       Model3
    4           1       Model4
    5            2       Model5

//Table model_images

image_id    model_id
    1            1
    2           1
    3           1
    4            2
    5           3
    6           3    

DESIRED OUTPUT:

Par1    ---> This is the parent. Needs to be shown only once.
Model1  -->  This is a model. List all models that belong to this parent. 
image_id 1 -> Show only 1 image of the model (model may have multiple images but I need just one) 

Model3  -->  This is a model.
image_id 5  -> Show only 1 image of the model    

Model4  -->  This is a model.
No Image    -> Note that no image exists for this model. So we show "No Image" text.

------------------------------------------------------------

Par2    ---> This is the parent. Needs to be shown only once.
Model2  -->  This is a model.
image_id 4  -> Show only 1 image of the model

Model5  -->  This is a model.
No Image   -> Note that no image exists for this model. So we show "No Image" text.

I need the PHP & mySQL code to achieve the above. All help in resolving the issue is appreciated.

Thank you very much.

EDIT 1: Sorry, I forgot to add this. I am non-object oriented programmer. So I would really be thankful if you can avoid object oriented code in your solution and show me the same in a non-oops way. Thanks.

Answer 1

You might do it in one query and than combine it to an associative array:

$query = '  SELECT     *
            FROM       parent AS p
            LEFT JOIN  model AS m
            ON         p.id = m.parent_id
            LEFT JOIN  model_images AS m_i
            ON         m.model_id = m_i.model_id';

$array = array();

if($mysli->query($quer)){
    while($row = $result->fetch_assoc()){
        $array[$row['parent_name']][$row['model_id']] = $row;
    }
}

You will than have an associative array with the parent name as the key of the array. You can then use a for loop to print the key only once (with $i = 0) but the rest value by value.

Is that clear enough?

EDIT: Your array than might look like this:

Array(
  'Par 1' => 
    Array(
      [0] => Array(
        'parent_id' => 1,
        'parent_name' => 'Par 1',
        'model_id' => 1,
        'model_name' => 'Model 1',
        'image_id',
      ),
      [1] => Array(...)
    ),
    'Par 2' => Array(...)
)

So to print out you need two loops. One for the parents (and there names) and one for their childs (models in this case).

foreach($array as $par_name => $models){
    echo 'Parent name: '.$par_name.'<br />';
    echo 'Model ID: '.$models[0]['model_id'].', Model Name: '.$models[0]['name']; // replace with your desired output
}

Now an idea of how it works? An sure as Artefacto said, you can use procedural functions if you don't like OOP funtions.

Answer 2

@Kau-Boy I am trying to retain the code formatting & hence I am posting this as a comment. I am using the following code:

$array = array();


while($row = mysql_fetch_assoc($res) )
{
        $array[$row['parent_name']][] = $row;

        echo ($array[$row['parent_name']]).'<br />';
}

This gives me the following Output:

Array
Array
Array
Array
Array
Array
Array
Array
Array
Array
Array

Could you please tell me how to fix this? Thank you.

EDIT 1: @Kau-Boy I modified the code as per your latest edit & here is my code:

$array = array();

while($row = mysql_fetch_assoc($res) )
{
    $array[$row['parent_name']][] = $row;

    foreach($array as $par_name => $models)
    {
        foreach($models as $model)
        {
            echo ($model).'<br />'; // replace with your desired output
        }
    }   
}

This again gives me the following Output:

Array
Array
Array
Array
Array
Array
Array
Array
Array
Array
Array

If I change

$array[$row['parent_name']][] = $row;

to

$array[$row['parent_name']] = $row;,

it shows me all the columns fetched, but I have no control on the column names i.e I cannot show them in the order that I wanted to. What am I doing wrong?