Populate HTML table with MySQL data (multiple images for a table cell)

Question

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 "description" 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.

first store those values in an array, then use that array for generating the html table — ɹɐqʞɐ zoɹǝɟ
– ɹɐqʞɐ zoɹǝɟ, Commented Jun 29, 2015 at 3:47
@FerozAkbar, Thank you for your comment. But I an not sure how to do it. Can you kindly show me with an example? Thank you. — user3733831
– user3733831, Commented Jun 29, 2015 at 3:52
I'd take out the join. Pull all services then query all images at the HTML creation when needed. As is you'll get multiple rows per ID, which I don't think you want. — chris85
– chris85, Commented Jun 29, 2015 at 3:56
If I understand what you are doing in the database, @chris85 is correct. Using a join here is just going to give you multiple lines for your services for each picture associated with the service. Using 2 queries will allow you to place multiple images in the final column for each service. — Codeguy007
– Codeguy007, Commented Jun 29, 2015 at 3:59

Sameeraa4ever · Accepted Answer · 2015-06-29 05:13:08Z

7

Change your sql code as below and try

 $prep_stmt = " SELECT s.id
                    , s.name
                    , s.description
                    , (select group_concat(i.image_path,'/',i.image)  from images i where i.service_id = s.id) as img
                FROM  services s";

Then use this Html code

    while ($stmt->fetch()) {        
    $html  = "<tr>";  
    $html .= "  <td><input type='checkbox'></td>";    
    $html .= "  <td>";    
    $html .= "     <a href='' class='name'>{$name}</a>";     
    $html .= "  </td>";   
    $html .= "  <td class='view_html'>{$description}</td>";   

    $html .= "  <td>";
    $img_array=explode(",",$img);
    foreach($img_array as $im){
        if($im==''){
         $html .= " <img src='default.jpg'>";   
        }else{
         $html .= " <img src='{$im}'>";   
        }
    }
    $html .= "  </td>";

    $html .= "</tr>";                 

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

edited Jun 29, 2015 at 5:13

answered Jun 29, 2015 at 3:58

Sameeraa4ever

5687 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Codeguy007 Over a year ago

GROUP_CONCAT is cool. I didn't know you could return an array like that.

Sameeraa4ever Over a year ago

Changed sql query. Try again

user3733831 Over a year ago

Can you kindly explain updated mysql query and why it didn't work earlier one?

Sameeraa4ever Over a year ago

Previous sql that I updated; group all images thats why u got one row . But actually u want to get images that are associated with service ID.

user3733831 Over a year ago

@Sameeraa4ever, one more question. Is there way to display a default image if $im is null?

Collectives™ on Stack Overflow

Populate HTML table with MySQL data (multiple images for a table cell)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related