vinpkl Posted June 12, 2009 Share Posted June 12, 2009 hi all i m working on search form. i have a product name "Nokia n97" in database. I m using the below query to search database and show result $qry="select * from product_table where product_name LIKE '%$model%' "; In this query model is the name of the search box. The problem is when i type "Nokia n97" in seach box then the result is shown perfectly but when i type "n97 nokia" then no result is found. what should i write in the query so if both words or any of two words match the "product name" in database the result can be shown. vineet Quote Link to comment Share on other sites More sharing options...
Dathremar Posted June 12, 2009 Share Posted June 12, 2009 Not sure if this is the best way but You could try: $search_parts = explode(" ", $search_text); $qry="select * from product_table where ("; $nb_parts = count($search_parts); for ($i = 0; $i < $nb_parts; $i++) { $qry .= " ( product_name LIKE '%". $search_parts[$i] ."%') "; if ($i == $nb_parts-1) $qry .= ")"; else $qry .= " OR "; } Quote Link to comment Share on other sites More sharing options...
vinpkl Posted June 12, 2009 Author Share Posted June 12, 2009 Not sure if this is the best way but You could try: $search_parts = explode(" ", $search_text); $qry="select * from product_table where ("; $nb_parts = count($search_parts); for ($i = 0; $i < $nb_parts; $i++) { $qry .= " ( product_name LIKE '%". $search_parts[$i] ."%') "; if ($i == $nb_parts-1) $qry .= ")"; else $qry .= " OR "; } hi Dathremar I tried your code. I entered "n97 nokia" in search box and it displayed the result of n97 and nokia both as we needed. Like if i enter "Nokia n97" or "n97 nokia" then "nokia n97 mobile phones is displayed in the end after all nokia phones. Is it possible to show the result with "nokia n97" or "n97 nokia" on top. I mean the product names which contain "n97" should be displayed on top and then other nokia phones should be displayed after "n97". vineet Quote Link to comment Share on other sites More sharing options...
Dathremar Posted June 12, 2009 Share Posted June 12, 2009 Hmm i am not that good with MySql :/ Adding a sorting of some kind would help here, but not sure what to sort them by. Can You tell me more info about the table structure please? Quote Link to comment Share on other sites More sharing options...
vinpkl Posted June 12, 2009 Author Share Posted June 12, 2009 Hmm i am not that good with MySql :/ Adding a sorting of some kind would help here, but not sure what to sort them by. Can You tell me more info about the table structure please? hi Dathremar i know how to sort with product_id but i dont know how to sort with product name or keyword entered into search box. in my table structure i have product id product name price image description shiping vineet Quote Link to comment Share on other sites More sharing options...
vinpkl Posted June 12, 2009 Author Share Posted June 12, 2009 hi dathremar can u tell me where should i write sort query as i m getting the below error mysql_num_rows(): supplied argument is not a valid MySQL result here is the code with sort query i m using $search_parts = explode(" ", $model); $qry="select * from product_table ORDER BY product_name DESC where ("; $nb_parts = count($search_parts); for ($i = 0; $i < $nb_parts; $i++) { $qry .= " ( product_name LIKE '%". $search_parts[$i] ."%') "; if ($i == $nb_parts-1) $qry .= ")"; else $qry .= " OR "; } vineet Quote Link to comment Share on other sites More sharing options...
Dathremar Posted June 12, 2009 Share Posted June 12, 2009 Dunno how deep are You in this project, but dividing the name field into something like manufacturer + model number will help here i guess. But even then I am not sure how are You going to order them by model cuz of simple reason You won't know which of the search words are the model number... Other idea: 1.Make a search with LIKE with all the search word by manufacturer 2. If a match is found, eliminate it from the search 3. Make a search string and search a match by the model with the other words 4. Display 1st the results from the 2nd query then from the 1st query. Another thought: Make UNION something like: qry1="(select *, ordered 2 from product_table where ("; qry2=" UNION (select *, ordered 1 from product_table where ("; $nb_parts = count($search_parts); for ($i = 0; $i < $nb_parts; $i++) { $qry1 .= " ( manufacturer LIKE '%". $search_parts[$i] ."%') "; $qry2 .= " ( model_nb LIKE '%". $search_parts[$i] ."%') "; if ($i == $nb_parts-1) { $qry1 .= ") "; $qry2 .= ") "; } else { $qry1 .= " OR "; $qry2 .= " OR "; } } $main_query = $qry1 . $qry2 . " ORDER BY ordered "; Quote Link to comment Share on other sites More sharing options...
Dathremar Posted June 12, 2009 Share Posted June 12, 2009 $search_parts = explode(" ", $model); $qry="select * from product_table where ("; $nb_parts = count($search_parts); for ($i = 0; $i < $nb_parts; $i++) { $qry .= " ( product_name LIKE '%". $search_parts[$i] ."%') "; if ($i == $nb_parts-1) $qry .= ")"; else $qry .= " OR "; } $qry .= " ORDER BY product_name DESC"; You need to add the order at the end of the query Quote Link to comment Share on other sites More sharing options...
vinpkl Posted June 12, 2009 Author Share Posted June 12, 2009 hi Dathremar i understood ur answer that i should first query with manufacturer name and then by product model number. But my database has been designed in such a way that we are not entering manfucturer name and model number separately. they are entered in single string as product name. i cannot query separely with model number, so i m left with no choice. Product name is entered as "nokia n97". so i have to set query with product name but i m not able to find a solution in which if "nokia n97" is entered or "n97 nokia" is entered then in both case the product names which have "n97" in their name should display on top. vineet Quote Link to comment Share on other sites More sharing options...
Dathremar Posted June 12, 2009 Share Posted June 12, 2009 If You can't change the table, I really am out of ideas how to order them by the % of the match. Maybe try out makeing different query for every search word entered and then count the words that matches and make a display of results by that, but imo that will be a lot of queries and not sure that is a good way to accomplish this. Better try googling for similar search scripts that can help You. Good luck Quote Link to comment Share on other sites More sharing options...
vinpkl Posted June 12, 2009 Author Share Posted June 12, 2009 If You can't change the table, I really am out of ideas how to order them by the % of the match. Maybe try out makeing different query for every search word entered and then count the words that matches and make a display of results by that, but imo that will be a lot of queries and not sure that is a good way to accomplish this. Better try googling for similar search scripts that can help You. Good luck hi Dathremar thanks for all the help. i will search for it. vineet Quote Link to comment Share on other sites More sharing options...
vinpkl Posted June 12, 2009 Author Share Posted June 12, 2009 hi Dathremar Like i need to query for product model but that not possible in my case. But Is their any solution by which i can match the numeric figures in the product name like "97" or "72" or "85" and then display the result. Like in nokia n97 the query should match or find "97" in product name and if found it should display result of nokia n97 phone. and also tell whether the query will be able to match "97" with "n97" or only with "97". vineet Quote Link to comment Share on other sites More sharing options...
Dathremar Posted June 12, 2009 Share Posted June 12, 2009 Hello again Yes You can check for words that have integer values in them and run the query with them, but again what if user enters for instance: Nokia n97 6100, then we get search words: Nokia; n97; 6100 We make the query like: (SELECT fileds_to_select, red 1 FROM product_table WHERE ( (product_name LIKE '%n97%') OR ( product_name LIKE '%6100%') ) ) UNION (SELECT fileds_to_select, red 2 FROM product_table WHERE ( (product_name LIKE '%Nokia%') ) ) ORDER BY red The result will have 2 rows for the 1st query and ie. 10 rows for second: Again we have problem cuz the Nokia 6100 may be the 1st result and then Nokia n97 ... It will be more accurate, but not sure if this satisfy Your needs Quote Link to comment Share on other sites More sharing options...
vinpkl Posted June 12, 2009 Author Share Posted June 12, 2009 Hello again Yes You can check for words that have integer values in them and run the query with them, but again what if user enters for instance: Nokia n97 6100, then we get search words: Nokia; n97; 6100 We make the query like: (SELECT fileds_to_select, red 1 FROM product_table WHERE ( (product_name LIKE '%n97%') OR ( product_name LIKE '%6100%') ) ) UNION (SELECT fileds_to_select, red 2 FROM product_table WHERE ( (product_name LIKE '%Nokia%') ) ) ORDER BY red The result will have 2 rows for the 1st query and ie. 10 rows for second: Again we have problem cuz the Nokia 6100 may be the 1st result and then Nokia n97 ... It will be more accurate, but not sure if this satisfy Your needs hi Dathremar If we work on a condition that user should only enter one product name at at time like "nokia n97" or "n97 nokia" then according to this it will show only n97 phone. i think this will solve my problem. but what will be the code to check and match the condition of numeric values. The below code will only show the result depending on the condition (SELECT fileds_to_select, red 1 FROM product_table WHERE ( (product_name LIKE '%n97%') OR ( product_name LIKE '%6100%') ) ) UNION (SELECT fileds_to_select, red 2 FROM product_table WHERE ( (product_name LIKE '%Nokia%') ) ) ORDER BY red but how will i match only the numeric values. like if "nokia n97" or "n97 nokia" is entered then i will match "97" numeric value in product name and show the result. if u can provide some code then i can play with queries and show result acordingly. vineet Quote Link to comment Share on other sites More sharing options...
Dathremar Posted June 12, 2009 Share Posted June 12, 2009 Is this search button only for mobile phones? If yes, split the search field into two fields and mark them manufacturer and model so then we can know which words to get priority in the search. If no, then I will tell You again that the thing You want to be done is not really easy to do as a matter of fact with the table You have not sure if You can do it at all without making a lot of queries and php mathematics which will really burden your server. Quote Link to comment Share on other sites More sharing options...
vinpkl Posted June 12, 2009 Author Share Posted June 12, 2009 Is this search button only for mobile phones? If yes, split the search field into two fields and mark them manufacturer and model so then we can know which words to get priority in the search. If no, then I will tell You again that the thing You want to be done is not really easy to do as a matter of fact with the table You have not sure if You can do it at all without making a lot of queries and php mathematics which will really burden your server. hi Dathremar no, the search form is not for only mobile phones. its for all the products in the database i.e. mobiles, cameras, memory cards etc vineet Quote Link to comment Share on other sites More sharing options...
fenway Posted June 15, 2009 Share Posted June 15, 2009 Yikes... just match either and give each one a weight/score. 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.