Simple Search of mySQL in PHP

fifeclub posted this at 20:16 — 1st July 2004.

Joined: Feb 2001

I've got this simple mySQL database that visitors can browse via webpages using php. RIght now it's all by BROWSING only and I want to add a simple seach function to my page. [see current page here]

How do I do this? What I would like to do is have a field that a user can type in his seach query and then choose from a pulldown menu of options for what field they want to search for that term. For instance: Search database for "Smith" in "Last Names". Other field options may be All, Location, Class Year, Employer, Position, etc. (note that my actual field name that corresponds with the dbtable not be as English friendly as above)

andy206uk posted this at 08:28 — 2nd July 2004.

He has: 1,758 posts

Joined: Jul 2002

You need put the searchbox on a page and pass the $keyword variable to your search form, then simply use the following sql to find results ordered by relevence (like a proper search engine).

SELECT *, MATCH (fields,you,want,to,search) 
	
		AGAINST ('$keyword')
AS score 
	
		FROM `tablenamehere` 
				
	ORDER BY `score` 
	
	DESC
	"; 

Then just loop through the results and display as required. I hope that's clear enough.

Andy

cmonkey posted this at 04:37 — 3rd July 2004.

He has: 19 posts

Joined: Jul 2004

If you are allowing the user to enter a search term I would suggest using LIKE in your query:

<?php
$query = \"SELECT fieldname1, fieldname2, FROM tablename WHERE $field LIKE '%$term%'\";
?>

Its rairly a good idea to do a SELECT * query as stated above because it eats up a lot of server resources depending on the size of your database.

the LIKE statement will do a case-insensitive comparison between the $field value submitted (the field they are searching) and the search term they enter. The % signs server as wild cards; so if any part of the value stored in that field contains whatever is between the two % signs it will be returned.

you can get more info on mysql.com

hope that helps

andy206uk posted this at 13:08 — 3rd July 2004.

He has: 1,758 posts

Joined: Jul 2002

True... but LIKE just returns all results that meet the requirements. MATCH orders them by relevance.

cmonkey posted this at 15:31 — 3rd July 2004.

He has: 19 posts

Joined: Jul 2004

True but I'm pretty sure MATCH will only work on fulltext indexes, here's a little snippet from mysql.com:

Quote:
The MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). The search is performed in case-insensitive fashion. For every row in the table, MATCH() returns a relevance value, that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

the rest of the info can be found here: http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

anyways, obviously there are multiple ways do different types of searches so I would just choose whichever is easier for you to set up, it doesn't sound like you need to do anything elaborate with it.

dudcore.net posted this at 17:35 — 6th July 2004.

They have: 2 posts

Joined: Jul 2004

I second cmonkey here. Exactly how I'd contruct the query as well. It all comes down to taste and coding style I suppose....

Anonymous posted this at 15:41 — 10th August 2004.

They have: 5,633 posts

Joined: Jan 1970

Hello,

I agree with you cmonkey, but andy206uk has an easy way to add relevance to the search results. How do you this? Does anyone has an example to share?

Also I need to search multiple database tables.. how do I do this? Do I need to put my results in an array first?
An third problem, I would like to be able to search for multiple keywords.. Any idea?

Thanks,

Mark.