Searching Mysql Database

[jamesbrauman]

I have been trying to figure out how to do this but as of yet I haven't thought of a way. I have a mysql database containing four tables that I want to search. On the search page, there is a textbox for the query string, and four checkboxes - each of them relate to each table and whether or not to search that table. The HTML looks like this:

<form action="search_process.php" method="post">
					<p>						
						Search Query: <input name="q" type="text" class="text" /><br />
						<input type="checkbox" name="search_jokes" checked="checked" /> Search Jokes
						<input type="checkbox" class="checksecond" name="search_pictures" checked="checked" /> Search Pictures<br />
						<input type="checkbox" name="search_movies" checked="checked" /> Search Movies
						<input type="checkbox" class="checksecond" name="search_games" checked="checked" /> Search Games<br /><br />
						<input type="submit" class="button" value="Search" />
					</p>
				</form>

 

So basically, you can either search nothing, or everything, or a combination of those. I am receiving the variables okay, but do not know what is the best way to go about retrieving the results from the database. The four tables along with the fields I want to search look like this:

jokedata - title, joke

picturedata - picturetitle

moviedata - title, description

gamedata - title

 

I want the search string to broken into an array using space as the delimiter and it should search all of these words. I know how to do this:

$search_array = explode(" ", $_POST['q']);

But I don't know how I would go about using that exploded data in generated mysql LIKE's, searching all the fields I wrote about. But it would be generated as something like this I am guessing:

... WHERE joketitle LIKE "%searchterm1%" OR joketitle LIKE "%searchterm2%" OR joke LIKE "%searchterm1%" OR joke LIKE "%searchterm2%"

But that code doesn't just need to be for jokes, it needs to be for all the tables that needs to be searched, and using the fields I wrote above.

 

Once I have received my data I need a way to tell which table each row came from - jokedata, picturedata, moviedata or gamedata. The reason for this is that each row should have the id of the item, and on the search page I need to write a link like "viewjoke.php?id=$id" OR "viewpic.php?id=$id".

 

Also I would like paginated search results, but this is not necessary and I could probably research as to how to do this.

 

I am not asking for the complete code but I would like someone to point out a logical and efficient way to do this so I may try to write the code, and perhaps write up some numbered steps.

 

Thanks a million, I am awaiting your answers 

[n3ightjay]

2 trains of thought for ya... First is start with MySQL joins ... a quick google search says that 'left join' can combine multiple tables .. but i can't think of how to maintain the id.

 

the second would be 4 seperate querys for each query and just limiting the results to keep the page size small ... this would definately make maintaining the ids easier