php to mysql data retrieval

Question

I am trying to retrieve data from mysql database that has 4 rows. I need to send this as an table in email using php. I have built a script for this, but the issue is the script is not emailing all the rows, its just emailing the last row or if i pass a specific parameter with "where" in db query.

Any help from anyone is appreciated. Thank you in advance.

Attached the db output and php code as well.

<?php

date_default_timezone_set('America/Los_Angeles');
$today = date("j-F-Y g:i:s a");                 // March 10, 2001, 5:16 pm


// DB Connect.
$db_host = 'localhost'; // Server Name
$db_user = 'root'; // Username
$db_pass = 'test123#'; // Password
$db_name = 'util'; // Database Name

 $conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
        if (!$conn) {
                die ('Failed to connect to MySQL: ' . mysqli_connect_error());
        }

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

        if (!$result) {
                die ('SQL Error: ' . mysqli_error($conn));
        }

        if(mysqli_num_rows($result) > 0){
                            while($row = mysqli_fetch_array($result)){
                                $File_system = $row[0];
                                $IP = $row[1];
                                $Capacity = $row[2];
                                $Available = $row[3];
                                $Used = $row[4];
                                $Percentage = $row[5];

                # Compare Percentage and alert.
                $subject = "Critical | FH NetApp-NetBackup Space Utilization..!";
                    $message1 = "
                    <html>
                        <body>
                            <p> Hi Team,<br><br>  The Server utilization is <b style='color:red'> critical</b>. Please find the below utilization details.</p>

                                                        <table>
                                <tr>
                                        <th> File_System </th>
                                        <th> IP </th>
                                        <th> Total_capacity </th>
                                        <th> Available_Capacity </th>
                                        <th> Used_Capacity </th>
                                        <th> Percentage </th>
                                </tr>
                                <tr>
                                        <td> $File_system </td>
                                        <td> $IP </td>
                                        <td> $Capacity </td>
                                        <td> $Available </td>
                                        <td> $Used </td>
                                        <td> $Percentage </td>
                                </tr>
                            </table>

                            <p style='font-size:15px'> Data generated at:<b> $today EST.</b><p>
                            <p>Regards, <br>
                               Backup Team. </p>
                        </body>
                </html>";
                    $headers[] = 'From: [email protected]'; // Sender's Email
                    $headers[] = 'Cc: [email protected]'; // Carbon copy to Sender
                    $headers[] = 'MIME-Version: 1.0 charset=".$encoding."';
                    $headers[] = 'Content-type: text/html; charset=iso-8859-1';
                    // Message lines should not exceed 70 characters (PHP rule), so wrap it
                    $message = wordwrap($message1, 70);

                    // Send Mail By PHP Mail Function
                    mail($to, $subject, $message, implode("\r\n", $headers));
        }
}
?>

enter image description here

I have been trying to shuffle around the loop in the code, it did not help.

Do a var_dump on your $result variable before the loop. What's actually in there? i.e var_dump($result); — Elroy Jetson
– Elroy Jetson, Commented May 3, 2019 at 13:26
@ElroyJetson Below is the output i get once i added var_dump. ``` object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(6) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) } ``` — SKowshik92
– SKowshik92, Commented May 3, 2019 at 13:56

stubben · Accepted Answer · 2019-05-07 08:46:11Z

Try and rearrange your code a little bit. First create the 'top' of the html, then go through all the results and append them to the html and then finish the html.

I haven't tested the code beneath but it should work :-)

<?php

date_default_timezone_set('America/Los_Angeles');
$today = date("j-F-Y g:i:s a"); // March 10, 2001, 5:16 pm


// DB Connect.
$db_host = 'localhost'; // Server Name
$db_user = 'root'; // Username
$db_pass = 'test123#'; // Password
$db_name = 'util'; // Database Name

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
    die('Failed to connect to MySQL: ' . mysqli_connect_error());
}

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

if (!$result) {
    die('SQL Error: ' . mysqli_error($conn));
}

if (mysqli_num_rows($result) > 0) {
    $subject = "Critical | FH NetApp-NetBackup Space Utilization..!";
    $message1 = "
          <html>
              <body>
                  <p> Hi Team,<br><br>  The Server utilization is <b style='color:red'> critical</b>. Please find the below utilization details.</p>

                                              <table>
                      <tr>
                              <th> File_System </th>
                              <th> IP </th>
                              <th> Total_capacity </th>
                              <th> Available_Capacity </th>
                              <th> Used_Capacity </th>
                              <th> Percentage </th>
                      </tr>";
    while ($row = mysqli_fetch_array($result)) {
        $File_system = $row[0];
        $IP          = $row[1];
        $Capacity    = $row[2];
        $Available   = $row[3];
        $Used        = $row[4];
        $Percentage  = $row[5];

        # Compare Percentage and alert.
        $message1 .= "
                                <tr>
                                        <td> $File_system </td>
                                        <td> $IP </td>
                                        <td> $Capacity </td>
                                        <td> $Available </td>
                                        <td> $Used </td>
                                        <td> $Percentage </td>
                                </tr>";
    }
    $message1 .= "</table>";
    $message1 .= "<p style='font-size:15px'> Data generated at:<b> $today EST.</b><p>
                                <p>Regards, <br>
                                   Backup Team. </p>
                            </body>
                    </html>";

    $headers[] = 'From: [email protected]'; // Sender's Email
    $headers[] = 'Cc: [email protected]'; // Carbon copy to Sender
    $headers[] = 'MIME-Version: 1.0 charset=".$encoding."';
    $headers[] = 'Content-type: text/html; charset=iso-8859-1';
    // Message lines should not exceed 70 characters (PHP rule), so wrap it
    $message   = wordwrap($message1, 70);

    // Send Mail By PHP Mail Function
    mail($to, $subject, $message, implode("\r\n", $headers));

}
?>

About conditional formatting

No worries. You can try and use nested ternaries (shorthand for if/else... to keep it short :-))

Example snippet from above code:

...
$Percentage  = $row[5];
$color = ($Percentage > 90) ? '#FCE901' : (($Percentage > 85) ? '#FF0000' : '#00E526');
# Compare Percentage and alert.
$message1 .= "
                        <tr>
                                <td> $File_system </td>
                                <td> $IP </td>
                                <td> $Capacity </td>
                                <td> $Available </td>
                                <td> $Used </td>
                                <td style=\"background-color:$color\"> $Percentage </td>
                        </tr>";

Sorry to get this here. I am trying to do a conditional formatting on $Percentage on the table when i send email. Any suggestion on how to achieve that? I have tried with javascript did not go well, i tried inline css did go well either.

Elroy Jetson · Accepted Answer · 2019-05-03 14:00:27Z

0

The issue is somewhere on your database end. Based on your var_dump output:

object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(6) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) }

You only have 1 result being returned. You should start there. Maybe you're not referencing the table you think you are. Who knows right now, but you php looks fine. Investigate your DB

answered May 3, 2019 at 14:00

Elroy Jetson

9761 gold badge12 silver badges28 bronze badges

Comments

Tin Bum · Accepted Answer · 2019-05-03 14:43:45Z

Your PHP is not fine stubben gave you the answer

<?php

date_default_timezone_set('America/Los_Angeles');
$today = date("j-F-Y g:i:s a");                 // March 10, 2001, 5:16 pm


// DB Connect.
$db_host = 'localhost'; // Server Name
$db_user = 'root'; // Username
$db_pass = 'test123#'; // Password
$db_name = 'util'; // Database Name

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) 
{
    die ('Failed to connect to MySQL: ' . mysqli_connect_error());
}

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

if (!$result) 
{
    die ('SQL Error: ' . mysqli_error($conn));
}

if(mysqli_num_rows($result) > 0)
{
    while($row = mysqli_fetch_array($result))
    {
        $File_system = $row[0];
        $IP = $row[1];
        $Capacity = $row[2];
        $Available = $row[3];
        $Used = $row[4];
        $Percentage = $row[5];

Above You have a Percentage from 1 Row of Data Below you use that to decide the eMail subject - only 1 subject per email - so 1 row of data per email

        # Compare Percentage and alert.
        if($Percentage > 90)
        {
            $subject = "Critical | FH NetApp-NetBackup Space Utilization..!";
            $message1 = "
                <html>
                    <head>
                        <style>
                            body,h6 {
                                font-family: Segoe UI,Helvetica,courier;
                            }
                            th {
                                border: 1px solid black;
                                border-collapse: collapse;
                                background-color: #04D1E8;
                                padding: 10px;
                                text-align: center;
                            }
                            td {
                                border: 1px solid black;
                                border-collapse: collapse;
                                padding: 10px;
                                text-align: center;
                            }
                            .cri {
                                background-color: #FF0000;
                            }
                        </style>
                    </head>

                    <body>
                        <p> Hi Team,<br><br>  The Server utilization is <b style='color:red'> critical</b>. Please find the below utilization details.</p>
                        <table>
                            <tr>
                                <th> File_System </th>
                                <th> IP </th>
                                <th> Total_capacity </th>
                                <th> Available_Capacity </th>
                                <th> Used_Capacity </th>
                                <th> Percentage </th>
                            </tr>
                            <tr>
                                <td> $File_system </td>
                                <td> $IP </td>
                                <td> $Capacity </td>
                                <td> $Available </td>
                                <td> $Used </td>
                                <td class='cri'> $Percentage </td>
                            </tr>
                        </table>

                        <p style='font-size:15px'> Data generated at:<b> $today EST.</b><p>
                        <p>
                            Regards, <br>
                            Backup Team. 
                        </p>
                    </body>
                </html>
            ";

            $headers[] = 'From: [email protected]'; // Sender's Email
            $headers[] = 'Cc: [email protected]'; // Carbon copy to Sender
            $headers[] = 'MIME-Version: 1.0 charset=".$encoding."';
            $headers[] = 'Content-type: text/html; charset=iso-8859-1';
            // Message lines should not exceed 70 characters (PHP rule), so wrap it
            $message = wordwrap($message1, 70);

            // Send Mail By PHP Mail Function
            mail($to, $subject, $message, implode("\r\n", $headers));
        } 
        else if($Percentage > 85)
        {
            $subject = "Warning | FH NetApp-NetBackup Space Utilization..!";
            $message1 = "
                <html>
                    <head>
                        <style>
                            body,h6{
                                font-family: Segoe UI,Helvetica,courier;
                            }
                            th {
                                border: 1px solid black;
                                border-collapse: collapse;
                                background-color: #04D1E8;
                                padding: 10px;
                                text-align: center;
                            }
                            td {
                                border: 1px solid black;
                                border-collapse: collapse;
                                padding: 10px;
                                text-align: center;
                            }
                            .war {
                                background-color: #FCE901;
                            }
                        </style>
                    </head>

                    <body>
                        <p> Hi Team,<br><br>  The Server utilization is <b style='color:yellow'> above normal</b>. Please find the below utilization details.</p>
                        <table>
                            <tr>
                                <th> File_System </th>
                                <th> IP </th>
                                <th> Total_capacity </th>
                                <th> Available_Capacity </th>
                                <th> Used_Capacity </th>
                                <th> Percentage </th>
                            </tr>
                            <tr>
                                <td> $File_system </td>
                                <td> $IP </td>
                                <td> $Capacity </td>
                                <td> $Available </td>
                                <td> $Used </td>
                                <td class='war'> $Percentage </td>
                            </tr>
                        </table>
                        <p style='font-size:15px'> Data generated at:<b> $today EST.</b></p>
                        <p>
                            Regards, <br>
                            Backup Team. 
                        </p>
                    </body>
                </html>
            ";

            $headers[] = 'From: [email protected]'; // Sender's Email
            $headers[] = 'Cc: [email protected]'; // Carbon copy to Sender
            $headers[] = 'MIME-Version: 1.0 charset=".$encoding."';
            $headers[] = 'Content-type: text/html; charset=iso-8859-1';
            // Message lines should not exceed 70 characters (PHP rule), so wrap it
            $message = wordwrap($message1, 70);

            // Send Mail By PHP Mail Function
            mail($to, $subject, $message, implode("\r\n", $headers));
        } 
        else 
        {
            $subject = "Normal | FH NetApp-NetBackup Space Utilization..!";
            $message1 = "
                <html>
                    <head>
                        <style>
                            body,h6{
                                font-family: Segoe UI,Helvetica,courier;
                            }
                            th {
                                border: 1px solid black;
                                border-collapse: collapse;
                                background-color: #04D1E8;
                                padding: 10px;
                                text-align: center;
                            }
                            td {
                                border: 1px solid black;
                                border-collapse: collapse;
                                padding: 10px;
                                text-align: center;
                            }
                            .nor {
                                background-color: #00E526;
                            }
                        </style>
                    </head>

                    <body>
                        <p> Hi Team,<br><br> The Server utilization is <b style='color:green'> normal. </b>Please find the below utilization details.</p>
                        <table>
                            <tr>
                                <th> File_System </th>
                                <th> IP </th>
                                <th> Total_capacity </th>
                                <th> Available_Capacity </th>
                                <th> Used_Capacity </th>
                                <th> Percentage </th>
                            </tr>
                            <tr>
                                <td> $File_system </td>
                                <td> $IP </td>
                                <td> $Capacity </td>
                                <td> $Available </td>
                                <td> $Used </td>
                                <td class='nor'> $Percentage </td>
                            </tr>
                        </table>

                        <p style='font-size:15px'> Data generated at:<b> $today EST.</b></p>
                        <p>
                            Regards, <br>
                            Backup Team. 
                        </p>
                    </body>
                </html>
            ";
            $headers[] = 'From: [email protected]'; // Sender's Email
            $headers[] = 'Cc: [email protected]'; // Carbon copy to Sender
            $headers[] = 'MIME-Version: 1.0 charset=".$encoding."';
            $headers[] = 'Content-type: text/html; charset=iso-8859-1';

            // Message lines should not exceed 70 characters (PHP rule), so wrap it
            $message = wordwrap($message1, 70);

            // Send Mail By PHP Mail Function
            mail($to, $subject, $message, implode("\r\n", $headers));
        }
    }
}
?>

The size of his query is only 1 before he does anything based on his var_dump. How does $percentage have anything to do with this. My assumption of course is that (s)he put the var_dump immediately after the query lol.
@Elroy Jetson I'm not saying his/her data is Ok just saying the PHP isn't - it is structured to produce a single email from a single data record. As stubben said needs to write headers and subject line etc first then a series of table rows for each data record, but the percentage field determines the email subject - as code is currently written :-)

Collectives™ on Stack Overflow

php to mysql data retrieval

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related