Skip to content
Advertisement

How to build HTML element based on MySQL results

Problem I am constructing an HTML email to send which includes products in our database.

I have all the moving parts, I believe, to complete the task but just not sure how to piece it together.

The HTML template

$template = file_get_contents("notify.html");

Template Tags

{{TITLE}}
{{PRICE}}
{{LOCATION}}
{{MILES}}

The element I want to loop

NOTE: This first element is inside the template. I can remove it if we need to, though.

$div = <<<EOF
<!-- START LOOP HERE -->
<div style="background-color:transparent;">
      <div class="block-grid four-up no-stack" style="Margin: 0 auto; min-width: 320px; max-width: 650px; overflow-wrap: break-word; word-wrap: break-word; word-break: break-word; background-color: #FFFFFF;">
    <div style="border-collapse: collapse;display: table;width: 100%;background-color:#FFFFFF;"> 
          <!--[if (mso)|(IE)]><table width="100%" cellpadding="0" cellspacing="0" border="0" style="background-color:transparent;"><tr><td align="center"><table cellpadding="0" cellspacing="0" border="0" style="width:650px"><tr class="layout-full-width" style="background-color:#FFFFFF"><![endif]--> 
          <!--[if (mso)|(IE)]><td align="center" width="162" style="background-color:#FFFFFF;width:162px; border-top: 0px solid transparent; border-left: 0px solid transparent; border-bottom: 0px solid transparent; border-right: 0px solid transparent;" valign="top"><table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-right: 0px; padding-left: 0px; padding-top:5px; padding-bottom:5px;"><![endif]-->
          <div class="col num3" style="max-width: 320px; min-width: 162px; display: table-cell; vertical-align: top; width: 162px;">
        <div style="width:100% !important;"> 
              <!--[if (!mso)&(!IE)]><!-->
              <div style="border-top:0px solid transparent; border-left:0px solid transparent; border-bottom:0px solid transparent; border-right:0px solid transparent; padding-top:5px; padding-bottom:5px; padding-right: 0px; padding-left: 0px;"> 
            <!--<![endif]-->
            <div align="center" class="img-container center fixedwidth" style="padding-right: 0px;padding-left: 0px;"> 
                  <!--[if mso]><table width="100%" cellpadding="0" cellspacing="0" border="0"><tr style="line-height:0px"><td style="padding-right: 0px;padding-left: 0px;" align="center"><![endif]--><img align="center" alt="Image" border="0" class="center fixedwidth" src="images/2.png" style="text-decoration: none; -ms-interpolation-mode: bicubic; border: 0; height: auto; width: 100%; max-width: 130px; display: block;" title="Image" width="130"/> 
                  <!--[if mso]></td></tr></table><![endif]--> 
                </div>
            <!--[if (!mso)&(!IE)]><!--> 
          </div>
              <!--<![endif]--> 
            </div>
      </div>
          <!--[if (mso)|(IE)]></td></tr></table><![endif]--> 
          <!--[if (mso)|(IE)]></td><td align="center" width="162" style="background-color:#FFFFFF;width:162px; border-top: 0px solid transparent; border-left: 0px solid transparent; border-bottom: 0px solid transparent; border-right: 1px dotted #E8E8E8;" valign="top"><table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-right: 0px; padding-left: 0px; padding-top:30px; padding-bottom:35px;"><![endif]-->
          <div class="col num3" style="max-width: 320px; min-width: 162px; display: table-cell; vertical-align: top; width: 161px;">
        <div style="width:100% !important;"> 
              <!--[if (!mso)&(!IE)]><!-->
              <div style="border-top:0px solid transparent; border-left:0px solid transparent; border-bottom:0px solid transparent; border-right:1px dotted #E8E8E8; padding-top:30px; padding-bottom:35px; padding-right: 0px; padding-left: 0px;"> 
            <!--<![endif]--> 
            <!--[if mso]><table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-right: 10px; padding-left: 0px; padding-top: 10px; padding-bottom: 5px; font-family: Tahoma, Verdana, sans-serif"><![endif]-->
            <div style="color:#555555;font-family:Lato, Tahoma, Verdana, Segoe, sans-serif;line-height:1.2;padding-top:10px;padding-right:10px;padding-bottom:5px;padding-left:0px;">
                  <div style="font-size: 12px; line-height: 1.2; font-family: Lato, Tahoma, Verdana, Segoe, sans-serif; color: #555555; mso-line-height-alt: 14px;">
                <p style="font-size: 16px; line-height: 1.2; text-align: left; font-family: Lato, Tahoma, Verdana, Segoe, sans-serif; word-break: break-word; mso-line-height-alt: 19px; margin: 0;"><span style="font-size: 16px; color: #2190e3;"><strong>{{TITLE}}</strong></span></p>
              </div>
                </div>
            <!--[if mso]></td></tr></table><![endif]--> 
            <!--[if mso]><table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-right: 10px; padding-left: 0px; padding-top: 0px; padding-bottom: 10px; font-family: Tahoma, Verdana, sans-serif"><![endif]-->
            <div style="color:#555555;font-family:Lato, Tahoma, Verdana, Segoe, sans-serif;line-height:1.2;padding-top:0px;padding-right:10px;padding-bottom:10px;padding-left:0px;">
                  <div style="font-size: 12px; line-height: 1.2; font-family: Lato, Tahoma, Verdana, Segoe, sans-serif; color: #555555; mso-line-height-alt: 14px;">
                <p style="font-size: 14px; line-height: 1.2; text-align: left; font-family: Lato, Tahoma, Verdana, Segoe, sans-serif; word-break: break-word; mso-line-height-alt: 17px; margin: 0;">{{MILES}}</p>
              </div>
                </div>
            <!--[if mso]></td></tr></table><![endif]--> 
            <!--[if (!mso)&(!IE)]><!--> 
          </div>
              <!--<![endif]--> 
            </div>
      </div>
          <!--[if (mso)|(IE)]></td></tr></table><![endif]--> 
          <!--[if (mso)|(IE)]></td><td align="center" width="162" style="background-color:#FFFFFF;width:162px; border-top: 0px solid transparent; border-left: 0px solid transparent; border-bottom: 0px solid transparent; border-right: 1px dotted #E8E8E8;" valign="top"><table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-right: 0px; padding-left: 0px; padding-top:55px; padding-bottom:5px;"><![endif]-->
          <div class="col num3" style="max-width: 320px; min-width: 162px; display: table-cell; vertical-align: top; width: 161px;">
        <div style="width:100% !important;"> 
              <!--[if (!mso)&(!IE)]><!-->
              <div style="border-top:0px solid transparent; border-left:0px solid transparent; border-bottom:0px solid transparent; border-right:1px dotted #E8E8E8; padding-top:55px; padding-bottom:5px; padding-right: 0px; padding-left: 0px;"> 
            <!--<![endif]--> 
            <!--[if mso]><table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-right: 10px; padding-left: 10px; padding-top: 0px; padding-bottom: 10px; font-family: Tahoma, Verdana, sans-serif"><![endif]-->
            <div style="color:#555555;font-family:Lato, Tahoma, Verdana, Segoe, sans-serif;line-height:1.2;padding-top:0px;padding-right:10px;padding-bottom:10px;padding-left:10px;">
                  <div style="font-size: 12px; line-height: 1.2; font-family: Lato, Tahoma, Verdana, Segoe, sans-serif; color: #555555; mso-line-height-alt: 14px;">
                <p style="font-size: 20px; line-height: 1.2; text-align: center; font-family: Lato, Tahoma, Verdana, Segoe, sans-serif; word-break: break-word; mso-line-height-alt: 24px; margin: 0;"><span style="font-size: 20px;"><strong>{{LOCATION}}</strong></span></p>
              </div>
                </div>
            <!--[if mso]></td></tr></table><![endif]-->
            <table border="0" cellpadding="0" cellspacing="0" class="divider" role="presentation" style="table-layout: fixed; vertical-align: top; border-spacing: 0; border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; min-width: 100%; -ms-text-size-adjust: 100%; -webkit-text-size-adjust: 100%;" valign="top" width="100%">
                  <tbody>
                <tr style="vertical-align: top;" valign="top">
                      <td class="divider_inner" style="word-break: break-word; vertical-align: top; min-width: 100%; -ms-text-size-adjust: 100%; -webkit-text-size-adjust: 100%; padding-top: 10px; padding-right: 10px; padding-bottom: 10px; padding-left: 10px;" valign="top"><table align="center" border="0" cellpadding="0" cellspacing="0" class="divider_content" height="30" role="presentation" style="table-layout: fixed; vertical-align: top; border-spacing: 0; border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; border-top: 0px solid transparent; height: 30px; width: 100%;" valign="top" width="100%">
                          <tbody>
                          <tr style="vertical-align: top;" valign="top">
                              <td height="30" style="word-break: break-word; vertical-align: top; -ms-text-size-adjust: 100%; -webkit-text-size-adjust: 100%;" valign="top"><span></span></td>
                            </tr>
                        </tbody>
                        </table></td>
                    </tr>
              </tbody>
                </table>
            <!--[if (!mso)&(!IE)]><!--> 
          </div>
              <!--<![endif]--> 
            </div>
      </div>
          <!--[if (mso)|(IE)]></td></tr></table><![endif]--> 
          <!--[if (mso)|(IE)]></td><td align="center" width="162" style="background-color:#FFFFFF;width:162px; border-top: 0px solid transparent; border-left: 0px solid transparent; border-bottom: 0px solid transparent; border-right: 0px solid transparent;" valign="top"><table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-right: 0px; padding-left: 0px; padding-top:55px; padding-bottom:5px;"><![endif]-->
          <div class="col num3" style="max-width: 320px; min-width: 162px; display: table-cell; vertical-align: top; width: 162px;">
        <div style="width:100% !important;"> 
              <!--[if (!mso)&(!IE)]><!-->
              <div style="border-top:0px solid transparent; border-left:0px solid transparent; border-bottom:0px solid transparent; border-right:0px solid transparent; padding-top:55px; padding-bottom:5px; padding-right: 0px; padding-left: 0px;"> 
            <!--<![endif]--> 
            <!--[if mso]><table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-right: 15px; padding-left: 0px; padding-top: 0px; padding-bottom: 0px; font-family: Tahoma, Verdana, sans-serif"><![endif]-->
            <div style="color:#555555;font-family:Lato, Tahoma, Verdana, Segoe, sans-serif;line-height:1.2;padding-top:0px;padding-right:15px;padding-bottom:0px;padding-left:0px;">
                  <div style="line-height: 1.2; font-size: 12px; font-family: Lato, Tahoma, Verdana, Segoe, sans-serif; color: #555555; mso-line-height-alt: 14px;">
                <p style="line-height: 1.2; text-align: center; font-size: 20px; font-family: Lato, Tahoma, Verdana, Segoe, sans-serif; word-break: break-word; mso-line-height-alt: 24px; margin: 0;"><span style="font-size: 20px;"><span style="font-size: 20px;"><strong>{{PRICE}}</strong></span></span></p>
              </div>
                </div>
            <!--[if mso]></td></tr></table><![endif]--> 
            <!--[if (!mso)&(!IE)]><!--> 
          </div>
              <!--<![endif]--> 
            </div>
      </div>
          <!--[if (mso)|(IE)]></td></tr></table><![endif]--> 
          <!--[if (mso)|(IE)]></td></tr></table></td></tr></table><![endif]--> 
        </div>
  </div>
</div>
EOF;

A Simple template system But I believe this will work on only one row (right?)

foreach($variables as $key => $value)
{
    $template = str_replace('{{ '.$key.' }}', $value, $template);
}

MySQL Results

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * from";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    // $row['title']
    // $row['price']
    // $row['location']
    // $row['miles']

  }
} else {
  echo "0 results";
}

So…

Given all that, how would I construct the HTML to loop over the results and include that DIV as many times as there are results?

Thank you for looking.

Ps. I did research this topic on SO , and found similar questions but none quite matched my criteria. Thanks again!

Advertisement

Answer

I believe something like that would work:

<?php

$template = file_get_contents("notify.html");
$html = "";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) 
{
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * from PRODUCT";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) 
{
    // Add template into HTML with replaced variable for each row
    while($row = mysqli_fetch_assoc($result)) 
    {
        $tmp_template = $template;
        foreach($row as $key => $value)
        {
            $tmp_template = str_replace('{{ '.$key.' }}', $value, $template);
        }
        $html .= $tmp_template;
    }

    echo $html;
}
else 
{
    echo "0 results";
}

The only thing I added is the tmp_template variable. This variable is a copy of the original template where you will replace the variable from your query into it. After replacing all the variable with their value, you will at this tmp_template variable into a html variable. This html variable will be your final result that you will send by email. You could add a header before the loop and a footer after the loop into your html variable for the email.

PLEASE NOTE: I did not test this code, but it should be very close to what you need.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement