Load data from sql to a list in html

Question

I have a list in html that defined as follows

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php include "connect.php"; ?>
<div id="view"> 
        <div id="container">
            <ul>
                <!-- row 01 -->
                <a href="#"><li class="clearfix">
                        <img src="images/modern-castle-kitchen.png" alt="thumb" class="thumbnail">
                        <h2>Full-Room Mansion with Open Kitchen</h2>
                        <p class="desc">Rental located in Pheonix, AZ. 2 bedrooms 1.5 baths. </p>                       
                        <span class="price">S2,650/month</span>
                </li></a>

            </ul>
        </div>
 </div>      

</body>
</html>

Another connect.php file to configure a connection to my database as

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "room_seeker";

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

$sql = "SELECT image, detail, money FROM room";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "image: " . $row["image"]. " - detail: " . $row["detail"]. " "  . "money:". $row["money"]. "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

In my above html file, I just define a fixed data. How can I make a code to get data from my database (room_seeker.sql) and insert to the list in my above html file. Thank you for your help

Update: Based on the suggestion, I update my html file as

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php include "connect.php"; ?>
<div id="view"> 
        <div id="container">
            <ul>
                <!-- Assume sql has five rooms, so each room will be displayed in one list -->
                <a href="#"><li class="clearfix">
                        <img src="images/modern-castle-kitchen.png" alt="thumb" class="thumbnail">
                        <h2><?php echo $row['image']?></h2>
                        <p class="desc"><?php echo $row['detail']?></p>                     
                        <span class="price"><?php echo $row['money']?></span>
                </li></a>

            </ul>
        </div>
 </div>      

</body>
</html>

But the ouput is was wrong (have nothing in page)

Echo the HTML around the $row data points so it formats as expected. — chris85
– chris85, Commented Jul 21, 2016 at 12:31
Could you make it more clear? I assume that I have 5 rooms from the database. How can I list all them to the 5 lists in html? I tried the code but it did not work <h2><?php echo $row['image']?></h2> <p class="desc"><?php echo $row['detail']?></p> <span class="price"><?php echo $row['money']?></span> — user3051460
– user3051460, Commented Jul 21, 2016 at 12:39
Can you update the question with where that code was used and what happened? — chris85
– chris85, Commented Jul 21, 2016 at 12:42

Arina · Accepted Answer · 2016-07-21 12:48:34Z

Try this

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php include "connect.php"; ?>
<?php 
    $sql = "SELECT image, detail, money FROM room";
    $result = mysqli_query($conn, $sql);
?>

<div id="view"> 
        <div id="container">
            <ul>
            <?php
                if (mysqli_num_rows($result) > 0) {
                    while($row = mysqli_fetch_assoc($result)) {
            ?>
                <!-- row 01 -->
                <a href="#"><li class="clearfix">
                        <img src="images/modern-castle-kitchen.png" alt="thumb" class="thumbnail">
                        <h2>Full-Room Mansion with Open Kitchen</h2>
                        <p class="desc">Rental located in Pheonix, AZ. 2 bedrooms 1.5 baths. </p>                       
                        <span class="price">S2,650/month</span>
                </li></a>

                <a href="#"><li class="clearfix">
                        <img src="<?php echo $row["image"]; ?>" alt="thumb" class="thumbnail">
                        <h2>Full-Room Mansion with Open Kitchen</h2>
                        <p class="desc"><?php echo $row["detail"]; ?></p>                       
                        <span class="price"><?php echo $row["money"]; ?></span>
                </li></a>
            <?php } 
            } ?>
            </ul>
        </div>
 </div>      

</body>
</html>

I modified connect.php so it looks like this

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "room_seeker";

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

?>

<div id='view'> ..... </div> <?php mysqli_close($conn); ?> close it after </div> on div id='view'

Pradeep · Accepted Answer · 2016-07-21 12:50:44Z

Try to wrap your html within while loop like this

Note: set ur html css and tag class as per requirement

   $sql = "SELECT image, detail, money FROM room";
   $result = mysqli_query($conn, $sql);

   if (mysqli_num_rows($result) > 0) { ?>

      <ul>
          <?php  while($row = mysqli_fetch_assoc($result)) 
           { ?>

             <!-- row 01 -->
             <a href="#"><li class="clearfix">
             <img src="<?php echo $row["image"];?>" alt="thumb" class="thumbnail">
             <h2><?php echo $row["detail"];?></h2>
             <p class="desc"><?php echo $row["detail"];?></p>                       
             <span class="price"><?php echo $row["money"];?></span> </li>
             </a>

      <? }?>
 </ul>
  <?php
  } else 
    {
      echo "0 results";
  }

Javi González García · Accepted Answer · 2016-07-21 12:53:27Z

0

You only have to put the output of your sql sentence in an array.Later with javascript you can get this array and embeed it on your table.

PHP:

while($row = mysqli_fetch_assoc($result)) {
    $rows[] = $row;
}

Javascript:

var list = <?php echo json_encode($rows);?> 
for (var row in list){...}

answered Jul 21, 2016 at 12:53

Javi González García

411 silver badge6 bronze badges

Collectives™ on Stack Overflow

Load data from sql to a list in html

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related