Jump to content

Archived

This topic is now archived and is closed to further replies.

jerastraub

separating search words

Recommended Posts

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 "&nbsp;<a href=\"http://pluswomen.thefreestuffvault.com/moresear-{$search}-$i.htm\">$i</a> &nbsp;";

      }
  }
 
  // Build the next link
  if ($page < $total_pages){
      $next = ($page +1);
      echo "&nbsp;<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 "&nbsp;";
  }
}

// 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 "&nbsp;<a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$i.htm\">$i</a> &nbsp;";

      }
  }
 
  // 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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 "&nbsp;<a href=\"http://pluswomen.thefreestuffvault.com/moresear-{$search}-$i.htm\">$i</a> &nbsp;";

      }
  }

  // Build the next link
  if ($page < $total_pages){
      $next = ($page +1);
      echo "&nbsp;<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 "&nbsp;";
  }
}

// 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 "&nbsp;<a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$i.htm\">$i</a> &nbsp;";

      }
  }

  // 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]

Share this post


Link to post
Share on other sites
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"?

Share this post


Link to post
Share on other sites
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!

Share this post


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

Share this post


Link to post
Share on other sites
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 "&nbsp;<a href=\"http://pluswomen.thefreestuffvault.com/moresear-{$search}-$i.htm\">$i</a> &nbsp;";

      }
  }
 
  // Build the next link
  if ($page < $total_pages){
      $next = ($page +1);
      echo "&nbsp;<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 "&nbsp;";
  }
}

// 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 "&nbsp;<a href=\"http://pluswomen.thefreestuffvault.com/moresear-$search-$i.htm\">$i</a> &nbsp;";

      }
  }
 
  // 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!

Share this post


Link to post
Share on other sites
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.



Share this post


Link to post
Share on other sites
"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.

Share this post


Link to post
Share on other sites
They already are i thought:

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

Share this post


Link to post
Share on other sites
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!!!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.