CODEWITHSUNDEEP
phpmysql
Anthony
Anthony

Reputation: 53

Search MySQL database in PHP

I'm attempting to make a search page that allows a user to type any part of a title of a book from a mysql database. With this code I am getting no results no matter the word I put in. The goal is to use the query result and use num_rows to determine the number of rows in the result and the fetch_assoc method to fetch a result row as an associative array. I am getting no errors. Just blank results.

The search books page: 

$page_title = "Search book";

include ('includes/header.php');
?>
<h2>Search Books by Title</h2>
<p>Enter one or more words in book title.</p>
<form action="searchbooksresults.php" method="get">
<input type="text" name="terms" size="40" required />&nbsp;&nbsp;
<input type="submit" name="Submit" id="Submit" value="Search Book" />
</form>
<?php

include ('includes/footer.php');
?>

The search results page 

$page_title = "Books in Our Store";
require 'includes/header.php';
require_once('includes/database.php');

//retrieve the search terms from a query string
$term_string = filter_input(INPUT_GET, "term", FILTER_SANITIZE_STRING);
$terms = explode(" ", $term_string);

//sql query statement
$sql = "SELECT * FROM books WHERE 1";
foreach ($terms as $term){
$sql .= "AND CONCAT(title) LIKE %" .$term. "%";
}
//execute the query
$query = $conn->query($sql);

//using the query results to dertmine number of rows
$results = @$conn->num_rows($query);


//Handle errors
if (!$query) {
$errno = $conn->errno;
$error = $conn->error;
$conn->close();
die("Selection failed: ($errno) $error.");
}
?>

<h2>Books: <?php $term ?></h2>
<table id="booklist" class="booklist">
    <tr>
    <th>Title</th>
    <th class="col2">Author</th>
    <th class="col3">Category</th>
    <th class="col4">Price</th>
    </tr>

<?php
while ($row = $query->fetch_assoc($results)) {
    echo "<tr>";
    echo "<td><a href='bookdetails.php?id=", $row['id'], "'>",     $row['title'], "</a></td>";
    echo "<td>", $row['author'], "</td>";
    echo "<td>", $row['category_id'], "</td>";
    echo "<td>", $row['price'], "</td>";
    echo "</tr>";
}
?>
</table>

<?php
require 'includes/footer.php';

Upvotes: 2

Views: 92

Answers (1)

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

Try replacing AND to OR because in the way you are using it right now a row must have all terms from input delimiter space. Also add single quote.

$sql .= "AND CONCAT(title) LIKE %" .$term. "%";

To:

$term_string = 'Term1 Term2 Term3';
$terms = explode(" ", preg_replace('/(\s)+/', ' ', trim($term_string))); //Remove multiple spaces

$sql = "SELECT * FROM books";
$temp = array();

foreach ($terms as $term){
    $temp[] = "title LIKE '%$term%'";
}
if (!empty($temp)) {
    $sql .= " WHERE ".implode(' OR ', $temp);
}

echo $sql;

Result:

SELECT * FROM books WHERE title LIKE '%Term1%' OR title LIKE '%Term2%' OR title LIKE '%Term3%'

Furthermore don't supress errors using @. Finally try to use prepared statements.

Upvotes: 3

Related Questions