separating search words

jerastraub · October 2, 2006

Okay here's my problem.

I have a php mysql search feature on my site that shows products. I want to know if there is a way to make it so when a customer searches for let's say "blue blouse" it will search for "blouse" and "blue" serparately. So that the customer will get search results for all blouses that are blue. But since these are datafeeds I am dealing with I want to search serveral fields for these terms.

Here my code:

search form:

[code]<form method="GET" action="search.php">
<input type="text" value="Ex: Blouse" name="search" size="18" onFocus="clearBox(this)">
<input type="submit" value="Search">
</form>[/code]

display:

[code]<?php
include("config.php");

// Select total results for pagination
$result = mysql_query("SELECT count(*) FROM womensproducts
WHERE ProductName LIKE '%$search%' || CategoryID LIKE '%$search%' || Category LIKE '%$search%'");

$num_records = mysql_result($result,0,0);

if ($num_records == 0){
echo "There are no records to display in the selected category, please select another\n";
exit;
}

// Set maximum number of rows and columns
$max_num_rows = 3;
$max_num_columns = 3;
$per_page = $max_num_columns * $max_num_rows;

// Work out how many pages there are
$total_pages = ceil($num_records / $per_page);

// Get the current page number
if (isset($_GET['page'])) $page = $_GET['page']; else $page = 1;

// Get the category or assign a defailt
if (isset($_GET['search'])) $search = $_GET['search']; else $search = "Dress"; // enter a default category here

// Work out the limit offset
$start = ($page - 1) * $per_page;

// Run your query with the limit and offset in place
$result_main = mysql_query("SELECT *
FROM womensproducts
WHERE ProductName LIKE '%$search%' || CategoryID LIKE '%$search%' || Category LIKE '%$search%' limit $start, $per_page") or die(mysql_error());
$num_columns = ceil(mysql_num_rows($result_main)/$max_num_rows);
$num_rows = ceil(mysql_num_rows($result_main)/$num_columns);

// If there's more than one page, show links
if ($total_pages > 1){
echo <<<HTML
<table>

<tr>
<td>
HTML;

// Build the previous link
if ($page > 1){
$prev = ($page -1);
echo "<a href=\"http://pluswomen.thefreestuffvault.com/moresear-{$search}-$prev.htm\">Previous</a>";
}

// Build the page numbers
for($i = 1; $i <= $total_pages; $i++){
if($page == $i){
echo "$i ";
}
else {
echo " <a href=\"http://pluswomen.thefreestuffvault.com/moresear-{$search}-$i.htm\">$i</a>  ";

}
}

// Build the next link
if ($page < $total_pages){
$next = ($page +1);
echo " <a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$next.htm\">Next</a>";
}

// Close off the links
echo <<<HTML
</td>
</tr>
</table>
HTML;
}

// Lets start creating tables and echoing code
echo "<table>\n";

$c = 0;
while($row = mysql_fetch_array($result_main)){

// make the variables easy to deal with
extract($row);

if($row["prodSale"] < $row["prodPrice"] and $row["prodSale"] != NULL){
$price = $prodSale . " - Sale Price";

}
else {
$price = $prodPrice;

}

// open row if counter is zero
if($c == 0){
echo "<tr>";
}

// echo the individual cells
echo <<<HTML
<td align=center><table class="quick" width="180" height="350" border="0" align=center cellspacing="0" cellpadding="0">
<tr>
<td align=center><a href="http://pluswomen.thefreestuffvault.com/searchproduct-{$ProductID}.html">
<img rel="nofollow" src="{$BigImage}" border="0" height=180 alt="{$Name}"></a></td>
</tr>
<tr>
<td align=center><p id="productname"><a href="http://pluswomen.thefreestuffvault.com/searchproduct-{$ProductID}.html">{$ProductName}</A></p></td>
</tr>
<tr>
<td align=center><font size="2">Price: $ {$price}</font></td>
</tr>
</table></td>
HTML;

// increment counter - if counter = max columns, reset counter and close row
if(++$c == $max_num_columns){
echo "</tr>";
$c = 0;
}
}

// clean up table - makes your code valid!
if($c < $max_num_columns){
for($e = $c; $e < $max_num_columns; $e++){
echo " ";
}
}

// If there's more than one page, show links
if ($total_pages > 1){
echo <<<HTML
<table>
<tr>
<td align="center">
HTML;

// Build the previous link
if ($page > 1){
$prev = ($page -1);
echo "<a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$prev.htm\">Previous</a>";
}

// Build the page numbers
for($i = 1; $i <= $total_pages; $i++){
if($page == $i){
echo "$i ";
}
else {
echo " <a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$i.htm\">$i</a>  ";

}
}

// Build the next link
if ($page < $total_pages){
$next = ($page +1);
echo "<a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$next.htm\">Next</a>";
}

// Close off the links
echo <<<HTML
</td>
</tr>
</table>
HTML;
}
?>[/code]

Thanks in advance for you help.

Daniel0 · October 2, 2006

You can spilt up the words like this: [code]$words_array = explode(' ',$_POST['search']);[/code]

You might want to look at full-text searching as well:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Example: [code]SELECT * FROM stuff WHERE MATCH(title,body) AGAINST('the search string here');[/code]

Or Google-like search: [code]SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+PHP -MySQL' IN BOOLEAN MODE);[/code]

alpine · October 2, 2006

This is an example that will split and search all your fields repeately exploded if you don't want to digg into fulltext.
Fine tune it to your needs, you get the point here.
You should however look into adding index on your table to speed things up.
[code]

<?php

$search = htmlspecialchars($_GET['search']);
$sq = explode(" ",$search);
$aq = count($sq) - 1;
$ac = 0;
for($i=0; $i<=$aq; $i++)
{
if($ac == 0)
{
$query = "SELECT * FROM womensproducts WHERE ProductName LIKE '%$sq[$i]%'";
$query .= " OR CategoryID LIKE '%$sq[$i]%'";
$query .= " OR Category LIKE '%$sq[$i]%'";
}
else
{
$query .= " AND (ProductName LIKE '%$sq[$i]%'";
$query .= " OR CategoryID LIKE '%$sq[$i]%'";
$query .= " OR Category LIKE '%$sq[$i]%')";
}
if($aq == $ac)
{
$query .= " limit $start, $per_page";
}
$ac ++;
}

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

?>

[/code]

jerastraub · October 2, 2006

Okay you lost me!

I don't see where your output is. I need the out to be in this type of format: http://pluswomen.thefreestuffvault.com/search.php?search=dresses

Also I am not familiar with was you mean by: "You should however look into adding index on your table to speed things up."

I am knew with using php and mysql.

alpine · October 2, 2006

My example was only replacing one query as example,
i've put it in place here - test and adjust:
[code]

<?php
include("config.php");

// Get the category or assign a defailt
if (isset($_GET['search'])) $search = htmlspecialchars($_GET['search']);
else $search = "Dress"; // enter a default category here

$sq = explode(" ",$search);
$aq = count($sq) - 1;

$ac = 0;
for($i=0; $i<=$aq; $i++)
{
if($ac == 0)
{
$query = "SELECT count(*) FROM womensproducts WHERE ProductName LIKE '%$sq[$i]%'";
$query .= " OR CategoryID LIKE '%$sq[$i]%'";
$query .= " OR Category LIKE '%$sq[$i]%'";
}
else
{
$query .= " AND (ProductName LIKE '%$sq[$i]%'";
$query .= " OR CategoryID LIKE '%$sq[$i]%'";
$query .= " OR Category LIKE '%$sq[$i]%')";
}
$ac ++;
}

$result = mysql_query($query) or die(mysql_error());
$num_records = mysql_result($result,0,0);

if ($num_records == 0){
echo "There are no records to display in the selected category, please select another\n";
exit;
}

// Set maximum number of rows and columns
$max_num_rows = 3;
$max_num_columns = 3;
$per_page = $max_num_columns * $max_num_rows;

// Work out how many pages there are
$total_pages = ceil($num_records / $per_page);

// Get the current page number
if (isset($_GET['page'])) $page = $_GET['page']; else $page = 1;

// Work out the limit offset
$start = ($page - 1) * $per_page;

// Run your query with the limit and offset in place
$ac2 = 0;
for($i=0; $i<=$aq; $i++)
{
if($ac2 == 0)
{
$query2 = "SELECT * FROM womensproducts WHERE ProductName LIKE '%$sq[$i]%'";
$query2 .= " OR CategoryID LIKE '%$sq[$i]%'";
$query2 .= " OR Category LIKE '%$sq[$i]%'";
}
else
{
$query2 .= " AND (ProductName LIKE '%$sq[$i]%'";
$query2 .= " OR CategoryID LIKE '%$sq[$i]%'";
$query2 .= " OR Category LIKE '%$sq[$i]%')";
}
if($aq == $ac2)
{
$query2 .= " limit $start, $per_page";
}
$ac2 ++;
}

$result_main = mysql_query($query2) or die(mysql_error());

$num_columns = ceil(mysql_num_rows($result_main)/$max_num_rows);
$num_rows = ceil(mysql_num_rows($result_main)/$num_columns);

// If there's more than one page, show links
if ($total_pages > 1){
echo <<<HTML
<table>

<tr>
<td>
HTML;

// Build the previous link
if ($page > 1){
$prev = ($page -1);
echo "<a href=\"http://pluswomen.thefreestuffvault.com/moresear-{$search}-$prev.htm\">Previous</a>";
}

// Build the page numbers
for($i = 1; $i <= $total_pages; $i++){
if($page == $i){
echo "$i ";
}
else {
echo " <a href=\"http://pluswomen.thefreestuffvault.com/moresear-{$search}-$i.htm\">$i</a>  ";

}
}

// Build the next link
if ($page < $total_pages){
$next = ($page +1);
echo " <a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$next.htm\">Next</a>";
}

// Close off the links
echo <<<HTML
</td>
</tr>
</table>
HTML;
}

// Lets start creating tables and echoing code
echo "<table>\n";

$c = 0;
while($row = mysql_fetch_array($result_main)){

// make the variables easy to deal with
extract($row);

if($row["prodSale"] < $row["prodPrice"] and $row["prodSale"] != NULL){
$price = $prodSale . " - Sale Price";

}
else {
$price = $prodPrice;

}

// open row if counter is zero
if($c == 0){
echo "<tr>";
}

// echo the individual cells
echo <<<HTML
<td align=center><table class="quick" width="180" height="350" border="0" align=center cellspacing="0" cellpadding="0">
<tr>
<td align=center><a href="http://pluswomen.thefreestuffvault.com/searchproduct-{$ProductID}.html">
<img rel="nofollow" src="{$BigImage}" border="0" height=180 alt="{$Name}"></a></td>
</tr>
<tr>
<td align=center><p id="productname"><a href="http://pluswomen.thefreestuffvault.com/searchproduct-{$ProductID}.html">{$ProductName}</A></p></td>
</tr>
<tr>
<td align=center><font size="2">Price: $ {$price}</font></td>
</tr>
</table></td>
HTML;

// increment counter - if counter = max columns, reset counter and close row
if(++$c == $max_num_columns){
echo "</tr>";
$c = 0;
}
}

// clean up table - makes your code valid!
if($c < $max_num_columns){
for($e = $c; $e < $max_num_columns; $e++){
echo " ";
}
}

// If there's more than one page, show links
if ($total_pages > 1){
echo <<<HTML
<table>
<tr>
<td align="center">
HTML;

// Build the previous link
if ($page > 1){
$prev = ($page -1);
echo "<a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$prev.htm\">Previous</a>";
}

// Build the page numbers
for($i = 1; $i <= $total_pages; $i++){
if($page == $i){
echo "$i ";
}
else {
echo " <a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$i.htm\">$i</a>  ";

}
}

// Build the next link
if ($page < $total_pages){
$next = ($page +1);
echo "<a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$next.htm\">Next</a>";
}

// Close off the links
echo <<<HTML
</td>
</tr>
</table>
HTML;
}
?>

[/code]

jerastraub · October 2, 2006

Still not quit right, i am getting results, but I don't think it is searching both terms, i think it is only using the first team. Ie. Blue blouse, I get Blue Topaz Silver Ring and Mainstreet Blues (R) Everyday Jean Skirt in the results.

I think it separated the words and is only using the first.

Is there are way to get it to search both and only bring back results for "blue blouse"?

printf · October 2, 2006

if you split "blue blouse", the results will give you anything that contains blue or anything that contains blouse, so you will return a rows containing blouse that don't contain blue, and things that may contain blue but not blouse! If that what you want then user regular expression, because it will put rows containing blue and blouse first, then it will put just blouses followed by things that are blue!

[code]

SELECT ... FROM records WHERE column_name REGEXP '[[:<:]]blue|blouse[[:>:]]';

[/code]

me!

jerastraub · October 2, 2006

How do I do this with the form above, as this is user input? Cause that's exactly what I want it to do!

printf · October 3, 2006

Try it like this...

[code]
<?php
include("config.php");

$search = array ( 'Dress' );

if ( isset ( $_GET['search'] ) )
{
$temp = trim ( $_GET['search'] );

if ( ! empty ( $temp ) )
{
$search = explode ( ' ', $temp );
}
}

$temp = implode ( '|', array_map ( 'mysql_real_escape_string', $search ) );

$qadd = "ProductName REGEXP '[[:<:]]" . $temp . "[[:>:]]' ";
$qadd .= "OR CategoryID REGEXP '[[:<:]]" . $temp . "[[:>:]]' ";
$qadd .= "OR Category REGEXP '[[:<:]]" . $temp . "[[:>:]]'";

$search = implode ( ' ', $search );

// Select total results for pagination

$result = mysql_query ( "SELECT count(*) FROM womensproducts WHERE " . $qadd );

$num_records = mysql_result($result,0,0);

if ($num_records == 0){
echo "There are no records to display in the selected category, please select another\n";
exit;
}

// Set maximum number of rows and columns
$max_num_rows = 3;
$max_num_columns = 3;
$per_page = $max_num_columns * $max_num_rows;

// Work out how many pages there are
$total_pages = ceil($num_records / $per_page);

// Get the current page number
if (isset($_GET['page'])) $page = $_GET['page']; else $page = 1;

// Work out the limit offset
$start = ($page - 1) * $per_page;

// Run your query with the limit and offset in place
$result_main = mysql_query ( "SELECT * FROM womensproducts WHERE " . $qadd . " limit " . $start . ", " . $per_page ) or die ( mysql_error () );
$num_columns = ceil(mysql_num_rows($result_main)/$max_num_rows);
$num_rows = ceil(mysql_num_rows($result_main)/$num_columns);

// If there's more than one page, show links
if ($total_pages > 1){
echo <<<HTML
<table>

<tr>
<td>
HTML;

// Build the previous link
if ($page > 1){
$prev = ($page -1);
echo "<a href=\"http://pluswomen.thefreestuffvault.com/moresear-{$search}-$prev.htm\">Previous</a>";
}

// Build the page numbers
for($i = 1; $i <= $total_pages; $i++){
if($page == $i){
echo "$i ";
}
else {
echo " <a href=\"http://pluswomen.thefreestuffvault.com/moresear-{$search}-$i.htm\">$i</a>  ";

}
}

// Build the next link
if ($page < $total_pages){
$next = ($page +1);
echo " <a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$next.htm\">Next</a>";
}

// Close off the links
echo <<<HTML
</td>
</tr>
</table>
HTML;
}

// Lets start creating tables and echoing code
echo "<table>\n";

$c = 0;
while($row = mysql_fetch_array($result_main)){

// make the variables easy to deal with
extract($row);

if($row["prodSale"] < $row["prodPrice"] and $row["prodSale"] != NULL){
$price = $prodSale . " - Sale Price";

}
else {
$price = $prodPrice;

}

// open row if counter is zero
if($c == 0){
echo "<tr>";
}

// echo the individual cells
echo <<<HTML
<td align=center><table class="quick" width="180" height="350" border="0" align=center cellspacing="0" cellpadding="0">
<tr>
<td align=center><a href="http://pluswomen.thefreestuffvault.com/searchproduct-{$ProductID}.html">
<img rel="nofollow" src="{$BigImage}" border="0" height=180 alt="{$Name}"></a></td>
</tr>
<tr>
<td align=center><p id="productname"><a href="http://pluswomen.thefreestuffvault.com/searchproduct-{$ProductID}.html">{$ProductName}</A></p></td>
</tr>
<tr>
<td align=center><font size="2">Price: $ {$price}</font></td>
</tr>
</table></td>
HTML;

// increment counter - if counter = max columns, reset counter and close row
if(++$c == $max_num_columns){
echo "</tr>";
$c = 0;
}
}

// clean up table - makes your code valid!
if($c < $max_num_columns){
for($e = $c; $e < $max_num_columns; $e++){
echo " ";
}
}

// If there's more than one page, show links
if ($total_pages > 1){
echo <<<HTML
<table>
<tr>
<td align="center">
HTML;

// Build the previous link
if ($page > 1){
$prev = ($page -1);
echo "<a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$prev.htm\">Previous</a>";
}

// Build the page numbers
for($i = 1; $i <= $total_pages; $i++){
if($page == $i){
echo "$i ";
}
else {
echo " <a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$i.htm\">$i</a>  ";

}
}

// Build the next link
if ($page < $total_pages){
$next = ($page +1);
echo "<a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$next.htm\">Next</a>";
}

// Close off the links
echo <<<HTML
</td>
</tr>
</table>
HTML;
}
?>
[/code]

me!

jerastraub · October 3, 2006

This isn't working either, still produces the same results.

This is the search i am using to test this:

[code]http://pluswomen.thefreestuffvault.com/search.php?search=blue+blouse[/code]

Whether I use the above or http://pluswomen.thefreestuffvault.com/search.php?search=blue+dresses I still get the same results.

The form code I am using is:

[code]<form method="GET" action="search.php">
<input type="text" value="Ex: Blouse" name="search" size="18" onFocus="clearBox(this)">
<input type="submit" value="Search">
</form>[/code]

So if a use puts blue blouse or whatever in the search field, I want is to diplay as you said earlier Printf.

Like I know there are only two flare types of dresses that i offer, when I submit the form for flare dresses, i get all the dresses.

moxicon · October 3, 2006

"printf" hinted at something very important that needs to be addressed. You MUST escape the values that get inserted into your query string. In this case, mysql_real_escape_string(). Whithout doing so, you may be opening your script up to SQL Injection attacks at worst, or a sometimes failing query at best. Please do not save this step for later.

jerastraub · October 3, 2006

They already are i thought:

[code]$temp = implode ( '|', array_map ( 'mysql_real_escape_string', $search ) );[/code]

alpine · October 3, 2006

It does split and search each one - however i've added some () in first where clause (it did however split as it was too),
and by echoing the $query you see what the final query looks like later on:
[code]

<?php

$start = 0; // only set here for visual purposes
$per_page = 10; // only set here for visual purposes

$search = htmlspecialchars($_GET['search']);
$sq = explode(" ",$search);
$aq = count($sq) - 1;
$ac = 0;
if($aq>0): $qq = "("; $qc = ")"; else: $qq = ""; $qc = ""; endif;
for($i=0; $i<=$aq; $i++)
{
if($ac == 0)
{
$query = "SELECT * FROM womensproducts WHERE $qq ProductName LIKE '%$sq[$i]%'";
$query .= " OR CategoryID LIKE '%$sq[$i]%'";
$query .= " OR Category LIKE '%$sq[$i]%' $qc";
}
else
{
$query .= " AND ( ProductName LIKE '%$sq[$i]%'";
$query .= " OR CategoryID LIKE '%$sq[$i]%'";
$query .= " OR Category LIKE '%$sq[$i]%' )";
}
if($aq == $ac)
{
$query .= " limit $start, $per_page";
}
$ac ++;
}

//$result = mysql_query($query) or die(mysql_error());

echo $query;

?>

[/code]

With a search-url like www.site.com/search.php?search=blue+blouse
this is what you get if you echo $query:
[code]
SELECT * FROM womensproducts WHERE
( ProductName LIKE '%blue%' OR CategoryID LIKE '%blue%' OR Category LIKE '%blue%' )
AND ( ProductName LIKE '%blouse%' OR CategoryID LIKE '%blouse%' OR Category LIKE '%blouse%' ) limit 0, 10
[/code]

Now, another issue might be that you need to match the exact frases "blue" and "blouse" - in that case you must remove all [color=red]%[/color] located around each $search inside the query string

If you use %$search% you will match on for example "nonblues" and "antiblouses"
If you use %$search you will match on for example "bluesbrothers" and "blouses"
If you use $search you will match only "blue" and "blouse"

And the htmlspecialchars will take care of db security

If you wrote this search-script yourself, you will be able to implement this and work your way.

printf · October 3, 2006

OK, I think understand what you want now.

You want to make sure that all words are in a certain row, but the words may not be in order!

search: blue+blouse

So match

blue blouse

or

True Blue, a blouse celebrating freedom...

or

blouse comes in colors blue, red, yellow

me!

jerastraub · October 3, 2006

Mainly I just need to results that match "blue" and "shirt" or "Brylane" and "bigshirt" to filter to the top.

Also is there are way for a use to do an exact search, like I see at alot of engines:

Ie. "Brylane" "bigshirt". I get an error when I try to do this with my current code.

alpine · October 3, 2006

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

jerastraub · October 3, 2006

After looking around and some other sites that have searches, they are all getting the same type of results.

So I think I will stick with what i've got.

Tks all!!!

Sign In

separating search words

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information