posted
I want to create a database of human skeletal collections, and so far I have the basic idea of the page created ( located here). I'm using PHP to generate the list from an SQL database. What I want to do is allow the user to choose which entries to display based on region and time period. For instance, if the user wants to see only modern Asian collections then they select "show all Asian" from the region drop down menu and "show all modern" from the time period drop down menu and then click submit, directing them to a page that lists only those collections. Is there a way to do this? I'm assuming there is, but I've searching for about an hour and can't find anything. Keep in mind my knowledge of PHP and mySQL is pretty small. Thanks in advance.
Posts: 1658 | Registered: Sep 2003
| IP: Logged |
posted
Depending on what you mean by 'region' (and 'time period', though that's simpler), this might not be very easy.
Can you quality all of what you mean by region? For instance, do regions overlap or nest? How many regions would there be?
The basic sketch of how you might do it, assuming the simplest possible time period and region setup (that is, every time period or region related to a skeleton has to be attributed manually; there is no inference based on intersection of time periods or regions), is to make two new tables, one for time periods and one for regions. Each would be related many to many with your primary skeletons table with a joining table.
Then, when someone selects a time period and region, you would join each of those tables to the skeleton table, something like this:
code:
select column, names, here from skeletons inner join region_to_skeleton using (skeleton_id) inner join region using (region_id) inner join time_period_to_skeleton using (skeleton_id) inner join time_period using (time_period_id) where region_name == "asia" and time_period_name == "modern"
Note that this is not necessary the optimal database design, even for the simplest case, but it is a simple one. In particular, the ways it uses ids and names could be further refined.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
Each skeletal collection would be assigned one region and time period. At the moment I have both US and North American (Canada, US, and Mexico), but if that's too complicated I could change it. My idea with that was that "show all US" would just show the US while "show all North American" would show collections from Canada, the US, and Mexico. The current set of regions I have is US, North America, Central America, South America, Europe, Asia, Africa, Middle East, and Pacific. The time periods I have are modern, historic, and prehistoric. Aside from those three, the show options for time periods would also include "show all non-modern" which would be historic and prehistoric combined. Does that make sense?
I kind've understand your example, but I'm confused about how the region and time period get from the drop down list over to the mysql code. would it be possible to have region and time period as two extra rows in the current table (skelcoll) and have some code like SELECT name, institution, location, description, reference FROM skelcoll WHERE region='whatever' AND timeperiod='whatever'?
Now after I've stepped away from the computer for a while I'm thinking that check boxes might be better. The user could select whatever regions they want and then whatever time periods they want, and the page would display just those? Is that easier than the drop down lists?
Thanks for your help!
Posts: 1658 | Registered: Sep 2003
| IP: Logged |
quote:I'm confused about how the region and time period get from the drop down list over to the mysql code.
There are at least two ways to do this.
You can populate the items in the dropdown list from separate tables; this requires that you have one table for regions, one table for periods, and one table for the actual data.
Alternately, you can keep all the data in one table, and use a separate query for each dropdown. (These queries would look something like "SELECT DISTINCT REGION FROM SKELCOLL" and "SELECT DISTINCT PERIOD FROM SKELCOLL".) This approach would show only those regions and periods with skeletons in them.
Either way, if you want to be able to do "all non-modern" and "all North American," you're also going to need to programmatically add at least one item to each dropdown list, since those groupings won't exist in your underlying data.
Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
Unless you make them exist in your underlying data
How much data is going to be on this site, approximately? I assume it won't need to be updated terribly often. Also, how many visitors are you hoping to accommodate?
Also, you're answering the opposite direction, Tom.
Think of it this way: when someone selects an event/time period pair on your page, whatever values you assigned that event and that time period in your HTML will be available to your PHP code. At that point, you can put them in a query in place of the region and time period names and get the selection of related skeletons.
In order for your HTML to have the right information, you'd do a couple select statements to get the options for region and time period names (or just one, which would be more efficient, but that doesn't matter too much for this).
Since you want each piece of information to be available for multiple places, it probably makes sense to use a schema as I outlined: two additional types of entities (places and times), and four additional tables (two for the entities, and two to join the entities with skeletons).
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
Thanks for the help, Tom and fugu. I think I have it working pretty much how I want it to now.
quote:Originally posted by fugu13: How much data is going to be on this site, approximately? I assume it won't need to be updated terribly often. Also, how many visitors are you hoping to accommodate?
I don't think the database is going to be that big. Not more than 100 collections, I'm guessing. I'm not really sure how many visitors. Not that many, since it would just be used by people studying or interested in skeletons and only occasionally or rarely when they do use it. Certainly not anything like the number of visitors Hatrack gets.
Posts: 1658 | Registered: Sep 2003
| IP: Logged |
quote:Also, you're answering the opposite direction, Tom.
I'm not sure what you mean by that, unless you mean that I was focusing on the front-end and not the back-end.
I was, on the grounds that the data structure is simple enough that, at the end of the day, her back-end really won't matter unless she plans to expand it later. Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
She asked how to get from the form to what comes out of the database, not how to get from the database to what appears in the form. These are closely related topics, of course.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
Printer-friendly view of this topic
![[Wink]](wink.gif)
![[Smile]](smile.gif)
