Search results will only work if one word searched for?

I don't know how your database table is structured, but I approach this a little differently since I figure people are going to query for all sorts of things.

<?php

   $query = $_GET['query'];

    // gets value sent over search form

$min_length = 2;

    // you can set minimum length of the query if you want

    if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then

        $query = htmlspecialchars($query);

        // changes characters used in html to their equivalents, for example: < to &gt;

        $query = mysql_real_escape_string($query);

        // makes sure nobody uses SQL injection

         $query = strip_tags($query);

                                    // strip code injection

         $query = trim ($query);

                                    // trim white spaces

$raw_results = mysql_query("SELECT * FROM products_table WHERE (`longdesc` LIKE '%".$query."%') OR (`prodcode` LIKE '%".$query."%') OR (`prodname` LIKE '%".$query."%') OR (`category` LIKE '%".$query."%') OR (`price` LIKE '%".$query."%') ORDER BY `prodcode` ASC") or die(mysql_error());

I won't tell you it's perfect, but it picks up more relevant results.

Nancy O.

As you've discovered, DW server behavior searching is not very robust. In most real world cases, it is practically useless. To achieve what you want, you will need to parse the separate words of the input string into an array and then expand that into the WHERE clause dynamically so that each individual word is tested. You need to determine whether to combine terms with AND or with OR depending on the type of search you want.

Another idea is to investigate MySQL FULLTEXT searches. This will probably makes things easier and provide better results. But again, you will need to write the PHP and SQL yourself.

Sara.Reese wrote:

 

Hello, I have almost finished my works new website http://www.reese-test2.co.uk, but while testing it, I have noticed that the search isn't working as great as I would like.

 

For example if you search for "floodlight" then it produces results, but if you search for "black floodlight" then no results come back, which is strange as both words are in in the same "description" sentence in the mySQL table.

 

Is there something in Dreamweaver that I have missed so it can search if more than one word is added to the search box?

 

The recordset is currently set up to see info from the "code" and "description" columns.

 

Recordset screenshot as follows:

 

SELECT *

FROM productsearch

WHERE `description` LIKE %colname% OR `code` LIKE %colname%

ORDER BY code ASC

 

thanks for your help

Sara (I'm using DW CS6)

Use  php 'explode'

First get the search terms from your form field - 'search_box':

$searchTerms = $_POST['search_box'];

Then split the terms using explode:

$searchTerms = explode(" ", $searchTerms);

Asign part of the sql query to a variable:

$searchCondition = "description LIKE '%" . implode("%' OR description LIKE '%", $searchTerms) . "%'";

Then make sure the variable is inserted into the sql query string:

"SELECT * FROM productsearch WHERE $searchCondition ORDER BY code ASC ";

Nice osgood. A very concise, easy to understand way to do what I suggested.

"parse the separate words of the input string into an array and then expand that into the WHERE clause dynamically so that each individual word is tested"

And again, the OP will need to decide whether to combine terms with AND or OR. A radio button on the search form can be added to allow users to "Search for All Terms" or "Search for Any Term" or "Search Phrase as Entered"

bregent wrote:

 

Nice osgood. A very concise, easy to understand way to do what I suggested.

 

Well must admit it's not all my own - I found a few resources on Google and kind of pieced it together as none worked 'out of the box' so to speak, or looked a bit complex.

It's a simple solution, which most should be able to understand, and a step up from the 'single word' option that the limited DW server behaviours offer BUT as you say there are plenty of other criteria that could be introduced depending on how precise the search needs to be.

I'm concerned that exploding $search_query_terms into an array and cross checking each term against a large db might be very slow.  Have you encountered problems with this?

Nancy O.

Nancy O. wrote:

 

I'm concerned that exploding $search_query_terms into an array and cross checking each term against a large db might be very slow.  Have you encountered problems with this?

 

 

Nancy O.

Hi Nancy,

I've used explode a couple of times but only on fairly small quanties of data to be searched. It seems to perform smoothly enough for what I need it to do - I don't have any experience of working with large volumes of data to check through unfortuantely.

It could slow things down - how many rows do you consider to be a large table

If you are searching for any term (OR) then it probably wouldn't hurt as much as if you are searching all terms (AND). If you are testing a large number of terms against multiple columns that could also cause performance issues. You'd need to test and add performance indices as needed and possibly optimize the query in other ways.

You also might want to limit the number of search terms in the query; test the number of exploded items and give the user an error if it exceeds a set amount.

At some point, a FULLTEXT search might be more efficient, I don't know.

Can you do a test? Copy the below code and paste into a new Dreamweaver document and save as - search_test.php

Change the text in red below to that of your database connection details.

Then temporarily point the search form action field on one of your pages at the search_test.php page - see if you get any results.

COPY FROM HERE ----->

<?php $conn = new mysqli('server' , 'username' , 'password' , 'database_name'); ?>

<?php

$searchTerms = $_POST['search_box'];

$searchTerms = explode(" ", $searchTerms);

$searchCondition = "description LIKE '%" . implode("%' OR description LIKE '%", $searchTerms) . "%'";

?>

<?php

// get list of products that contain search terms

$sql = "SELECT * FROM description WHERE $searchCondition";

$listProducts = $conn->query($sql) or die($conn->error);

// count no of rows

$numRows = $listProducts->num_rows;

?>

<!DOCTYPE HTML PUBLIC>

<html>

<head>

<meta http-equiv="charset=UTF-8">

<title>Search Test</title>

</head>

<body>

<table width="100%" border="0" align="center" cellpadding="0" cellspacing="4">

<tr align="center" bgcolor="#FF0099" class="mainareaH">

<td width="150" bgcolor="#0099FF">PRODUCT CODE</td>

<td width="90" bgcolor="#0099FF">IMAGE</td>

<td bgcolor="#0099FF">DESCRIPTION</td>

<td width="90" bgcolor="#0099FF">GO</td>

</tr>

<?php while($row = $listProducts->fetch_assoc()) { ?>

<tr align="center">

<td width="150" bgcolor="#333333" class="mainareaH"><?php echo $row['code']; ?></td>

<td width="90" bgcolor="#333333"><?php echo $row['picture']; ?></td>

<td bgcolor="#333333" class="mainareaP"><?php echo $row['description']; ?></td>

<td width="90" bgcolor="#333333"><?php echo $row['go']; ?></td>

</tr>

<?php } ?>

</table>

</body>

</html>

Sara.Reese wrote:

 

Hi Osgood, I did as above, I copy & pasted the above into a new doc called search_test.php

 

and saved another page with the search_box on and called it test.php - pointing the search_box form to search_test.php

 

I'm however getting a blank page saying:
Table 'cl44-localhost.description' doesn't exist

 

my details

server: localhost

username: cl44-localhost

password: passwordgoeshere (not my real password)

database name: cl44-localhost

below is a screenshot of my info (password is changed), shouldn't searchDB go somewhere?

also the table on my database is called productsearch so doesnt this need to also go somewhere?

I.E in the SELECT * FROM bit somehow?

thanks

Sara

Change this line:

$sql = "SELECT * FROM description WHERE $searchCondition";

To:

$sql = "SELECT * FROM productsearch WHERE $searchCondition";

Try changing OR in the line below to AND - see if that narrows down the results.

$searchCondition = "description LIKE '%" . implode("%' OR description LIKE '%", $searchTerms) . "%'";

Sara, that's what I was saying about adding a radio button on the search form to allow the user to choose between searching for any words in the search term or all words. Depending on which they selected you would combine the terms with AND or OR

Return results that contain any term

$searchCondition = "description LIKE '%" . implode("%' OR description LIKE '%", $searchTerms) . "%'";

Return results that contain all terms

$searchCondition = "description LIKE '%" . implode("%' AND description LIKE '%", $searchTerms) . "%'";

Try this:

$searchCondition = "(description LIKE '%" . implode("%' AND description LIKE '%", $searchTerms) . "%')";

$searchCondition .= " OR (code LIKE '%" . implode("%' AND code LIKE '%", $searchTerms) . "%')";

Probably a slight error in the code I provided. Can you add an echo statement after the code I provided so we can see how it is being concatenated:

$searchCondition = "(description LIKE '%" . implode("%' AND description LIKE '%", $searchTerms) . "%')";

$searchCondition .= " OR (code LIKE '%" . implode("%' AND code LIKE '%", $searchTerms) . "%')";

echo $searchCondition

also give us details about the syntax error.

bregent wrote:

 

Probably a slight error in the code I provided. Can you add an echo statement after the code I provided so we can see how it is being concatenated:

 

$searchCondition = "(description LIKE '%" . implode("%' AND description LIKE '%", $searchTerms) . "%')";

$searchCondition .= " OR (code LIKE '%" . implode("%' AND code LIKE '%", $searchTerms) . "%')";

echo $searchCondition

 

also give us details about the syntax error.

The above works fine for me Bregent.

description LIKE '%FL10SMD%' AND description LIKE '%%' OR (code LIKE '%FL10SMD%' AND code LIKE '%%')

description LIKE '%Green%' AND description LIKE '%Frog%' AND description LIKE '%%' OR (code LIKE '%Green%' AND code LIKE '%Frog%' AND code LIKE '%%')

EDITED:

Apart from only the exact code number is returned when I assume what is needed is anything like the number hence ORDER BY code

It seems it returns an exact match once it finds a correct number - like searching for FL10 will only return the product assigned the code number FL10SMD NOT FL20SMD, FL30SMD etc BUT if you just type in FL the results will widen by returning more options.

Seems to work pretty good to me though.

Sara.Reese wrote:

 

Hi Bregent, that is now working now, I can now search a code name and get results. I replaced

$searchTerms) . "%'";

with

echo $searchCondition

for it to work.

 

I dont understand what you have done here. Replacing $searchTerms) . "%'"; with $searchConditions isn't correct.

Your code should look like below:

$searchCondition = "description LIKE '%" . implode("%' AND description LIKE '%", $searchTerms) . "%'";

$searchCondition .= " OR (code LIKE '%" . implode("%' AND code LIKE '%", $searchTerms) . "%')";

$searchCondition .= "ORDER BY code ASC";

No 2. Surround the table with a show/hide php region (see in red below) and check if any rows were returned from the mysqli query string.

This is what this piece of code does:

// count no of rows

$numRows = $listProducts->num_rows;

This displays the table IF a record/s is returned.

<?php if($numRows > 0) { ?>

<table width="100%" border="0" align="center" cellpadding="0" cellspacing="4">

<tr align="center" bgcolor="#FF0099" class="mainareaH">

<td width="150" bgcolor="#0099FF">PRODUCT CODE</td>

<td width="90" bgcolor="#0099FF">IMAGE</td>

<td bgcolor="#0099FF">DESCRIPTION</td>

<td width="90" bgcolor="#0099FF">GO</td>

</tr>

<?php while($row = $listProducts->fetch_assoc()) { ?>

<tr align="center">

<td width="150" bgcolor="#333333" class="mainareaH"><?php echo $row['code']; ?></td>

<td width="90" bgcolor="#333333"><?php echo $row['picture']; ?></td>

<td bgcolor="#333333" class="mainareaP"><?php echo $row['description']; ?></td>

<td width="90" bgcolor="#333333"><?php echo $row['go']; ?></td>

</tr>

<?php } ?>

</table>

<?php } ?>

Then add the below after the table code. If no records are found the message 'No records found' will be viewable

<?php if($numRows <= 0) { ?>

<h1>No records found</h1>

<?php } ?>

1. Shouldn't be happening if you are inserting something into the search box on another page which points to the results page.

Does this happen if you open the results.php page directly in a browser or is this happening coming from another page where something has been inserted the search box?

This could be one of 2 things I can think of:

1. Have you got the right REMOTE server details in the connections string:

<?php $conn = new mysqli('server' , 'username' , 'password' , 'database_name'); ?>

2. Is the remote server set up for mysqli? mysqli is the (improved) version of mysql - most servers are compatible with both mysql and mysqli

To test this insert the below into a blank DW document, upload to server and browse to the file. It should return all of the php details. Somewhere in the list it should mention 'mysqli'

<?php

phpinfo();

?>

Sara.Reese wrote:

 

Hi Osgood, yeah i'm pretty sure they are correct, but for just in cases I have messaged the hosting company to see if there are any issues, seeing as it works perfectly until I upload to their server!

 

I copied the above text and saved it - you can see the page here:

http://www.reese-test2.co.uk/testsqli.php

mysqli is enabled so it's not that.

Is there anything else on that page at all that uses any DW server behaviours - mysql and mysqli dont mix?

Also can you post the COMPLETE page code here so I can test it? MINUS the connection details of course.

Sara.Reese wrote:

 

Hi Osgood, I just redid the results.php from scratch, so there is defiantly no old DW code in there. Its probably something to do with the hosting company (i've trouble with them before). As soon as I hear back from them I will let you know.

 

thanks for all your help

Sara

The best way to tell for sure is if you have access to other remote hosting where you can test it.

As it's working now....no need to test. Seems it was a hosting issue, which happens.