Hi!
As I am fairly new to MySql and the PHP language you must forgive me for asking questions that might seem simple. This is my problem:

I am creating a search script for a webpage which has several tables with information to be searched in, for example:

Table CD, DVD, TECH etc

These tables has ID autoincrement and several other columns. To conduct a search in all tables in one query I have created a script that indexes these tables into a new table. The new table has the columns ID (autoincr) POST_ID (id from the indexed table), POST_TYPE (which type of table the index comes from, CD, DVD, TECH etc), INDEXTABLE (containing text from all columns in one chunk).

Now when I have the index table containing everything from all the other tables i can do a query on it and the result will be a recordset containing ID, POST_ID, POST_TYPE, INDEXTABLE. This is just fine, I could easily create a loop with links to a detailpage that checks the POST_TYPE and then gets the row with the POST_ID and display it. However, since the indextable has all the text/info in one column it is hard to display the result of the query in a nice way. So what I want to do is to create a query which gets all the rows from the indextable that matches the searchstring, and then match the result with the "real" tables and then create the list of results writing it out from the "real" tables. I know this could be done with JOIN but as I am a newbie I dont know how. I presume one has to match all the rows by POST_TYPE and then by POST_ID since the result from the query migth very well contain rows where the POST_ID's are identical, since they originate from three different tables. If anyone has the time please give me a hint on this. BR / bjarman.org

Sorry, I spent about 10 minutes trying to understand what you were trying to do. It may help if you give a small representative example of a couple of columns form each table (and their data), what you are searching for, and what result you would like to get.

It sounds like a basic select statement listing multiple fields, multiple tables and a where clause would work.

Hi and thanks for the fast response!
I solved my problem by using LEFT JOIN like this:

$query_indexresult = "SELECT searchindex.*, cd.*, dvd.*, tech.*, interviews.*, live.*, photo.*, blog.* FROM searchindex LEFT JOIN photo on searchindex.POST_ID = photo.ID LEFT JOIN blog on searchindex.POST_ID = blog.ID LEFT JOIN tech on searchindex.POST_ID = tech.ID LEFT JOIN interviews on searchindex.POST_ID = interviews.ID LEFT JOIN live on searchindex.POST_ID = live.ID LEFT JOIN cd on searchindex.POST_ID = cd.ID LEFT JOIN dvd on searchindex.POST_ID = dvd.ID WHERE INDEXTABLE LIKE '%$searchstring%' ORDER BY searchindex.POST_TYPE ASC";

Well, it might not be beautiful but ut worked for me, i joined all the tables represented in the searchindex table by their ID's, but that is only half of the solution since there can be identical id's depending on which table the id originates from. Later in the code i have a do loop that writes out the recordset, within this loop i have a case in where i check from what type of table each row in the recordset comes from and puts the id together with the type definition on a querystring linked to the detailpage. In this way I can conduct a search in the indextable but show the data from the original tables to the user.

BR // Bjarman.org