Populate HTML table with mysql data

[thara]

I am trying to populate HTML table with the data comes from my database. Here I have stored "services" in my table. each service may have multiple images. So when populating the table it should have 3 table cells one for "service name" and second for "discription" and third one for images.

This is my sql query looks like:

$prep_stmt = " SELECT s.id
                    , s.name
                    , s.description
                    , i.image
                    , i.image_path
                FROM  services s
                LEFT JOIN images i ON i.service_id = s.id";

This is how my while look like this:

while ($stmt->fetch()) {        
    $html  = "<tr>\n";  
    $html .= "  <td><input type='checkbox'></td>\n";    
    $html .= "  <td>\n";    
    $html .= "      <a href='' class='name'>{$name}</a>\n";     
    $html .= "  </td>\n";   
    $html .= "  <td class='view_html'>{$description}</td>\n";   
    $html .= "  <td>\n";    
                --- My images should be display here ----
    $html .= "  </td>\n";   
    $html .= "</tr>\n";                 

    //Add output to array
    $output[] = $html;  
}

My problem is How I display multiple images in one table cell? If one service have only one image then I can do it, but it has multiple images then I am not sure how to do it.

Hope somebody may help me out.

Thank you.

[Barand]

You may find it easier to concatenate the image names into a comma-separated string for each service

$prep_stmt = " SELECT s.id
                    , s.name
                    , s.description
                    , i.image
                    , GROUP_CONCAT(i.image_path) as images
                FROM  services s
                LEFT JOIN images i ON i.service_id = s.id
                GROUP BY s.id";

[thara]

Thanks @Barand, how would be the PHP for my table. ?

 

Meanwhile I changed above query to set image path with the images, like this.

SELECT s.id
     , s.name
     , s.description
     , GROUP_CONCAT(i.image_path,i.image) as images
FROM  services s
LEFT JOIN images i ON i.service_id = s.id
GROUP BY s.id

[thara]

One more question. When I use this mysql query, I need to get image ID for each image. Can you tell me how I get image id with each image?

 

This is how I tried it. But its only get one image id.

SELECT s.id
     , s.name
     , s.description
         , i.id AS imageId
     , GROUP_CONCAT(i.image_path,i.image) AS images
FROM  services s
LEFT JOIN images i ON i.service_id = s.id
GROUP BY s.id

The result from above query look like this. 

*************************** 1. row ***************************
         id: 12
       name: dfafdf
description: <p>afdsfdfadf</p>
    imageId: 18
     images: ../upload/services/12_1435570281.jpg,../upload/services/12_1435570270.jpg,../upload/services/12_1435570260.jpg,../upload/services/12_1435570251.jpg
*************************** 2. row ***************************
         id: 13
       name: dfafdfghdhgdhg
description: <p>dsfasfdfdasfadf</p>
    imageId: 20
     images: ../upload/services/13_1435570313.jpg,../upload/services/13_1435570293.jpg
*************************** 3. row ***************************
         id: 14
       name: sadfasfd df awerw dsfa
description: <p>dsfadfadsf dfsfaf dsfds ffgfg r3t retg ntmyrh thfdasa</p>
    imageId: NULL
     images: NULL
3 rows in set (0.06 sec)

[Ch0cu3r]

Either use group_concat again for the image ids

 

Or instead of using group_concat in the query, when you get the results from the query build an array of results, grouping the images into a sub array. Example code

$prep_stmt = " SELECT s.id
                    , s.name
                    , s.description
                    , i.id as image_id
                    , CONCAT(i.image_path, i.image) as image
                FROM  services s
                LEFT JOIN images i ON i.service_id = s.id";

... prepare stmt here ...

// bind results
$stmt->bind_result($id, $name, $description, $image_id, $image);

$services = array();
while ($stmt->fetch())
{
    // store service info in array
    if(!isset($services[$id]))
    {
        $services[$id] = array(
            'name' => $name,
            'description' => $description,
            'images' => array();
        );
    }

    // group the service images into a sub array
    $services[$id]['images'][] = array(
        'id' => $image_id,
        'path' => $image_path
    );
}

// now loop through and display each service
foreach($services as $service_id => $service)
{
    $html  = "<tr>\n";  
    $html .= "  <td><input type='checkbox'></td>\n";    
    $html .= "  <td>\n";    
    $html .= "      <a href='' class='name'>{$service['name']}</a>\n";     
    $html .= "  </td>\n";   
    $html .= "  <td class='view_html'>{$service['description']}</td>\n";   
    $html .= "  <td>\n";   

    // loop image for service
    foreach($service['images'] as $image)
    {
        $html .= 'Image ID: ' . $image['id'] . '<img src="'.$image['path'].'" /><br />';
    }

    $html .= "  </td>\n";   
    $html .= "</tr>\n";                 

    //Add output to array
    $output[] = $html;  
}