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