Search multiple tables

Looking to search through tables for content, categories, store items, store categories and more..

I have been playing around with joins doing a full join to join the content and categories together. That seemed to work, when i search with data assist if i do a AND + OR, for each title column in each table, it returns only one of the tables. heres the code.

$WADbSearch1->addComparison("title_con","".$_POST['searchbox'] ."","AND","Includes",0);
$WADbSearch1->addComparison("title_cat","".$_POST['searchbox'] ."","OR","Includes",0);

How do i set it up so that when it searches and finds results on each table, it will display the result?

I need to add probably 5 more tables into the mix after i get 2 working..

Do you mean tables or columns? Usually you would join multiple tables with a join statement, and then the search would search multiple columns of the joined table.

If you use the "keyword" type search in dataAssist search it will allow you to multiselect columns with a single comparison. That is probably the correct solution based on your description.

Well im looking for results for the keyword searched in content table, category table and product table.

So to add them all together and have data assist find keywords in each in 1 combined result, i would have to union them together right? Because there is no same columns that i can join each table to each other..

Yes you would have to union them together and use AS statements to obtain consistent column names.

SELECT content_name AS searchable_field FROM contents
UNION SELECT category_name AS searchable_field FROM categories
UNION SELECT product_name AS searchable_field FROM products

Then you could do a search based on searchable field.

Either that or you could add three separate recordsets and three separate DataAssist search Server behaviors onto the page with three separate repeat regions.

Ok sounds good, that i think will work great,

Now once i union them together, is there a way i can say order them all by or group them all by?

Im trying to find a way to make certain results more popular than others..

Yes, you can union them into a separate select statement and add an order by like:

SELECT * FROM (
SELECT
FirstField AS FieldName
FROM
FirstTable
UNION SELECT
SecondColumn AS FieldName
FROM
SecondTable) as newTable ORDER BY FieldName

Ray,

Everything you have suggested worked perfectly, i have am able to search multiple tables and fields using this method..

The only problem is after setting a union, dreamweaver or dataassist doesnt recognize the query, so i just copy and past out my query with a simple one to make adjustments to my search and then past it back in when im finished.

All works perfectly..

I do have one question though, when someone clicks on the search results page, i want NO results to pop up instead of ALL of the results ( for a blank search). How would i set this in dataassist?

That is the default search string setting. Just set it to "0=1" which should remove all results instead of the current "0=0" which returns all results.

Didnt seem to work,

How about when you search a space.. eveything comes up

You could add validation to prevent someone from searching with just a space. If you attach a copy of the page I could try to determine why it doesn't appear empty in an empty search.