Dataassist Search using more than two tables.

Using:
Dreamweaver 8
Web Developer Suite
MySql + php
Webassist e-commerce Database (new release) with modifications.


Database:

Products table has had categories ID column removed, otherwise unchanged.
Product Categories is unchanged
Product Ceremonies Added (same function as Product Categories)
ProductsToCategories Table added (Contains Product ID & Category ID)
ProductsToCeremonies Table Added (Contains Product ID & Ceremony ID)

So: I have PRODUCTS that appear in MANY CATEGORIES AND MANY CEREMONIES

Now: I've created my results page (From The Products Table) and the corresponding Details Page - They are working fine.

I've then followed the document for running Datassist Search and have hit the following problem:

In the example the product ID and Category ID's are related in the Products TABLE. So the example shows adding the recordsets required on the Results & Search pages and them binding the form elements to these recordsets.

So far so good.

However I'm new to this and a little up against it! I need to create a search page (I may also want to add this search facility to the results page at a later date) that allows a user to select / filter products by CATEGORY, by CEREMONY, By PRICE RANGE. I need to understand how to create multiple search criteria, add the appropriate Recordset and bind the correct elements to the page.

I'd very much appreciate your help.

Kind Regards,
Mark Martin.

Using these multiple criteria is no different than using a single criteria in the DataAssist Search server behavior, you can just add as many as you need for the search you are performing.

The trick is that you need to have the recordset on the results page setup to select all of the possible records you would like to search through. Then you use your new criteria that you add to the DataAssist Search server behavior to filter this recordset so that it only returns the desired results.

Do you have the recordset crafted so that it will return all the results? If not this is the first place to start. Next you would add in the additional search criteria to filter this recordset. Please post back with any questions that you have about any part of this.

Eric, Thank you.
To clarify: The search page needs to contain any recordsets with the data I'm searching, the results page only needs to contain recordsets for the data to be displayed.

Also and perhaps this is not the correct thread for this, but I'd appreciate your response - I'm getting an error when attempting to display the page on the web. Locally the pages work just fine.
There is no error from the webserver (all the non-DB drived pages (still php) are fine). The full URL of the file is displayed, I get simply a blank page wil no source code.
So I summized at there was a permissions issue: I've checked the error log on the server and found the following:

PHP Warning: main() [<a href='plugins/function.main'>function.main</a>]: open_basedir restriction in effect. File(/WA_eCart_Definition_PHP.php) is not within the allowed path(s): (/var/www/vhosts/MYDOMAIN.net/httpdocs:/tmp) in /var/www/vhosts/MYDOMAIN.net/httpdocs/WA_eCart/CCL_Cart_PHP.php on line 2
PHP Warning: main(WA_eCart_Definition_PHP.php) [<a href='plugins/function.main'>function.main</a>]: failed to open stream: Operation not permitted in /var/www/vhosts/MYDOMAIN.net/httpdocs/WA_eCart/CCL_Cart_PHP.php on line 2
PHP Fatal error: main() [<a href='plugins/function.require'>function.require</a>]: Failed opening required 'WA_eCart_Definition_PHP.php' (include_path='.:')

Your thoughts?

Regards,
Mark.

Regarding the search there is no need for recordsets to be present on your search page, unless you are using them to display choices for the user. It is the recordset on the results page that is returning all of the results that will be filtered by the values that were submitted on the search page.

As for the error you are getting this has to do with the open_base_dir setting in the php server. When you are using safe mode this setting will restrict the files that can be included in a php page. With this setting in effect you will only be able to use include files that are in the directory specified in this setting.

To check on this setting you can create a php info page. To make a page like this you just need to add this line of code to the top of any php page:

<?php phpinfo(); ?>

In here check for the open base dir setting to see what is listed as the allowed directory. If you want to include any files on any pages the files that are being included must be located here.

This is not normal to have this limitation in effect, you should speak with your server admin to have them turn this off so that you can use include files in your site without this limitation. If this is something that cannot be changed then you will need to add all of the files to include to the specified directory and update all of the paths in the include and require once lines in your php pages.

Eric,
Update: I now have a functioning results page (locally - see last post). However, after having carefully build new css layouts and attached them to my template pages to be able to create & destrypages without having to restyle each time, I now have a results section at the top of my original results page.
If I unbind the original elements (thumb, name, description etc) and rebind to my new recordset [the idea being my search results get dropped into my original page layout] I simply get no data displayed.

Hi Eric,
Thanks for this - The server is not running in php safe mode, yet the allowed directory is /tmp
I'll see if I can change this.

In regards to the data not being displayed on the page did you add your bindings to the existing results that were on the page? If the old recordset is no longer present you will not see anything, this is because there is a repeat region to show all of the records and this region is based on a recordset. If the recordset this region is based on does not have any records you will not see the results.

You can build your own results by dragging and dropping bindings from your working recordset onto the page where you would like to see them. Once they are on the page you can then apply the repeat region by selecting all of the bindings and going to Server Behaviors > Repeat Region.

If you have any further trouble with this please post back and include the results page itself so that I can take a look to see if I can spot any issues.

Eric,
I'm going to rebuild the pages and my recordsets.
When I have a result - I'll send you the page code - However I'd prefer not to post this.
Would you email me at my account email address and I'll send it through that way.

Thanks,
Mark.

Hi Eric,
I have full access to this server as it's a 1and1 dedicated server. I therefore have the ability to change whatever i need to.
The config is:
document root = /httpdocs

open_basedir= /var/www/vhosts/domainname.net/httpdocs:.:/php:/tmp

include_path = .:

What I need to know is what to change the open_basedir to to enable my includes to work - would you help me with this - it seems this must be a pretty common problem as as you partner with 1and1 - I'm sure you've come across this with them before.

Regards,
Mark.

If you have access to this setting in your php.ini file you will just need to comment it out, here is how mine looks on my testing server:

;open_basedir =

If this setting is commented out it should be ignored and include files can be referenced from any location. For the results pages you are working on let me know if you have having any further problems with these and if files need to be exchanged in private I can open a ticket for you.