View Full Version : Search MySQL and then display results

06-30-2004, 04:32 AM
Alright - I think I've programmed myself into insanity tonight... Need some help.

I have a search page in which I can search by a keyword or by several "categories" plus the keyword for a more refined search.

Scenario is:
products.php (shows search if no product has been displayed)
dosearch.php (function script that performs the search by criteria passed from products.php)

I'm trying to search about 7 different columns for a match to the keyword and/or a match to the keyword + criteria.

First part of dosearch says if user has only put in a keyword, and not selected any other criteria, then do a full text search of everything.

if (!empty($search_text) && empty($category) && empty($mfg) && empty($subcat)){
$search = $search_text;
$sql = "SELECT * FROM products WHERE category LIKE '%".$search."%' OR mfg LIKE '%".$search."%' OR subCat LIKE '%".$search."%' OR name LIKE '%".$search."%' OR descr LIKE '%".$search."%' OR overview LIKE '%".$search."%' OR features LIKE '%".$search."%'";
$result = mysql_query($sql) or die(mysql_error());
$rows = mysql_num_rows($result);

And I'll stop there so not to have a 100 page post. Now - my question is, how do I sort by the "closest match" or "best match"...

#2 - the search is bound to return more than 1 result. How do I iterate through those results back in products.php so that I can show all the products that the query returned?

Do I have to run another query back in products.php or can I iterate the results and use an array... I'm kind of stuck.

Any help?

06-30-2004, 09:44 AM
I didn't understand your explanation.

You are talking about a full text search, wut your query just uses a regular where clause with a like-operator.
more info on full text search http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

there is a big difference against keyword searching and 'contextual searching'. To order the results of a keywordsearch, you'll need to have some sort of algorithme that specifies the weight that each match should get. 1 point for each found keyword? 1 point for each field with a keyword in? 5 points if the keyword is in the titlefield and 1 point in the other fields?

Its all possible, but you'll need to tell us what exactly you need.
Maybe these clarify some things

06-30-2004, 01:34 PM
Alright RAF..

I knew my query was bunk. LOL.

I am looking to do a full text search... my table structure is:

id | productID | name | category | subCat | descr | image | overview | features |

A user has a form with option to enter a word/words to search by...

they may also enter word/words plus select whether to search within a specific category, manufacturer or subCat.

I like your suggestion about points, but I'm not sure how to do that.

So basically - I need a query that will search within productID, name, category, subCat, descr, overview and features when no other criteria are chosen and then order them by which one is the best match.

If they match productID or name, those would be .. I guess? big point matches. descr, overview and feature content matches would be big matches too, but not as big as the first two. finally - any matches to category, subCat and mfg would be last.

Does that make more sense?

06-30-2004, 02:16 PM
if you are using the mysql full text function (match () against()), then you can not influence the weight.
see first link in previous mail

If you wanna give points, like i outlined above, then the second link i posted should get you started. It will be something like

sql="select *, if(locate('". $_POST['searchkey']."', productID),7,0) as prod, if(locate('". $_POST['searchkey']."', name),7,0) as nm, if(locate('". $_POST['searchkey']."', category),3,0) as cat, (locate('". $_POST['searchkey']."', subCat),1,0) as sCat, (locate('". $_POST['searchkey']."', descr),1,0) as dscr FROM yourtable ORDER BY prod DESC, nm DESC, cat DESC, sCat DESC"

the numbers in bold will be the value of the resulting variable (prod, nm, ...) is the searchkey appears in the column (productID, name, ...). The other number is the value if the searchkey isn't included (0).

06-30-2004, 02:25 PM
Excellent - got it all in... now it gives me this error

Can't find FULLTEXT index matching the column list

but I searched for the term HP which I know is a match to the mfg field.

Here is my query:

$sql = "SELECT *, if(locate('".$search."', name),10,1) as name, if(locate('".$search."', productID),10,1) as productID, if(locate('".$search."', category),7,1) as category, if(locate('".$search."',descr),5,1) as descr, if(locate('".$search."',overview),5,1) as overview, if(locate('".$search."',features),5,1) as features, if(locate('".$search."',mfg),7,1) as mfg, if(locate('".$search."',subCat),7,1) as subCat, MATCH (name,productID) AGAINST ('".$search."') AS relevance FROM products WHERE MATCH(name,productID) AGAINST ('".$search."') ORDER BY name DESC, productID DESC, relevance DESC LIMIT 0,30";

$result = mysql_query($sql) or die(mysql_error());
$rows = mysql_num_rows($result);

06-30-2004, 02:30 PM
Urg - disregard... It would help if I altered my table for the fulltext search... It is 7:30 am... mind isn't here yet.

06-30-2004, 02:37 PM
Well that didn't work - I made each field an index with FULL TEXT...

Am I supposed to make one index with all fields in it?

Also - can I these fields be varchars and longtext and text... or do they all have to be text.


06-30-2004, 04:51 PM
should be one index with all variable from your match().

the columns must be char, varchar or text.

07-01-2004, 04:51 AM
Alright - last question...

Here is my SQL statement:

$sql = "select *, if(locate('".$search."', productID),7,0) as prod, if(locate('".$search."', name),7,0) as nm, if(locate('".$search."', descr),1,0) as dscr, MATCH (productID, name, descr, overview, features) AGAINST ('".$search."') AS relevance FROM products ORDER BY relevance DESC, prod DESC, nm DESC, category DESC, subCat DESC";

I guess I don't know if the results are correct... 'relevance' gives me a big fat 0... see the result below.. (I did a fulltext search on the word 'bob')

-> | id | productID | nam |category | mfg| subCat| prod|nm| dscr| over | feature | relevance |
-> +----+----------------+------------------+------------+-----+----------+------+----+------+------+---------+-----------+
-> | 4 | 0 | bobtest | bob | HP | test | 0 | 7 | 0 | 0 | 0 | 0 |
-> | 3 | hpcompaqdx2000 | HP Compaq DX2000 | Commercial | HP | Desktops | 0 | 0 | 0 | 0 | 0 | 0 |
-> | 5 | 0 | NULL | NULL | | | 0 | 0 | 0 | 0 | 0 | 0 |
-> +----+----------------+------------------+------------+-----+----------+------+----+------+------+---------+-----------+

So - I'm wondering why I'm not getting a relevance #? Any thoughts?

07-01-2004, 05:15 AM
Can I delete the above thread? LOL... So I don't think well early in the AM and late at night.

THe problem was too short of a search word. :-)

I'm going to bed now!