[RESOLVED] Set min number of characters to match with "like" query in mysql?

[ElectricRain]

$dosearch = mysql_query("SELECT * from search WHERE keywords LIKE '%$searchterms%'") or die(mysql_error());

Im writing a basic search for a site.
All I want, is for it to search the keywords column and fetch any rows where any of the posted keywords match.

The problem with the above, I have discovered is if someone enters multiple keywords, but those keywords dont appear next to each other in the database it doesnt work.

Also if you just enter a letter, it brings up everything in the database. Eugh. I thought I could fix THAT bit if I can enter a min number of characters that must match the like query, is that possible?

Clearly writing a search is not as simple as I envisioned. Any clues on where to look for more info?

[bradgrafelman]

Well, first, give your keyword a min length requirement:

if(strlen($searchterms) < 4) {
     die('Search terms are too short, 5 char minimum.');
}

For the other bit, try replacing each space in $searchterms with a percent sign using [man]str_replace/man.

[ElectricRain]

Thats really clever, thanks... but what if my keywords go

readymade layouts designs complete....

and someone searches for "designs readymade"

instead of "readymade designs"

it wont work. Im guessing because its in the wrong order. Is there a way to fix that?

[shonuff]

So you just want to specify a minimum requirement of characters?

if ($_GET['keywords'] == "")
{
echo "<p>You failed to enter a search term or phrase. Please enter a keyword and try again.</p>";
}
//Checks if the keyword is least 4 characters long
elseif (strlen($_GET['keywords']) <= 3)
{
echo "<p>The term you are searching for must be more than 3 characters long.</p>";
}
//Check input
elseif (!preg_match('/^[-a-z0-9_!@# ]+$/i', $_GET['keywords']))
{
echo "<p>Illegal characters.</p>";
//Passed, Continue
}
else
{
//Go on searching the database...

Edit: Oops took too long to type a reply apparantly. :-)

Edit 2: Didn't realize this was a family forum, my apologies. :rolleyes:

[ElectricRain]

I got the min string length bit, thanks. And I dont need to check it.. I dont think? because its only a search, nothing is going into the database.

Now Im just stuck on getting it to recognise search terms that are entered in a different order than the keywords in the database.

[bradgrafelman]

shonuff - no need to use foul language...

ElectricRain, what you could do is split the keyword on spaces and search each word as a different term, like so:

$searchterms = 'designs readymade';
$searchterms = preg_replace('/[[:space:]]+/', ' ', $searchstr); // strip extra spaces
$search_array = explode(' ', $searchterms);

$sql_where = '';
foreach($search_array as $word)
    $sql_where .= '`keywords` LIKE \'%' . $word . '%\' OR ';

$query = 'SELECT * from search WHERE ' . rtrim($sql_where, ' OR');

[thorpe]

The first thing you'll want to do is break multiple search items into an array, check the laength of each element, then form a query based on all that.

if (!strstr($searchterms," ")) {
  if (strlen($searchterms) < 4) {
    echo "search term $searchterms is too short";
    exit;
  } else {
    $sql = "SELECT * from search WHERE keywords LIKE '%$searchterms%'";
  }
} else {
  $searcharray = explode(" ", $searchterms);
  foreach ($searcharray as $item) {
    if (strlen($item) < 4) {
      echo "seach term $item is too short";
      exit;
    }
  }
  $sql = "SELECT * from search WHERE keywords LIKE '%".implode("% OR %",$searcharray)."%'";
}

ps: Not tested (and more than likely not working) at all, but should give you some ideas.

[ElectricRain]

It works 😃

Thanks so much.

marks thread resolved

[bradgrafelman]

Just curious, which version did you go with? Mine? Thorpe's? A hybrid of your own mixing?

[ElectricRain]

Yours.. I didnt see Thropes till I refreshed the page again just now. I shall save it for future disecting though, Im sure itll come in handy at some point.

[thorpe]

Just fixed mine up a little. So it is actually working now, just as an example....

#!/usr/bin/php
<?php

$searchterms = "what will this find?";

if (!strpos($searchterms," ") > 0) {
    if (strlen($searchterms) < 3) {
        echo "the search term '$searchterms' is too short";
        exit;
    } else {
        $sql =  "SELECT * from search WHERE keywords LIKE '%$searchterms%'";
    }
} else {
    $searcharray = explode(" ", $searchterms);
    foreach ($searcharray as $key => $val) {
        if (strlen($val) < 3) {
            echo "the search term '$val' is too short";
            exit;
        }
    }
    $sql = "SELECT * from search WHERE keywords LIKE '%".implode("% OR %",$searcharray)."%'";
   }
   echo $sql;
?>

[ElectricRain]

Thanks 🙂

[Blulagoon]

@ :

if (!strpos($searchterms," ") > 0) {

In the revised example, why did you change it from strstr to strpos ?
Very useful little piece of code, definitely one for my archive 😃

Thanks - Blu

[Weedpacket]

It's a lot more efficient to just return the position where a string matches and see if it is greater than 0 than it is to create an entire new string that contains the search and everything in the original string that follows it, return that and see whether its length is greater than 0.

This is in fact mentioned on the [man]strstr[/man] manual page.

[Blulagoon]

Thanks. I only wondered because strpos gives an unexpected and undesirable result if the user accidentally puts a blank space in front of their first search term, while strstr returns the search term too short error message.

Blu

[Weedpacket]

No idea what the "unexpected and undesirable" result is (privatisation of the military forces?) but strpos() would be working as advertised - if the string starts with a space then strpos() would return 0.

This is why the [man]trim[/man] function was invented.

[ouija]

Hey guys, I'm new to this forum, but I came across this thread when I did a search trying to solve the same sort of problem... except the script I’m using is a little more complicated and me, having such little php knowledge, I couldn't get this script to work with your solutions.. so for now, I’m stuck on searching for single keywords! 🙂

Now my question is how do you tweak the code to return results for multiple keywords? how can you separate them and create the array, all while maintaining the other aspects of the code?

Let me know, you're help is greatly appreciated! 😃 😃