MySQL Query to search Database using PHP

Question

So what i am trying to do is run a search query. i want the user to be able to search a database of books in mysql by entering a search term in a box on a HTML form. i then want the mysql data to be displayed based on what the user entered. This is annoying, this worked a couple of weeks ago and now it does not. Whenever I run the script, I get the following messages:

Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\Shopping Cart 2.0\search.php on line 52

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\Shopping Cart 2.0\search.php on line 67

I tried mysql_error but it told me the same thing. Maybe I put it in the wrong place? Anyways, any help would be appreciated. Thanks.

Here is the HTML form that is referenced:

<form action="search.php" method="GET">
<input type="text" name="query" alt="Search Books" value=" Search by ISBN, Author, or Title" 
maxlength="356" size="52" onfocus="if(this.value==this.defaultValue)this.value='';"     onblur="if(this.value=='')this.value=this.defaultValue;" />
<input type="submit" name="querybtn" value="Search Books"/>
 </form>

Below is the php code:

<?php
 $query= $_GET['query'];

$q1 = "(`Author(s)` LIKE '%" . $query . "%')";
$q2 = "(`Title` LIKE '%" . $query . "%')";
$q3 = "(`Price` LIKE '%" . $query . "%')"; 
$q4 = "(`Edition` LIKE '%" . $query . "%')";
$q5 = "(`Publication Date` LIKE '%" . $query . "%')";
$q6 = "(`ISBN-10` LIKE '%" . $query . "%')";
$q7 = "(`ISBN-13` LIKE '%" . $query . "%')";
$qfinal = $q1 . " OR " . $q2 . " OR " . $q3 . " OR " . $q4 . " OR " . $q5 . " OR " . $q6 . " OR " . $q7;

$qt1 = "SELECT * FROM accountingtextbooks WHERE " . $qfinal;
$qt2 = "SELECT * FROM anthropologytextbooks WHERE " . $qfinal;
$qt3 = "SELECT * FROM architecturetextbooks WHERE " . $qfinal;
$qt4 = "SELECT * FROM biologytextbooks WHERE " . $qfinal;
$qt5 = "SELECT * FROM businesstextbooks WHERE " . $qfinal;
$qt6 = "SELECT * FROM computersciencetextbooks WHERE " . $qfinal;
$qt7 = "SELECT * FROM economicstextbooks WHERE " . $qfinal;
$qt8 = "SELECT * FROM engineeringtextbooks WHERE " . $qfinal;
$qt9 = "SELECT * FROM geographytextbooks WHERE " . $qfinal;
$qt10 = "SELECT * FROM geologytextbooks WHERE " . $qfinal;
$qt11 = "SELECT * FROM historytextbooks WHERE " . $qfinal;
$qt12 = "SELECT * FROM mathematicstextbooks WHERE " . $qfinal;
$qt13 = "SELECT * FROM philosophytextbooks WHERE " . $qfinal;
$qt14 = "SELECT * FROM psychologytextbooks WHERE " . $qfinal;
$qt15 = "SELECT * FROM sociologytextbooks WHERE " . $qfinal;


$query = $qt1 . " UNION " . $qt2 . " UNION " . $qt3 . " UNION " . $qt4 . " UNION " . $qt5 . " UNION " . $qt6 . " UNION " . $qt7 . " UNION " . $qt8 . " UNION " . $qt9 . " UNION " . $qt10 . " UNION " . $qt11 . " UNION " . $qt12 . "UNION " . $qt13 . " UNION " . $qt14 . " UNION " . $qt15;

$result= mysql_query($query); 

$num = mysql_numrows($result);

echo "<table id='search' border='1'>

<tr>
<tr>
<th>Author(s)</th>
<th>Title</th>
<th>Price</th>
<th>Edition</th>
<th>Date</th>
<th>ISBN-10</th>
<th>ISBN-13</th>
</tr>";

while($row = mysql_fetch_array($result))
{
  echo "<tr>";
  echo "<td>" . $row['Author(s)'] . "</td>";
  echo "<td>" . $row['Title'] . "</td>";
  echo "<td>" . $row['Price'] . "</td>";
  echo "<td>" . $row['Edition'] . "</td>"; 
  echo "<td>" . $row['Publication Date'] . "</td>"; 
  echo "<td>" . $row['ISBN-10'] . "</td>"; 
  echo "<td>" . $row['ISBN-13'] . "</td>";
}

?>

EDIT: Here is my code to establish a connection to the database.

<?php 

 $username = "root";
 $password = "test";   
 $hostname = "localhost";
 $dbname = "thetextbookexpress";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
mysql_select_db($dbname, $dbhandle) or die('Could not select database.');

Answer 1

Where is you connection to database?

You should connect to mysql like this:

<?php
$link=@mysql_connect(/*host:*/'localhost',/*user:*/'root',/*password:*/'') or die('err/:'.mysql_error());

$db=mysql_select_db(/*database:*/'test') or die('err:'.mysql_error(),/*the resorce link that the warning is because*/$link);

$res=mysql_query($query);
?>

AND please be careful of SQL INJECTION and also try to use PDO instead of mysql_* to connect to database.

And also the warning said :

Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\Shopping Cart 2.0\search.php on line 52

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\Shopping Cart 2.0\search.php on line 67

means you have not the correct connection so you should look at your mysql_connect to see if it has some errors with somthing like:

$link=@mysql_connect(/*host:*/'localhost',/*user:*/'root',/*password:*/'') or die('err/:'.mysql_error());

Answer 2

The mysql_query function fails and returns FALSE which is a boolean, as it is told on the warnings. Examine your query to look for some errors on it. See mysql_query reference

You may echo $query before mysql_query($query) and look for the errors on the select. When using unions the most common problem is to have different rown counting on each select statement.

Some notes:

1. mysql_* functions are deprecated. You should use mysqli_* or PDO instead.
2. Sanitize your form input before appending it to your queries.