close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Search multiple tables

Thread began 7/22/2011 8:44 am by dave315749 | Last modified 7/26/2011 9:31 am by Ray Borduin | 1407 views | 9 replies |

dave315749

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..

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

dave315749

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..

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

dave315749

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..

Sign in to reply to this post

Ray BorduinWebAssist

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

Sign in to reply to this post

dave315749

Update

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?

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

dave315749

Didnt seem to work,

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

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...