PHP not printing database as table

Question

I am attempting to print out mySQL database into a html table. I have watched many tutorials on how to do this but am unsure as to how I refer to the html table in my php code. The information gets printed fine and connects to the database but for some reason it isn't output in the table format.

<?php
    $conn = mysqli_connect('localhost', 'Admin', 'admin1', 'info');
    if (!$conn) {
        echo "Connection failed:" . mysqli_connect_error();
    }
    //Writing query for database.
    $sql = "SELECT `First Name`,`Last Name`,Emails,`Date Created` FROM clientinfo ORDER BY `Date Created`";

    //Querying and getting results
    $result = mysqli_query($conn, $sql);

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            echo "<tr><td>" . $row["First Name"] . "</td></tr>" . $row["Last Name"] . "</td></tr>"
                . $row["Emails"] . "</td></tr>" . $row["Date Created"] . "</td></tr>";
        }
        echo "</table>";
    } else {
        echo "0 result";
    }

    //Fetch resulting rows as an array
    $informed = mysqli_fetch_all($result, MYSQLI_ASSOC);

    // Freeing result from the memory.
    mysqli_free_result($result);

    mysqli_close($conn);
?>

<!DOCTYPE html>
<html lang="en-US">
    <head>
        <div class="Contained">
            <div class="row">
                <?php foreach ($informed as $inform) { ?>
                    <div class="col s6 medium-3">
                        <div class="card z-depth-0">
                            <div class="card-content center">
                                <h6><?php echo htmlspecialchars($inform['First Name']); ?></h6>
                                <div><?php echo htmlspecialchars($inform['Last Name']); ?></div>
                            </div>
                            <div class="card-action right-align">
                                <a class="brand-text" href="#">More Info
                            </div>
                        </div>
                    </div>
                <?php } ?>
            </div>
        </div>
        <title> Email and Name List </title>
    </head>
    <body>
        <table>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Emails</th>
                <th>Date Created</th>
            </tr>
        </table>
    </body>
</html>

Output in browser gyazo:

loop your data in between ``
– Yash Karanke Jan 17 '20 at 12:19 — Yash Karanke, Jan 17 '20 at 12:19

Simone Rossaini · Accepted Answer · 2020-01-17T12:17:27.330

1

You must change code for output all table in php like:

<body>
<?php


$conn = mysqli_connect('localhost', 'Admin', 'admin1', 'info');

if (!$conn){
echo "Connection failed:" . mysqli_connect_error();
}
//Writing query for database.
$sql = "SELECT `First Name`,`Last Name`,Emails,`Date Created` FROM clientinfo ORDER BY `Date 
Created`";

//Querying and getting results

$result = mysqli_query($conn,$sql);

if ($result->num_rows>0){
echo '
<table>
<tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Emails</th>
    <th>Date Created</th>
</tr>';
    while($row = $result->fetch_assoc()){
        echo "<tr> ";
        echo "<td>" . $row["First Name"] . "</td>";
        echo "<td>" . $row["Last Name"] . "</td>";
        echo "<td>" . $row["Date Created"] . "</td>";
        echo "</tr> ";
        }

    echo"</table>";
}
else{
    echo "0 result";
}

//Fetch resulting rows as an array

$informed = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Freeing result from the memory.

mysqli_free_result($result);

mysqli_close($conn);

?>

</body>

Another question are you sure is $row["First Name"] and not $row["First_Name"]?
Last tip learn how prepare stm for prevent sql inject

edited Jan 17 '20 at 12:17

answered Jan 17 '20 at 09:51

Simone Rossaini

8,115
1
13
34

This outputs the data but again it does not do it in the table format. All the data is output under the column First_Name see: https://gyazo.com/9e409ad09464dde49b9154cf1997a200 could this be a problem with my sql database? I thought I had set it up correctly – cola465 Jan 17 '20 at 12:15
@SimoneRossaini Can you point out exactly how the code is vulnerable to SQL injection when there are no parameters in the query? – symcbean Jan 17 '20 at 12:22
1

You forgot the 'Emails' row in your echo loop but yep all works perfect! Thanks man this is gonna help me so much. – cola465 Jan 17 '20 at 12:25
@symcbean see [here](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) all reference – Simone Rossaini Jan 17 '20 at 12:31
@SimoneRossaini No. I asked where, in the code presented by the OP, is there a SQLI vulnerability - which was intended as a polite way of pointing out that you are providing misleading and irrelevant information in your answer. – symcbean Jan 17 '20 at 12:40

score 0 · Answer 2 · edited Jan 17 '20 at 11:46

Based on your code, you are trying to print the table before the actual table is defined below. You can try something like this:

<?php

    $conn = mysqli_connect('localhost', 'Admin', 'admin1', 'info');

    if (!$conn){
        echo "Connection failed:" . mysqli_connect_error();
    }

    //Writing query for database.
    $sql = "SELECT `First Name`,`Last Name`,Emails,`Date Created` FROM clientinfo ORDER BY `Date Created`";

    //Querying and getting results

    $result = mysqli_query($conn,$sql);

    $informed = mysqli_fetch_all($result, MYSQLI_ASSOC); 
?>
<!DOCTYPE html>
<html lang="en-US">
<head>
<div class="Contained">
<div class="row">

    <?php foreach($informed as $inform){?>
        <div class="col s6 medium-3">

            <div class="card z-depth-0">
                <div class="card-content center">
                    <h6><?php echo htmlspecialchars($inform['First Name']); ?></h6>
                    <div><?php echo htmlspecialchars($inform['Last Name']);?></div>
                </div>
                <div class="card-action right-align">
                <a class="brand-text" href="#">More Info</div>

            </div>

        </div>
    <?php }?>

</div>
</div>

<title> Email and Name List </title>
</head>
<body>
    <table>
        <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Emails</th>
            <th>Date Created</th>
        </tr>
        <?php 
              if ($result->num_rows>0) {
                  while($row = $result->fetch_assoc()){
                      echo "<tr><td>" . $row["First Name"] . "</td><td>" .  $row["Last Name"] . "</td><td>" . $row["Emails"] . "</td><td>" . $row["Date Created"] . "</td></tr>";
                  }
              } else {
                  echo "<tr><td rowspan=\"5\">0 result</td></tr>";
              }
        ?>
    </table>
</body>

<?php 
    // Freeing result from the memory.
    mysqli_free_result($result);
    mysqli_close($conn);
?>

PHP not printing database as table

2 Answers2