MySQL single table assignment
In this assignment, you make a web page that displays data you have imported from a CSV text file into a MySQL database table.
What you will do:
- Find a CSV text file with data you find interesting
- Import this data into a MySQL table
- Make a web page that displays the data in this table
- Show the results of 10 different queries on this page
Find a CSV text file with data you find interesting
About your data:
- Pick a data source that interests you
- The CSV you choose must contain more than 300 records
- No two students can use the same data source
On your web page:
- Include a heading for the entire page
- Include two sentences describing what your data represents.
- Include two sentences describing what you find interesting in this data
- Provide a link to the original data source
Import this data into a MySQL table
About your MySQL table:
- Include an 'id' field that holds a unique identifier for each record
- An auto-incrementing unsigned int field
- Include a 'created' field that holds the date and time at which every record was created in the table
- A timestamp field that defaults to the current timestamp
- Create other fields using data types that make sense for your data
- If you expect any of the fields in the table to contain NULL values, be sure your field settings reflect that
Make a web page that displays the data in the table
Use good design: Follow one of the conventional web page layouts
Show the table structure:
- Include a sub-heading for the "table structure" section of the page
- Include a table showing all the fields in your MySQL table and their data types and any additional settings
- Include a one-sentence justification of the data type and settings you used for each field
Show the results of the 10 queries:
- How many records there are in your table
- Logically group the data in your table and print out only the last (or highest) record in each category.
- Display three important fields; at least one should be in alphabetical order; show only the first 10 records in the table.
- Use GROUP BY to find the averages on a numerical field in a reasonable breakdown?
- Create a user-friendly listing of the first 15 records in alphabetical order.
- Write five more queries that are relevant to your data and and yield interesting results.
- Display clear sub-headings above the results each query
- Display the SQL query itself and the results of the query in well-formatted, well-designed HTML and CSS
- Use PHP to run the queries
- Follow the standard PHP method for reading from a MySQL database and looping through the results
Submit your work
Submit the following:
- the URL of your application on the web
- a zip file of all the files used by your web page
- A link to the original data source