Building LIKE queries

Anon

I am having trouble making a mySQL query that will work to do the following. I have multiple fields that I want to build my query from. However, I don't want to search exactly, only if it contains the word. Also, one of the fields is a keyword search. Each keyword is seperated by a comma both in the search field and in the database. Do I need to search against those seperatly by using explode() to seperate the words from the commas?

Also, I get the following error when I attempt to see if there were no results so I can display "Sorry, no matches found"

Error:
Warning: Supplied argument is not a valid MySQL result resource in /home/sites/site19/web/php_test/works.php on line 343

Code:
if (!mysql_num_rows($result)){
... Display Message ...
}

Thanks,
Kyle

Anon

Sounds like your query is failing so you get no $result back. What is your query? Also print out any errors that occour when you run the query.

Anon

Rob,

Here's my query:
"SELECT * FROM works LIKE '%, " . implode(" AND ", $paramArray). ",%'";

$paramArray[] contains all the fields that are not empty.
Example:
if ($title1){
$paramArray[] = "title1='$title1'";
}

I don't think I have the LIKE structure setup correctly. But I got the same error when I took it looked like this:
"SELECT * FROM works WHERE " . implode(" AND ", $paramArray);

Thanks,
Kyle

junebug

a query using like is set up like so:

$q = "select * from table where column like '%val%'";

Anon

All right. Here's the code I'm using:
if ($title1){
$paramArray[] = "WHERE title1 LIKE '%$title1%'";
}
... etc ...

This is how I build the sql command:
$sql = mysql_query("SELECT * FROM works " . implode(" AND ", $paramArray) ."");

I ran the search and also had it output what the string was and I got this as the string:
SELECT * FROM works WHERE title1 LIKE '%Nothing%' AND WHERE title2 LIKE '%Ness%' AND WHERE category='Audio'

and I also got the following error:
Warning: Supplied argument is not a valid MySQL result resource in /home/sites/site19/web/php_test/works.php on line 344

... any ideas why I get the error? Also, the search works fine when I only enter data for one field, but when there is more than one field with info, then it returns nothing. Something wrong with the sql syntax I'd imagine, but everything looks ok to me.

Thanks,
Kyle

Anon

Ok first off SQL select statements can only have 1 WHERE clause. These are build using AND and OR clauses, but only 1 WHERE can exist so your SQL is failing because it is invalid. Your query should look like this.

SELECT *
FROM works
WHERE title1 LIKE '%Nothing%'
AND title2 LIKE '%Ness%'
AND category='Audio'

The extra WHERE's are causing the query to fail thus giving you an invalid resource for your $result.

Anon

Thanks a million Rob.