PHP/mySQL Search Function

[ziycon]

I'm have a search option on my site, i'm able to search one DB table no problem and display the results, my problem is i need to search multiple tables by this single 'search term' entered by the user. (Preferably by most relevant first as in percentages but i wont worry about that yet.).

First things first, can anyone point me in the right direction or advise how to go about this?

[Dormilich]

I don't know how you search your DB, but in most cases it should be possible to do requests to more than one table. see MySQL 5.1 Reference Manual :: 12.2.8.1 JOIN Syntax

regards

[Atli]

Hi.

You would have to either search each table individually, or you could JOIN the together and search them all at once.
Not knowing your database, I can't really say which is better for you.

As to getting the results sorted by relevance, if your tables use the MyISAM engine, you could try a FULLTEXT search.
(See MySQL :: MySQL 5.1 Reference Manual :: 11.8 Full-Text Search Functions)

[ziycon]

All table are being searched by the field title and i need to get the below fields from all four tables:

id, title, body

[ziycon]

I'm trying it on two tables but it doesn't look right(please bare with me, never used joins before):

$criteria is just the search item entered into the search field by the user.

$sql = mysql_query("SELECT COUNT(people.id) AS numrows1,COUNT(project.id) AS numrows2 
						FROM people,project 
						WHERE people.title LIKE '%".$criteria."%',project.title LIKE '%".$criteria."%'");