I am creating a dynamic PHP website where just about everything on the site, including navigation is generated dynamically. I am creating a test page (test.php), as I am new to PHP, and am having some issues when doing multiple queries plus a prepared statement as I have a search form on the page. Basically want I am doing, is in the SQL, creating a query for top level navigation items where parent = 0, and also a prepared statement searching for items in another table based on the search. The two queries are unrelated. In my body, where I have the navigation displaying, I want to nest another query to then search for navigation items with a parent that equals the current ID of the top level menu item. This is the SQL at the top of my document:
try {
//db connection is here
$sql1 = 'SELECT * FROM menu_items WHERE item_parent = 0';
$result1 = $db->query($sql1);
if (isset($_GET['search'])) {
$sqlSearch = "SELECT id, css_body, page_title, meta_description, meta_keywords, heading, details, layout
FROM content
WHERE ( details LIKE ? OR heading LIKE ? )";
$stmt = $db->stmt_init();
if (!$stmt->prepare($sqlSearch)) {
$error = $stmt->error;
} else {
$var1 = '%' . $_GET['searchterm'] . '%';
$stmt->bind_param('ss', $var1, $var1);
$stmt->execute();
$stmt->bind_result($id, $css_body, $page_title, $meta_description, $meta_keywords, $heading, $details, $layout);
}
}
} catch (Exception $e) {
$error = $e->getMessage();
}
In my body:
<ul>
<?php while ($row = $result1->fetch_assoc()) { ?>
<li><?php echo $row["item_title"]; ?>
<?php $sql2 = 'SELECT * FROM menu_items WHERE item_parent = ' . $row["item_id"];
$result2 = $db->query($sql2);
if (isset($result2)) { ?>
<ul>
<?php while ($row2 = $result2->fetch_assoc()) { ?>
<li><?php echo $row2["item_title"]; ?></li>
<?php } ?>
</ul>
<?php } ?>
</li>
<?php } ?>
</ul>
With that code, if I just go straight to the test.php page, without doing a search, everything displays fine. For example, the page would look like:
<ul>
<li>Page 1</li>
<ul>
<li>Sub Page 1</li>
<li>Sub Page 2</li>
</ul>
<li>Page 2</li>
</ul>
However if I then submit the search form on the page (which submits to the same page, url would look like test.php?searchterm=my+search&search=Go), then I get an error. The page will display the first LI, but then when it hits that second query it just completely stops. So it looks like:
<ul>
<li>Page 1</li>
If I removed everything for the prepared statement, the "sub-navigation"/nested query works fine. If I removed the SQL/php for the menu_items, the prepared statement works fine. The issue is when I put everything together on one page. If anyone could explain what I may be doing wrong, I would greatly appreciate it. I'm new to prepared statements and object oriented PHP. I learned all of the above after watching Accessing Databases with Object-Oriented PHP with David Powers on Lynda.com.
Here is the entire page code:
<?php
try {
require_once '_includes/mysqli-connect.php';
$sql1 = 'SELECT * FROM menu_items WHERE item_parent = 0';
$result1 = $db->query($sql1);
if (isset($_GET['search'])) {
$sqlSearch = "SELECT heading, details FROM content WHERE ( details LIKE ? OR heading LIKE ? )";
$stmt = $db->stmt_init();
if (!$stmt->prepare($sqlSearch)) {
$error = $stmt->error;
} else {
$var1 = '%' . $_GET['searchterm'] . '%';
$stmt->bind_param('ss', $var1, $var1);
$stmt->execute();
$stmt->bind_result($heading, $details);
}
}
} catch (Exception $e) {
$error = $e->getMessage();
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Test</title>
</head>
<body>
<?php if (isset($error)) {
echo "<p>$error</p>";
}
?>
<ul>
<?php while ($row = $result1->fetch_assoc()) { ?>
<li><?php echo $row["item_title"]; ?>
<?php
$sql2 = 'SELECT * FROM menu_items WHERE item_parent = ' .$row["item_id"];
$result2 = $db->query($sql2);
if (isset($error)) {
echo "<p>$error</p>";
}
if (isset($result2)) { ?>
<ul>
<?php while ($row2 = $result2->fetch_assoc()) { ?>
<li><?php echo $row2["item_title"]; ?></li>
<?php } ?>
</ul>
<?php }
?>
</li>
<?php } ?>
</ul>
<!-- Search form and results below -->
<form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<p>
<label for="searchterm">Enter a name or part of one:</label>
<input type="search" name="searchterm" id="searchterm"> <input type="submit" name="search" value="Go"></p>
</form>
<?php
if (isset($error)) {
echo "<p>$error</p>";
}
$stmt->store_result();
$numrows = $stmt->num_rows;
if ($numrows) {
echo "<p>Total results found: ". $numrows;
} else {
echo "No Results";
}
while ($stmt->fetch()) {
echo "<p>Heading: " . $heading . "</p>";
echo $details;
echo "<hr/>";
}
?>
</body>
</html>
<?php
$result1->free();
$stmt->free_result();
if (isset($db)) {
$db->close();
} ?>
Looks like I found the issue. Basically, the problem is that MySQLi thinks there might be more results and it won't run any further statements or queries until the results from the search query are stored or the statement is closed. I didn't do either origionally until after I tried running the sub-nav query. I now moved this line of code $stmt->store_result(); under $stmt_bind_result($heading, $details); and now all the queries work.
$stmt->execute();
$stmt->bind_result($heading, $details);
$stmt->store_result();