I've got an impossible select box valuelist

A place for users and developers of the Xataface to discuss and receive support.

I've got an impossible select box valuelist

Postby alec » Mon Oct 24, 2011 3:02 pm

It looks like xataface is a great piece of software, i've been playing with it for a few hours, and have hit a couple of problems...
My database is for events management - the tables are 'Cities', 'Teachers', 'Venues', 'Products', and 'Events'
A teacher can be in many cities, and can teach many products - these use a multi checkbox to the city & product id's
A venue can be in one city, but can host many products - use select box (saves city id) and multi checkbox (saves product id) respectively

The events table stores the city id, product id, teacher id, venue id, as well as other customer related data.

When a customer calls us to book an event, they'll first tell us the city they want the event in, then the product they want, and we find an available teacher and venue later.

My main problem is that when a customer phones and says the city, we want the 'products' select box to update to only show products available in that city (Products available in that city should be determined by matching 'is there a teacher that does this product and is in this city' and 'is there a venue in this city that does this product'); below is the version we had working in Access.
then update the teacher and venue select boxes with only matching data after a product is selected (ie teachers in this city who do this product and venues in this city that do this product).. I thought my mysql skills were ok, but I just can't crack this one - even what i thought were basic queries (below) are failing in xataface

Access version of the 'products available in this city' query which i'm failing in moving to mysql/xataface
Code: Select all
SELECT Products.product_label
FROM (Venues INNER JOIN Products ON Venues.[venue_products].[Value] = Products.[product_id]) INNER JOIN Teachers ON Products.[product_id] = Teachers.[teacher_products].[Value]
WHERE (((Venues.venue_products.Value)=[Products].[products_id]) AND ((Venues.venue_city)=[Location]) AND ((Teachers.teacher_city.Value)=[Location]) AND ((Teachers.teacher_products.Value)=[Products].[products_id])

Simple mysql query that seems to be failing
Code: Select all
SELECT teachers_id, teacher_name FROM teachers WHERE FIND_IN_SET('$event_city', REPLACE(`teacher_city`, '\n', ',')) > 0


Thanks for any help, or any pointing me in the right direction you can do
alec
 
Posts: 2
Joined: Mon Oct 24, 2011 2:38 pm

Re: I've got an impossible select box valuelist

Postby shannah » Mon Oct 24, 2011 3:10 pm

We have created a depselect module that provides such a dependent select list. The catch is that it is designed to work with Xataface 2.0 which is under development.

The docs for this module can be see at http://xataface.com/dox/modules/depselect/latest/

You can look through the source if you like to see how it was done.

Other than this it's not that hard to roll out your own dependent select system but it requires a little bit of javascript. If you look in the forum there are a few threads where people describe strategies that they used (and some code).... but it gets a little messy.

It could be a little while before 2.0 gets released as I've got a few other projects that come first...
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 12 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved