search form query

[vinpkl]

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

[Dathremar]

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 ";
}

 

 

[vinpkl]

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

[Dathremar]

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?

[vinpkl]

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

 

 

[vinpkl]

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

[Dathremar]

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

[Dathremar]

$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

[vinpkl]

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

[Dathremar]

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

[vinpkl]

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

[vinpkl]

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

[Dathremar]

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

[vinpkl]

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

[Dathremar]

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.

[vinpkl]

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

[fenway]

Yikes... just match either and give each one a weight/score.