Jump to content

Advanced Query, not working, and crashed mysql


DeanWhitehouse

Recommended Posts

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

 

 

Link to comment
Share on other sites

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;
?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.