Advanced Query, not working, and crashed mysql

[DeanWhitehouse]

Ok, a few problems with this query

 

First one, it said it cannot search as the data encoding is different for one of the tables to the other one. I think i have fixed this in mysql but can't be sure as i need this query to be perfect, as we took the server down with it last night :$

 

Second the query doesn't seem to work as i think it should

 

The Query

<?php
$query = "SELECT *, MATCH(product_title,product_description,cat_name) AGAINST ('+$query* *+$query' IN BOOLEAN MODE)AS relevance FROM `{$config['tables']['product']}`,category WHERE category.id = (SELECT main_cat_id FROM cat_product WHERE product_id = {$config['tables']['product']}.product_id) AND cat_name LIKE '%".$query."%' OR category.id = (SELECT sub_cat_id FROM cat_product WHERE product_id = {$config['tables']['product']}.product_id) AND cat_name LIKE '%".$query."%' OR product_title LIKE '%".$query."%' OR product_description LIKE '%".$query."%' ".(isset($order) ? $order : "ORDER BY relevance DESC")." ".$limit;

 

I need the query to search two tables;

The first table is "{$config['tables']['product']}", here it searches against the product_title and product_description.

 

The second table is categories, here it searches against the cat_name.

 

Now the hard part

The cat name to search against needs to be the category the product is in, so it needs to find the products category id from cat_product table then find the category name using the category id then search against the search criteria.

 

Complex

 

Any help and suggestions please?

 

Thanks,

Blade

 

 

[DeanWhitehouse]

Easier to read

<?php
$query = "
SELECT *,
MATCH(product_title,product_description,cat_name) 
AGAINST ('+$query* *+$query' IN BOOLEAN MODE) 
AS relevance
FROM `{$config['tables']['product']}`,category 
WHERE 
category.id = (SELECT main_cat_id FROM cat_product WHERE product_id = {$config['tables']['product']}.product_id)
AND 
cat_name LIKE '%".$query."%' 
OR 
category.id = (SELECT sub_cat_id FROM cat_product WHERE product_id = {$config['tables']['product']}.product_id) 
AND 
cat_name LIKE '%".$query."%' 
OR 
product_title LIKE '%".$query."%' 
OR 
product_description LIKE '%".$query."%' 
".(isset($order) ? $order : "ORDER BY relevance DESC")." ".$limit;
?>

[aschk]

Is it me or are you inserting $query into $query several times?

[DeanWhitehouse]

The query isn't stored in a variable, that was just to make it easier to read for anyone viewing the thread

[DeanWhitehouse]

Anyone any ideas/suggestions?

[DeanWhitehouse]

Anyone?

[fenway]

Show us the actual query, not an intermediate version.