need help with database setup and logic

[MWpro]

Ok so in the next few weeks I will be attempting to make a script which lets the visitor first select a U.S. State, then select a location within that state, and finally be able to read reviews and ratings about that particular location. Much like a product review system with categories. Very similar to "ratemyprofessor" actually.

/* If someone can recommend a good, free PHP script to do what I want, please let me know now, otherwise please proceed to my question below */

While I have experience with databases, I have never designed something so complex and need input on the design of the database. Would it be something like this:

Table name: ratings 
State ¦ Location ¦ Comments 
------------------------------ 
NJ ¦ Loc 1  ¦ Great location! 
PA ¦ PA Loc  ¦ Bad, do not use this location.  

And then after they choose their state, NJ for example, it will just SELECT * FROM ratings WHERE State=NJ ORDER BY DESC and then spit all of those options out on the page.

Then when they click a particular NJ location, Loc 1 for example, the next page would look something like this: SELECT * FROM ratings WHERE STATE=NJ AND LOCATION=Loc1 ORDER BY DESC and then spit out all of those comments.

Am I on the right path? Or do I need separate tables for each of the states? This will be a huge database with eventually hundreds to thousands of potential comments so I need to know the best way to attack this.

[edited by: MWpro at 4:20 am (utc) on Oct. 14, 2008]

[deMorte]

This is how I'd approach the situation.
You should create three tables. One with all the states. One with all the locations. And finally one with all the comments.

Table: State
Fields: ID, Name, Abbreviation, *More fields if needed*

Table: Location
Fields: ID, State_ID, Name, *More fields if needed*

Table: Comment
Fields: ID, Location_ID, State_ID, Comment, *More fields if needed*

Now when querying comments, you can do it either by State_ID or Location_ID. The point in this is that you can add, modify and delete locations and states. And with this approach you can have multiple locations with same names and you can distinguish them. (Wikipedia says there are 30 Springfields in United States)

Hope this helps.

[edited by: deMorte at 7:58 am (utc) on Oct. 14, 2008]

[MWpro]

Thank you for your response.

I assume to query I am going to have to have variables in the URL: like ....html?state=NJ&site=3

Am I correct in assuming this? If so, what is the easiest way to protect against malicious SQL injection?

[MWpro]

bump

[coopster]

It depends on how they will select the state, etc. Typically you use an HTML form and the method determines whether or not you will retrieve the values from the $_GET or $_POST superglobal [php.net]. Typically, the form method is $_POST.

One to to remember is that all user-supplied data is not to be trusted, as you have already mentioned you are concerned with SQL injection. The key is to check the incoming data for validity and then always be sure to escape any variables being used in your SQL query. For example, with MySQL you would use the mysql_real_escape_string [php.net] function.