Searching from multiple tables

Anon

Hey,
I'm writing a search function, and all was going well but now I'm stuck. Please bare with me, I'm new.

The form. There are three checkboxes, which select which categories can be returned in the search (ie. if checkbox1 is checked category one is include, if checkbox 2 is checked, it's included as well, etc.). The rest is pretty standard.

The three categories are determined by the last four characters of the value in one column. Records that have "abcd" as the last four characters in columnA are one category, records with something else are another, etc. So...rather than having to process that string of each record for the search, I sorted the different categories into three separate tables. When the checkbox for that category is checked, it's table is added to the query. Easy enough.

So I came up with a query like this:

" SELECT * FROM tableA,tableB,tableC WHERE tableA.columnA LIKE '%$query%' OR tableB.columnA LIKE '%$query%' OR
tableC.columnA "

Worked fine. Now I have to sort, and that's where I'm stuck.

I want to sort by columnA, but if I use:

" SELECT * FROM tableA,tableB,tableC WHERE tableA.columnA LIKE '%$query%' OR tableB.columnA LIKE '%$query%' OR
tableC.columnA ORDER BY columnA"

I get an error: "columnA is ambigous". It doesn't know which columnA to sort by. So I need to combine the result of those searches, then search by the common column.

Any ideas?

Thanks, in advance.

Anon

My thought was to make a temporary table containing the results, and then select from that, but I can't get the syntax.

Thanks

fitzbean

thats confusing! why 3 tables? i dont fully understand what you are trying to accomplish.

Anon

Yeah...sorry, I didn't think I explained it very well.

Ok, the search is for domains. There are three checkboxes, so you can search for .coms, .nets, and .orgs, or any combination of them. However, I'm working with a pre-existing db and I can't modify it's structure for numerous reasons.

So, in order to be able to search by domain extension, I have to determine which domain each record belongs to. In order to do that I have to perform a string function on the domain name. Rather than doing that on every search, I thought it might make the search faster if I presorted the domains into three tables based on extension, and save doing the string function as well for the hundreds of records. Make sense so far?

So now I have three tables with identical structure. The search is conducted on any number of the tables depending on which checkboxes are checked.

That part I've got figured out. Where I'm having trouble is sorting the results of the multitable query.

A little more clear? Sorry for the confusion.