Below is how the database is searched.

$query = "SELECT * FROM $tbl WHERE content LIKE \"%$var%\"";

$var includes the term or phrase searched for. But using the "LIKE" in the query doesn't seem to ignore whitespace or hyphens or commas (like i want it to)

E.g. if $var contains "helloworld" (what the user searched for) and the content row includes text which is either
"hello world" or "hello-world" or "hello,world"... it won't match it.

It also doesn't match substrings like if I searched "hello world" it wont match a phrase in the content such as "hello some words world"

Is there a way possible that I can work around this?

Recommended Answers

You can work around it if you split your var into multiple parts, so if your search key is hello world, split on the space (explode) and generate this query:

$search = explode(' ', $var);
$where = array ();
foreach ($search as $item)
{
  $where[] = " …
Jump to Post

Don't guess, know. Output the query, and the error it gives.

echo $query;
$results = mysql_query($query) or die(mysql_error());
Jump to Post

Quotes are missing around '%$item%' .

Jump to Post

All 9 Replies

Uhh, yeah. Still waiting on suggestions/solutions!

You can work around it if you split your var into multiple parts, so if your search key is hello world, split on the space (explode) and generate this query:

$search = explode(' ', $var);
$where = array ();
foreach ($search as $item)
{
  $where[] = " content LIKE %$item% ";
}
$query = "SELECT * FROM $tbl WHERE " . implode(' OR ', $where);

While that makes sense to me, it seems to be returning this error:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\.......search.php on line 53

Line 52 and 53 has:

$results=mysql_query($query);
 $pages=mysql_num_rows($results);

I'm trying to fix it ^^ but let me know if you know a fix

I think it's because this

$search = explode(' ', $var);
$where = array ();
foreach ($search as $item)
{
  $where[] = " content LIKE %$item% ";
}

returns the output as "Array" so the $sql query

$query = "SELECT * FROM $tbl WHERE " . implode(' OR ', $where);

doesn't know what to do with it

Don't guess, know. Output the query, and the error it gives.

echo $query;
$results = mysql_query($query) or die(mysql_error());

Aha okay. echo $query prints out

"SELECT * FROM WHERE content LIKE %wordSearchedFor% OR content LIKE %secondWord(substring)SearchedFor%  "

The other error, as described in the earlier post was:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\........search.php on line 53

The above seems like it is the main problem. Although it also also displays this

"Array" returned no results

from the code:

if ($total_pages == 0) 
  {
  echo "<p>Array &quot;" . $search . "&quot; returned no results</p>";
}

If you think seeing the full code will help you deduce something, please ask and I can send it to you!

Oh, and that "sql query or die" thing returns this too:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%firstword% OR content LIKE %secondword% limit 0,5' at line 1

Quotes are missing around '%$item%' .

Ahh yeah! That seems to solve it!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.