CODEWITHSUNDEEP
phpmysqlsearch
Qchmqs
Qchmqs

Reputation: 1805

Querying for phrase with more than one space returns nothing

I have a searching form that the user should enter a word. Till now it works just fine but when I enter a word with 2 or more white spaces the query returns none (empty).

    $search = $_POST['srch'];

    if (!$search == "")
    {
    $con = mysql_connect('nnnnn', 'nnnnn', 'nnnn');
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
    mysql_select_db("nnnnnnn", $con);
    $search = mysql_real_escape_string($search);

    $sql = "SELECT * FROM `computers` WHERE `MODEL` REGEXP '$search|$search\$' OR
    `DESCR`\n"
        . "REGEXP '^$search|$search\$' LIMIT 0, 30 ";
    // here check if there is any content
if (mysql_fetch_array($result))

{

while($row = mysql_fetch_array($result))

{// here just echoing the returned content 

}

}else echo "not found";

    }echo "enter a word"! ;

So if I enter core 2 for example it retruns all the matching results.

But if I write core 2 dou it doesn't return anything, not even the "Not found error".

How can I fix this?

I tried the PHPMyAdmin by replacing the $search

and the query return matches even with core 2 dou and even with adding messy words like

core 2 dou computers

i am sure the problem is in the php way of sending the query or handling it i just cant figure it out

Upvotes: 0

Views: 94

Answers (2)

yitwail
yitwail

Reputation: 2009

Since you said any help would be appreciated, if you just want to search for a string, an easier way is to use LIKE instead of REGEXP, for example,

MODEL LIKE '%$search%'

also, I'm not sure why you're repeating $search separated by a '|'

incidentally, if multiple spaces are the problem, it's easy to replace with single space, like this:

$search = implode(' ', preg_split("/\s+/", $search));

Upvotes: 0

cdhowie
cdhowie

Reputation: 169478

You need to enclose the pattern in quotes. Whereas you have

"... REGEXP ^$search|$search\$ ..."

You need to have

"... REGEXP \"^$search|$search\$\" ..."

Additionally, you should escape $search at least using mysql_real_escape_string unless you want site visitors to be able to run random SQL against your database.

Upvotes: 1

Related Questions