Search MySQL database in PHP

Question

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';

he did "WHERE 1" so he would only have to write AND for any other possible query. I tend to write WHERE 1=1 for my dynamic queries. OH and I see a problem. You need to add a space into the $sql = "SELECT * FROM books WHERE 1_"; <-- it will echo "1AND" if you don't. — JoeCrash
– JoeCrash, Commented Nov 10, 2015 at 1:00

Kostas Mitsarakis · Accepted Answer · 2015-11-10 00:11:04Z

3

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.

edited Nov 10, 2015 at 0:11

answered Nov 9, 2015 at 23:41

Kostas Mitsarakis

4,7473 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Anthony Over a year ago

got 2 errors from your edit. "use of undefined constant temp-assumed 'temp' and fatal error: "call to undefined method mysqli::num_rows()

Professor Abronsius Over a year ago

that's just a minor typo, should be if( !empty( $temp ) ){

Kostas Mitsarakis Over a year ago

Ok, sorry about that. I 'll try it more carefully.

Kostas Mitsarakis Over a year ago

Yes, i fixed it, sorry about that (only 2 hours sleep).

Anthony Over a year ago

Your solution to execute the query works but the fatal error is coming from trying to store the result and determine the number of rows. I am then using the fetch assoc to try and display the results. Thoughts?

|

Collectives™ on Stack Overflow

Search MySQL database in PHP

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related