Welcome to WebmasterWorld Guest from 184.108.40.206
/* 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]
Fields: ID, Name, Abbreviation, *More fields if needed*
Fields: ID, State_ID, Name, *More fields if needed*
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]
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.