Select returns 0 results

PHP
1

when i run the following script it returns 0 results from the data base. By accident I duplicated a row in mysql when I query it, it will return both. If I take the duplicate out it returns 0 results. I am learning both php and mysql.

                   [B]PHP Script[/B]

<?php
//Force script errors and warnings to show during production only.
error_reporting(E_ALL);

ini_set(‘display_errors’, ‘1’);

//  intialize search_output variable

$search_output = “”;

if(isset($_POST[‘searchquery’]) && $_POST[‘searchquery’] != “”){
// run code if condition meets here
$searchquery = preg_replace(‘#[^a-z 0-9?!]#i’, ‘’, $_POST[‘searchquery’]);

	 $sqlCommand = "SELECT * FROM search WHERE keywords ='%$searchquery%'";

    include_once("connect_to_mysql.php");

    $query = mysql_query($sqlCommand) or die(mysql_error());

 $count = mysql_num_rows($query);

 if($count &gt; 1){

	 $search_output .= "&lt;hr /&gt;$count results for &lt;strong&gt;$searchquery&lt;/strong&gt;&lt;hr /&gt;";

	 while($row = mysql_fetch_array($query)){

             $id = $row["id"];
        $keywords = $row["keywords"];
	    $title = $row["title"];
		$link = $row["link"];
		$description = $row["description"];
		
		 $search_output .= "Item:   $title,&lt;br /&gt;
		                   Description - $description,&lt;br /&gt;
		                 &lt;a href='$link'&gt;$link&lt;/a&gt;&lt;br /&gt;&lt;hr /&gt;";

            } // close while

 } else {

	 $search_output = "&lt;hr /&gt;0 results for &lt;strong&gt;$searchquery&lt;/strong&gt;&lt;hr /&gt;$sqlCommand";

 }

}

?>

<form action=“<?php echo $_SERVER[‘PHP_SELF’]; ?>” method=“post”>

Search: <input name=“searchquery” type=“text” size=“40” maxlength=“88”>

<input name=“myBtn” type=“submit”>
</form>

Data base

Host: 127.0.0.1
Database: search_box
Generation Time: Dec 03, 2012 at 09:28 PM
Generated by: phpMyAdmin 3.5.2.2 / MySQL 5.5.27
SQL query: SELECT * FROM search LIMIT 0, 30 ;
Rows: 7

id

title

description

keywords

link

1

Paper Rush

Paper rush is used in seat weaving

paper rush, fiber rush, fiber cord

paper_rush.php

4

Flat Reed

Flat reed is used in basket weaving an wicker furniture.

flat reed, flat flat reed, flat rattan core

flat_reed.php

5

Round Reed

round reed is used in wicker furniture and basket weaving.

round reed, center cane, rattan core

round_reed.php

6

Binder Cane

Binder cane is used for wiker furniture wrappings, closewoven seat weaving and the final step of hand cane.

binder cane

binder_cane.php

7

Open Mesh Cane

Open mesh cane is used in seats an backs that have a groove around the frame.

Open mesh cane, machined cane, pressed cane, cane webbing

openmesh_cane.php

8

Reed Spline

Reed spline is used to hold the cane in a groove when using cane webbing

red spline, spline

reed_spline.php

10

Round Reed

Round reed is used in wicker furniture and basket weaving.

round reed, center cane, rattan core

round_reed.php

2

So delete the duplicate :slight_smile:
Using PHPMyAdmin, it’s easy deleting a row.

If I take the duplicate out it returns 0 results.

What do you mean by “take out”?

SELECT * FROM search WHERE keywords ='%$searchquery%'

That WHERE condition makes no sense. Use = without the % to get a specific value, or use LIKE and % to get values that contain the search value anywhere.
For example, if $searchquery contains ‘apple’, then

keywords = 'apple'

will return all rows with the value ‘apple’ in the keywords column, while

keywords IN '%apple%'

will return all rows with a value in the keywords column that contains ‘apple’, like ‘apple tree’, ‘apple’, ‘two apples’.
Your query

keywords = '%apple%'

would return something only if there is a row with ‘%apple%’ in the keywords column.

paper rush, fiber rush, fiber cord

Comma separated values in a column might mean complications if you want to do stuff with those values, like for example search for them !
Of course, using LIKE ‘%$searchquery%’ resolves that problem, as long as you don’t want to search for exact keywords.

Edit: I almost forgot! Since you are learning PHP and MySQL, you might want to start learning the mysqli_ extension, because the mysql_ one is [URL=“http://php.net/manual/en/intro.mysql.php”]not recomended for writing new code.

3

I apolgize I have been working on this for 2 solid days. I had the like in there as you stated, but forgot to put it back. I took the commas out of the keywords, and it still does the same thing
0 results for fiber cord

SELECT * FROM search WHERE keywords LIKE ‘%fiber cord%’

I duplicated one of the rows again and it works but 2 results
2 results for cane webbing

Item: Open Mesh Cane,
Description - Open mesh cane is used in seats an backs that have a groove around the frame.,
openmesh_cane.php

Item: Open Mesh Cane,
Description - Open mesh cane is used in seats an backs that have a groove around the frame.,
openmesh_cane.php

Still don’t understand why it wont get the results of the row.

I am using this on xampp on my local machine php5. & mysql 5,

I noticed in my search the last few days that PDO or mysqli but oracles documentation is not real clear for a beginner, any recomendations as far as books?

Thank you

4

The problem is not the comma’s, it’s having multiple values in one column. But it shouldn’t be the cause of your problem.

0 results for fiber cord

SELECT * FROM search WHERE keywords LIKE ‘%fiber cord%’

I duplicated one of the rows again and it works but 2 results
2 results for cane webbing

Item: Open Mesh Cane,
Description - Open mesh cane is used in seats an backs that have a groove around the frame.,
openmesh_cane.php

Item: Open Mesh Cane,
Description - Open mesh cane is used in seats an backs that have a groove around the frame.,
openmesh_cane.php

Still don’t understand why it wont get the results of the row.

The problem is in this line

if($count > 1){

I am using this on xampp on my local machine php5. & mysql 5,

I noticed in my search the last few days that PDO or mysqli but oracles documentation is not real clear for a beginner, any recomendations as far as books?

Thank you

Oracles documentation? Did you take a look at the PHP documentation I linked to?

5

One result is not greater than one!! A different set of eyes sure is great. Yes I have both php & msql documentation on my favorite bar. Thanks a million