DeanWhitehouse Posted August 13, 2009 Share Posted August 13, 2009 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 Quote Link to comment Share on other sites More sharing options...
DeanWhitehouse Posted August 13, 2009 Author Share Posted August 13, 2009 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; ?> Quote Link to comment Share on other sites More sharing options...
aschk Posted August 14, 2009 Share Posted August 14, 2009 Is it me or are you inserting $query into $query several times? Quote Link to comment Share on other sites More sharing options...
DeanWhitehouse Posted August 14, 2009 Author Share Posted August 14, 2009 The query isn't stored in a variable, that was just to make it easier to read for anyone viewing the thread Quote Link to comment Share on other sites More sharing options...
DeanWhitehouse Posted August 14, 2009 Author Share Posted August 14, 2009 Anyone any ideas/suggestions? Quote Link to comment Share on other sites More sharing options...
DeanWhitehouse Posted August 16, 2009 Author Share Posted August 16, 2009 Anyone? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 21, 2009 Share Posted August 21, 2009 Show us the actual query, not an intermediate version. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.