My simple PHP search, not returning all results

[jainsy]

Hi all,

 

In need of a bit of desperate help... I enjoy fiddling in a bit of code and needed to provide the ability for people to search a site. However, I'm experiencing some problems return some records from the MySQL database.

 

The search in question searches a table named items either by the item name or its author, the user can choose from a drop down box (the items are books). However, i have a record in this table with the title 'Dictionary of Law'. If I search dictionary then the record is returned but if I search for Law the query returns no matches. I'm finding this a bit strange because I am using a like search..

 

I am using the following html and php to conduct the search.

 

        <form method="post" action="search.php">
        Search by
	<select name="searchtype">
	  <option value="itemName">Title</option>
	  <option value="itemAuthor">Author</option>
          <option value="itemISBN">ISBN</option>
        
        </select>
        Keywords
        <input type="text" name="search" size=20 maxlength=25>
        <input type="Submit" name="Submit" value="Submit">
        </form>
        <?php

	mysql_select_db("web163-project"); 

	$search=$_POST["search"];
	$searchtype=$_POST["searchtype"];

	$result = mysql_query("SELECT * FROM items WHERE $searchtype LIKE '%$search%'");
	$row = mysql_fetch_assoc($result); 

	$num_results = mysql_num_rows($result); 
	?>

 

The search in question can be viewed here http://jainsy.com/itmb/search.php

 

p.s. I tried using the search feature but it was unavailable at time of posting.

 

Kind regards

 

[Psycho]

First, echo your query to the page to ensure it has the value that you expect:

 

$search=$_POST["search"];
$searchtype=$_POST["searchtype"];
$query = "SELECT * FROM items WHERE $searchtype LIKE '%$search%'";
//Debugging info
echo "Search: {$search}<br>\n";
echo "Search Type: {$searchtype}<br>\n";
echo "Query: {$query}<br>\n";
//End debugging
$result = mysql_query($query);

[ChemicalBliss]

hmm. interesting.

 

I would imagine its something to do with the spaces, perhaps full-text is the culprit:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

 

Take a look at that and create the query you need, see if it works then. I hve heard, though, that there is a minimum of 4 characters needed for a MATCH clause to give results.

 

Have a good read of the comments as well.

 

good Luck

-cb-

[Psycho]

I would imagine its something to do with the spaces

 

Don't think so. I tried his search and it workes fine for "Dictionary of ", but as soon as I add the "L" it didn't find that record. And, just searching for "of" works. But searching on "Law" fails to find the record. And, searching just on the letter "L" does work. Very odd indeed.

[ChemicalBliss]

I meant in respect to the full text. All i can think of atm is that its a text field rather than a varchar and he needs to use a full text search. Though i could be wrong but worth a look.

 

-cb-

[jainsy]

Still havn't worked this out.

 

In phpmyadmin I've tried executing some queries using a combination of spaces and letters (obviously as long as they are in the correct order) as you guys tried above and they all return the correct results, this is really weird, and annoying!!

 

Had a brief read of that document at mysql.net

 

I meant in respect to the full text. All i can think of atm is that its a text field rather than a varchar and he needs to use a full text search. Though i could be wrong but worth a look.

 

All my fields are varchar.

[ChemicalBliss]

Follow mjdamato's advice and echo the query.

 

cb

[Psycho]

Did use the code I provided so you can verify that the query is what you expect? I'd run that code using one of the values that is not returning the correct results. If the query looks right, try copy/pasting the exact same query in phpmyadmin to see if the results are different.

 

When debugging problems such as this you have to exclude ONE variable at a time. By executing queries manually in phpmyadmin you are validating that THOSE queries are working. You need to run the exact same query in phpmyadmin as the one which is giving you trouble in the application. That is why you should echo the query to the page. ALthough you think you know whatthey queries should be you need to verify that.

[jainsy]

I've left the code you provided on the page now, but I cant get the actual MySQL query to echo, how is this done?

 

I now have

	$result = mysql_query("SELECT * FROM items WHERE $searchtype LIKE '%$search%'");

	$row = mysql_fetch_assoc($result); 

	$num_results = mysql_num_rows($result); 

	echo "<B>Debugging</b><br>Search: {$search}<br>\n";
	echo "Search Type: {$searchtype}<br>\n";
	echo "Query: {$result}<br>\n";

 

Regards

[jainsy]

Okay, the search has slightly change. I've added some more advanced features. However, the same error is occuring. Where the mysql statement is displayed this is a string generated by the all the fields that the user chooses to include in their search.

 

This string is then used for the SQL query

$result = mysql_query("$mysqlstring");

 

The string generates SELECT * FROM items WHERE itemName LIKE '%law%' and this still displays no results, however, EXACTLY when I copy this into phpmyadmin I get one result, which is what is supposed to happen.

[ChemicalBliss]

to echo something, you use the echo function.

 

The query is the string you put in the mysql_query function, it is a mysql query string. so, to echo the query:

 

echo($query); where $query is the mysql query string, eg:

 

	
$query = "SELECT * FROM items WHERE $searchtype LIKE '%$search%'";
echo($query); // Echo the Query.
$result = mysql_query($query);
$row = mysql_fetch_assoc($result); 
$num_results = mysql_num_rows($result); 
echo "<B>Debugging</b><br>Search: {$search}<br>\n";
echo "Search Type: {$searchtype}<br>\n";
echo "Query: {$result}<br>\n";

 

-cb-

[jainsy]

Okay, thats exactly what I did originally and I get 'Resource id #4' echoed and not the actual query...

 

[Psycho]

Okay, thats exactly what I did originally and I get 'Resource id #4' echoed and not the actual query...

 

No, that is not what you did. You didn't use the code I provided.

 

In your original code you declared the query string INSIDE the mysql_query() function. In the code I provided I broke that up to where the query string is assigned to a variable and THEN that query string is run within the mysql_query() function. I always advise against defining the query string within the function as it makes debugging much more difficult - this post is a prime example of that.

 

Follow the code I first provided so you can echo the query to the page.