Foreach loop to generate HTML table with title in first cell of each row in PHP

By Posted on

Problem

I’ve used quite a lot of foreach loops to achieve this type of HTML table that has titles in the first cell of each row:

 <table>
     <thead>
        <tr>
         <th></th><th><img src='img.jpg'>Product 1</th><th><img src='img.jpg'>Product 2</th>
        </tr>
     </thead>
     <tbody>
        <tr>
          <td>Width</td><td>50mm</td><td>90mm</td>
          <td>Material</td>Plastic<td></td><td>Gold</td>
          <td>Diameter</td>60 mm<td></td><td>70mm</td>
          <td>Battery Hours</td>60 hours<td></td><td>50 hours</td>
        </tr>
     </tbody>
   </table>

Is it necessary to use so many foreach loops? Do you have any other better way of getting this output?

  $sql = "SELECT * FROM product WHERE name IN ('Product 1','Product 2') ";
  if ($wpdb->query($sql))
  {
    $rows = $wpdb->last_result;
    $similar_com = "";
    $similar_com .= "<table><thead><tr><th></th>";

    foreach($rows as $row)
    { 
       $similar_com .= "<img style='width:100px;height:100px' src='".$row->image_url."' alt='".$row->model_no."'>";
    }
    $similar_com .= "</thead><tbody><tr><td>Width</td>";
    foreach($rows as $row)
    { 
       $similar_com .= "<td>$row->width mm</td>";
    }
    $similar_com .= "</tr><td>Material</td>";
    foreach($rows as $row)
    { 
       $similar_com .= "<td>$row->material</td>";
    }
    $similar_com .= "</tr><td>Case diameter</td>";
    foreach($rows as $row)
    { 
       $similar_com .= "<td>$row->case_diameter (".plus($product->case_diameter - $row->case_diameter)." mm)</td>";
    }
    $similar_com .= "</tr><td>Case thickness</td>";

    foreach($rows as $row)
    { 
       $similar_com .= "<td>$row->case_thickness (".plus($product->case_thickness - $row->case_thickness)." mm)</td>";
    }
    $similar_com .= "</tr><td>Case Material</td>";
    foreach($rows as $row)
    { 
       $similar_com .= "<td>$row->case_material</td>";
    }
    $similar_com .= "</tr><td>Battery Hours</td>";
    foreach($rows as $row)
    { 
       $similar_com .= "<td>$row->battery_hours hours</td>";
    }
    $similar_com .= "</tr><td>Special Functions</td>";
    foreach($rows as $row)
    { 
       $similar_com .= "<td>$row->special_functions</td>";
    }

    $similar_com .= "</tr></tbody></table>";
   }

Solution

  • Is it critical that your product be the top row? It seems like you would benefit tremendously if your table was structured like this:

    <table>
    <thead>
        <tr>
            <th>Product</th>
            <th>Width</th>
            <th>Material</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <th><img /></th>
            <td>50mm</td>
            <td>Plastic</td>
        </tr>
    </tbody>
</table>

    because that way, you can just use a single foreach loop:

    foreach ($rows as $row) {
    $similar_com .= "<tr>";
    $similar_com .= "<td><img style='width: 100px; height: 100px;' src='$row->image_url' alt='$row->model_no' /><br />$row->name</td>";
    $similar_com .= "<td>$row->width mm</td>";
    ...
}

  • You’re doing a SELECT *. Avoid that; it’s considered better to explicitly state the columns you’re using.

  • I would rather rename your $rows variable to $products, but I notice that you already have a $product variable. I don’t know what this variable is, but you should consider renaming some things… $similar_com is an especially useless name; I can’t tell what it’s supposed to mean.

  • I noticed you’re using WordPress. The $wpdb->query(); $wpdb->last_result pattern is not as good as simply doing:

    if ($rows = $wpdb->get_results($sql) {

  • If you have to keep your table structure the same, you could have separate variables for each row, then do it all in one foreach:

    $widthRow = "<tr><th>Width</th>";
$materialRow = "<tr><th>Material</th>";
...
foreach ($rows as $row) {
    $widthRow .= "<td>$row->width mm</td>";
    $materialRow .= "<td>$row->material</td>";
    ...
}
$widthRow .= "</tr>";
$materialRow .= "</tr>";

I would take 3 options into account:

  • Floating <div>s next to each other (one foreach & more columns possible).
  • Template (native php) without loop with placeholders as <?php echo
    $rows[0]->img_url; ?>     etc. Need to assume there would be only two
    columns. (+title column).
  • Transpose db rows into columns using nested array (2 element array in
    each main key) within foreach loop: $table['image'][] =
    $row->image_url; $table['width'][] = ...    . Then loop rows (first row
    would be conditional th or outside second loop).

After retrieving $rows as you do already, try:

$new_rows = array( '' => array(), 'Width' => array(), 'Material' => array(), 'Diameter' => array(), array());

foreach ($rows as $row) {
    $new_rows[''][] = array($row->image_url, $row->model_no);
    $new_rows['Width'][] = $row->width;
    $new_rows['Material'][] = $row->material;
    $new_rows['Diameter'][] = $row->diameter;
}

$similar_com = "<table>";

foreach ($new_rows as $key => $new_row) {

    if ($key == '') {
        $similar_com .= "<thead>";
    }

    $similar_com .= "<tr><th>" . $key . "</th>";
    foreach ($new_row as $inner_key => $item) {
        if ($key == '') {
            $similar_com .= "<th><img style='width:100px;height:100px' src='" . $item[0] . "' alt='" . $item[1] . "'>Product " . $inner_key . "</th>;
        }
        else {
            $similar_com .= "<td>" . $item;
            if ($key == "Width" || $key == "Diameter") {
                $similar_com .= " mm";
            }
            $similar_com .= "</td>
        }
    }

    $similar_com .= "</tr>";
    if ($key == '') {
        $similar_com .= "</thead>";
    }
}

$similar_com .= "</table>";

echo $similar_com;

I haven’t included all your fields but it should be fairly obvious how to add them (I think).

Whilst writing this I thought of a way to do it with just 2 rather than 3 foreach loops but it makes the code a lot less readable and reuseable – let me know if you want it and I’ll post it as another answer though 🙂

Explanation:

The $rows variable returns something of the form:

$rows = array(
    [0] => array('field1_val', 'field2_val', ...),    //first result
    [1] => array(...),    //second result
    ...    //and so on for however many RECORDS there are
);

But looping through this returns one record at a time, whereas we want a field at a time – so the data has to be rearranged into an array like this:

$new_rows = array(
    ['field1'] => array('result1_val', 'result2_val', ...),    //each record's value for the first field
    ['field2'] => array(...),    //each record's value for the second field
    ...    //and so on for however many FIELDS there are
);

So now the size of the outer array is the number of fields and the inner arrays are each the same size as the number of records returned – effectively we have turned the original $rows variable inside out. Looping through this array using foreach gives an array of the field values each time.
That is the purpose of the first foreach loop in my code (to generate an array of the data of this form) – I also decided to group the first 2 fields into one “row” since they both want to appear on the table in the same row.

The next foreach loop starts the table – this is where having used field names as the keys of the array becomes useful; you just print out the key to get the field name rather than using something like:

switch ($key) {
    case 0:
        $similar_com .= "";
        break;
    case 1:
        $similar_com .= "Width";
    ...    //etc
}

Then inside that loop you need a 3rd foreach loop that will loop through each record’s value for that field and print it out.

Hope that is clearer now.

Leave a Reply

Your email address will not be published. Required fields are marked *