How to echo a MySQLi row in escaped PHP code, inside an HTML block
I'm wondering what is the appropriate syntax is to echo a row from MySQLi code in a block of HTML text. I'm working on a page that uses PHP code at the start to determine if a session is started and to post comments that user has posted, after pulling said comments from a MySQLi database. The interesting and confusing part is, I've accomplished what I'm trying to do in one HTML div, but I can't seem to get it to work in the next.
<?php
$page_title = "store";
require_once('connectvars.php');
require_once('startsession.php');
require_once('header.php');
// Connect to the DB
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
// Grab location data from the DB
// Grab post data
$query2 = "SELECT sp.post_id, sp.admin_id, sp.post, sp.date, sa.admin_id, s.store_name, s.store_city, s.store_state, s.store_zip,s.store_phone, s.store_email, s.store_address FROM store_posts sp, store_admin sa, stores s WHERE sp.admin_id='$admin_id' and sa.admin_id='$admin_id' and s.admin_id='$admin_id'ORDER BY date DESC";
$data = mysqli_query($dbc, $query2);
if (mysqli_num_rows($data) == 0) {
$blankwall = "Empty feed? '<a href=manage.php><u>Click here</u></a> to manage posts and communicate with your customers!";
}
?>
<body>
<div id="content">
<!-- BANNER & CONTENT-->
<h2>Recent Posts</h2>
<div id="store_wrap">
<div id="left_col">
<br />
<?php
**// Loop through posts and show them
if (mysqli_num_rows($data) == 0) {
echo $blankwall;
}
else {
while ($row = mysqli_fetch_array($data)) {
// Show the posts
echo '' . $row['post'] . ' | ';
echo date('M j, Y g:i A', strtotime($row['date']));
echo '<br /><hr />';
}**
}
?>
</div><!-- closes left_col -->
So all the above code is there to query the DB to grab the correct array and then show $row['posts'] in the HTML div below the PHP code, titled left_col. I am trying to do the exact same thing in the next div but instead of echoing $row['posts'], I want to echo rows such as $row['store_city'] to have the page display the store's location after pulling it out of the previously selected array. Here's my non-functioning code for that part:
<div id="right_col">
<div id="store_picture">
<img src="images/store.jpg" style="width:325px;"/>
</div><!-- closes picture --><br />
<div id="store_address">
<br /><br /><h2>Location Info</h2>
<?php
if (mysqli_num_rows($data) == 1) {
while ($row = mysqli_fetch_array($data)) {
echo '<p>' . $row['store_city']. '</p>';
}
}
mysqli_close($dbc);
?>
</div><!-- closes store_address -->
<div id="store_info">
<p></p>
</div><!-- closes store_info -->
</div><!-- closes right_col -->
<div class="clear"></div>
</div><!-- closes store_wrap -->
</div><!-- closes content -->
For whatever reason, the second time, when I try to echo data from that array, I just have empty space within that div. I don't get any errors. I just don't get...anything. Therefore, I think this is a syntax issue. I've tried exactly the same thing I did with the section where I echo $row['post'] and it isn't working.
The chief issue you're facing is that you make a second attempt at fetching rows from the $data MySQLi result resource object after already having fetched them once. This won't work as intended, as MySQL keeps an internal recordset pointer which advances every time mysqli_fetch_array() is called. So when the end of the first loop is reached, the pointer is at the end of the recordset and a subsequent call will return FALSE .
Therefore, your second loop gets nowhere because its first call to mysqli_fetch_array() returns FALSE , exiting the loop. You have two options.
The quickest fix is to just rewind the record pointer using mysqli_data_seek() . Called right before the second loop, it will set the pointer back to the first record, allowing you to fetch them all again.
if (mysqli_num_rows($data) == 1) {
// Rewind the record pointer back to the first record
mysqli_data_seek($data, 0);
while ($row = mysqli_fetch_array($data)) {
// note addition of htmlspecialchars() to escape the string for HTML!
echo '<p>' .htmlspecialchars($row['store_city']). '</p>';
}
}
Perhaps a better option if your recordset is small is to fetch all rows into an array once, then use that array with a foreach loop for both your subsequent output loops:
// To hold all rows
$rows = array();
// Do the query
$data = mysqli_query($dbc, $query2);
// (don't forget to check for errors)
if ($data) {
while ($row = mysqli_fetch_array($data)) {
// Append the row onto the $rows array
$rows[] = $row;
}
}
else{
// handle the error somehow...
}
Later, instead of using $data again, you will loop over $rows with a foreach loop.
// use count($rows)
if (count($rows) == 0) {
echo $blankwall;
}
else {
foreach ($rows as $row) {
// Show the posts
echo '' . $row['post'] . ' | ';
echo date('M j, Y g:i A', strtotime($row['date']));
echo '<br /><hr />';
}
}
...And do the same thing again for your second loop later in the code. It's recommended to use htmlspecialchars() on the string fields output from the query where appropriate.
上一篇: PHP if语句在html中