View Full Version : How to Create a drop down list with states

12-10-2010, 11:57 PM
Hey everyone I'm new here and I have a question about the best way to execute this task.

I am trying to create a page with a dropdown list where the user selects the state.
A drop down list below that can have the specific schools to that state to choose from. Now I need a way for the user to click on a school and then be taken to a page that has website URL's from all the fraternities in that school.

Do I need php and mysql? I don't want to create a million pages unless I have to. What is the easiest way to do this. Maybe some kind of central form things where it outputs the fraternity you want at a particular school and state.

Also I am using tumblr but can just link it to a normal html or php page just throwing that out there.

Thanks guys!

Old Pedant
12-11-2010, 12:02 AM

Yes, you'll almost surely want to use PHP with MySQL.

Yes, it can be done all with two pages (one, actually, if you really wanted to...but I think that would actually make it harder).

The biggest part of this will surely be creating and populating the database. Dunno where you are going to collect all those website URLs from.

12-11-2010, 12:20 AM
People will submit them via email and I will populate them.

I am a novice so my next question is how to do what you described above. Just a brief description of the steps involved. And what is AJAX. Thanks!

Old Pedant
12-11-2010, 01:02 AM
AJAX: http://www.w3schools.com/ajax/default.asp

Phew. You are asking a lot.

How much of a novice are you??? You are talking about needing to know *FOUR* computer languages to do this:
-- JavaScript
-- PHP
-- SQL as used in MySQL

The very fact you didn't know what AJAX is makes me nervous.

Do you know *ANY* of those reasonably well???

12-11-2010, 03:33 AM
I took an intro to web development course so I know html and css and have a brief understanding of php javascript and sql databases. If you point me in the right direction I can figure it out. Thanks!

Old Pedant
12-11-2010, 04:08 AM

The first thing to do is design your database. Do you understand database normalization? Or do you think a database is another kind of spreadsheet? Either way works, but a la spreadsheet is less efficient.

I would assume you'd have these tables:
States: st, name ( e.g., 'CA', 'California')
Unis: unid, st, name (e.g., 37, 'CA', 'Pepperdine University')
Frats: fratid, unid, name, link (e.g., 173, 37, 'ABK', 'www.abkAtPepperdine.org')

Or something equivalent.

Assuming you only want to show states where you actually *have* schools and only want to show schools where you actually have fraternities, you'd get that by the query
FROM States AS S, Unis AS U, Frats AS F
WHERE S.st = U.st AND U.unid = F.unid

And use that to populate your <select> from PHP code.

Then, when the user chooses a state in the <select>, you will use AJAX to go back to PHP, passing the st value. I pointed you to W3C school's Ajax tutorial. I assume you read and mostly understood it. You will use that parameter to invoke another SQL query that returns the unis in that state and then the AJAX code will populate the second <select>.
SELECT * FROM Unis WHERE st = $st ORDER BY name

When the user chooses a Uni, you do the same thing, again. This time using AJAX to query for all frats at that Uni and populating a third <select>.
SELECT * FROM Frats WHERE unid = $unid ORDER BY name

Finally, when the user chooses an option in the third <select>, you'll direct him to the given Frat's web site. You don't need a SQL query for this if you designed the code right.

This is a one page model.

If you want a two page model, then when the second select is used, it sends the user to a second page that displays maybe information about the chosen school and has links there to all the frat web sites for that school.

Now all you have to do is code that. All.

12-11-2010, 04:54 AM
Thank you soooo much! I appreciate this. I'm going to take a crack at it tonight. I took a database concepts class too so that helps a bit. Thanks again!

12-11-2010, 05:17 AM
Do I need the university ID to be auto assigned uniquely each time a university is added and if so how. I vaguely remember this from an assignment. Is it the INT type when creating a field?

12-11-2010, 05:19 AM
Found the auto increment. just wondering if this is still necessary.


12-11-2010, 05:52 AM
How do I get the code to retrieve the information from the database. Is this found in the <script> I'm sorry Im very confused. Where in this do I tell the form to retrieve from the database. In the ajax tutorial it never has a command like SELECT from etc.


Old Pedant
12-11-2010, 07:44 AM
That's all done in PHP.

Didn't you notice that they had an AJAX example showing PHP supplying the data *back* to JavaScript?

JavaScript can't interact with a database directly (can't even interact with the server directly); it can only issue HTTP Requests (via that AJAX stuff) and receive the HTTP Responses.

Look more at w3c schools. Look at their PHP+AJAX stuff, as well.


No, you don't *have* to use auto_increment to get the ID's for the Universities and Frats, but it's usually the most convenient way. You do need something that is a unique ID, and the auto_increment takes care of that. Even for the states you need something unique as the id, but there the two-letter state abbreviation fills the bill: It's just as short as an integer (2 bytes, actually same size as a short int) and easier to use because it's a natural designator. It would be tough to come up with some as short and simple for the Unis or Frats. I, personally, would just go with auto_inc.