jamesbrauman Posted October 27, 2008 Share Posted October 27, 2008 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 Quote Link to comment Share on other sites More sharing options...
n3ightjay Posted October 27, 2008 Share Posted October 27, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.