JimmyCrackedCorn
Posts: 583
|
| Posted: 09/08/2008, 9:24 PM |
|
Got a new project and unsure how to set this up in CCS. any ideas/thoughts/insights would be welcome!
new system is an interactive map
- ASP w/Access database using CodeChargeTools' Googlemap component
- each location on the map has lat/lon coords, name and a set of amenities
- the set of amenities needs to be variable so the site owner can add/delete/edit them
- the users of the site need to be able to search for locations based on these amenities
for example, here is how this should work,
- say the site launches with 2 locations
- the initial set of possible amenities are shopping, gasoline and restaurants
- site owner enters name, description, lat and lon for Location1 and then selects shopping as the only amenity
- site owner enters name, description, lat and lon for Location2 and then selects shopping and gasoline as the amenities
- when the site users search for shopping both Location1 and Location2 show in the results
- when the site users search for gasoline only Location1 shows up
- when the site users search for restaurants no locations show up
- site owner adds a new amenity called boating to the database and boating should automatically appear in the amenities search feature (but no results would come back since no location has this amenity assigned to it yet)
- site owner next adds boating to Location2 so now it has three amenities (shopping, gasoline and boating) and a search that includes boating would cause Location2 to be displayed
hopefully that all makes sense! I'm trying to get my head around the correct way to set up my database and my CCS app so I can 1) create an admin section that allows unlimited amenities and then allows any or all of these to be assigned to any or all locations and 2) create a public-facing section where users can search for locations based on the current list of amenities
my puzzle is what type of CCS controls should I use and what type fields should I use in the database? I figure cannot use checkboxes or radio buttons because the physical space required to display them will never be known ahead of time so I think I have to use some sort of scrolling list.
I'm thinking the amenities should be its own table with its primary key being used to connect to the locations table. but since a location could contain multiple amenities and an amenity could be used in many locations I am a little confused as to where to start. also the searching is confusing for me. how do I search the sites table for locations that contain certain items from the amenities table?
normally I'd just jump in and create something like this two or three times until I get it working but I'm in a time crunch and just wanted to see if anyone has any insights to get me started. Thanks in advance for any ideas!!
_________________
Walter Kempees...you are dearly missed. |
 |
 |
wkempees
Posts: 1679
|
| Posted: 09/09/2008, 4:10 AM |
|
Examples, will get you an idea as to the data entry part
Quote :
* Updating Many-to-Many via Multi-select ListBox
Using a multi-select ListBox to update multiple tables in a many-to-many relationship.
* Updating Many-to-Many via CheckBox List
Using a CheckBox List to update multiple tables in a many-to-many relationship.
* Updating Many-to-Many via Two ListBoxes
Using two multi-select ListBoxes to update multiple tables in a many-to-many relationship.
Steal the db-design idea from that as well 
You would need to have a many-to-many relationship.
Locations <-> keywords-per-location <-> keywords
Through a series of Joined Select you could then obtain all necessary combi's
locations per keyword (for one as well as multiple keywords)
keyword per location (same)
I would suggest synonymes as well, keywords pointing to keywords.
As to Data entry site owner side, the above examples would give ample methods.
As to searchin client side, you could have:
Open search textbox with autofill-prediction.
ListBox, listing all possible keywords (enhanced with the number of locations [gasoline - 20] )
Keyword clowd.
Oh man don't start me off.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 09/10/2008, 3:50 AM |
|
thanks for the thoughts Walter. right after posting this I had gone into the samples and figured it out from their many-to-many examples. I sometimes forget how helpful these canned examples are! but I really appreciate your quick and thoughtful response!!! you da man!
_________________
Walter Kempees...you are dearly missed. |
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 09/15/2008, 6:04 AM |
|
OK, this is going along pretty well! But, I have hit a bit of a snag and could use a gentle push in the right direction!
I'm using the following CCS example,
http://examples.codecharge.com/ExamplePack/MultiSelectS...electSearch.php
In that example multiple project selections result effectively in an OR condition; the result shows Employees who are assigned to 1st project OR 2nd project OR 3rd project etc.
I need it to be an AND condition so when multiple projects are selected it would only show employees who are assigned to ALL selected projects.
here is the SQL in the example,
SELECT DISTINCT employees.emp_id, employees.emp_name, employees.title, employees.phone_work, employees.email FROM employees LEFT JOIN projects_employees ON employees.emp_id = projects_employees.emp_id WHERE (emp_name LIKE '%{s_keyword}%') AND (projects_employees.project_id IN ({s_project}) OR '{s_project}'='0') ORDER BY employees.emp_id
and I think I need to change the section containing the IN statement since that is checking for items that match anything in the selection list rather than things that match everything in the selection list.
any thoughts on how to change this?
_________________
Walter Kempees...you are dearly missed. |
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 09/19/2008, 3:56 AM |
|
just wanted to publicly thank Yes Support for helping me resolve this (THANKS GREG!).
I submitted a support request and I was hoping for some advice or a just push in the right direction. what I got instead was a complete custom code snippet written in my language of choice!!!!! I modified it a tad to match my code, dropped it in and...presto! I'm back in business!!!
not only that, but by studying their example I have expanded my knowledge of how CCS works in ways that will be directly applicable to all of my projects!
most companies would have told me this was not a problem with their product (which would be true). a few companies would have told me in general terms how to use their product to solve my problem. but Yes solved my problem for me and educated me in the process!!
that is AMAZING customer service! Thanks again.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
|
