Lab 08 - PHP/MySQL Start to Finish

Preliminaries

Work

    [ESTIMATE: Steps 1-5: 10-15 minutes]

  1. Download the mini conference data model and open it in ER Studio. (It is a portion of the model from the final project, as discussed in class.)

  2. Generate the physical model.

  3. Setup your ODBC Connection to your MySQL database. Use [your]_db5 database since it should be empty, right!(?) (Forgot how? See Lab 05.)

  4. Generate the SQL database.

  5. Open the MySQL Query Browser to verify your tables were properly created in [your]_db5 database.

    6. [ESTIMATE: Step 6: 5 minutes]

  6. Insert some data into the CONTACT relation. Here is a sample insert script. Here's how to do it:

    1. Download the sample insert script.
    2. Open the MySQL Query Browser and connect to [your]_db5 database (make it BOLD by double clicking on it).
    3. Verify that your tables are there as expected from the above steps (do you see contact, reviewer, review_group, speaker?)
    4. In the MySQL Browser, go to File > Open Script ... to open the script.
    5. Execute it!
    6. That should run every line and basically put a bunch of data into the CONTACT table.

    Congratulations! You now have a bunch of data to start with. And now you have a template you can use to do the rest as well! (HINT: If you are good at using Excel, creating insert scripts is not hard! Use the CONCATENATE function in Excel to turn a bunch of data in to INSERT statements! If you are good with Search and Replace in Wordpad, this works well too!)

    7. [ESTIMATE: Step 7: 1 minute]

  7. Query your data to verify it is there: SELECT * FROM contact

    8. [ESTIMATE: Step 8-10: 20 minutes]

  8. Add a new WebDAV Network Place connection to access your online files. (See Lab 07.)

  9. Now make a HTML FORM page to ask for the first name of some contact (e.g., be able to type in 'Aaron' into a form). (See Lab 07.)

  10. Now make a PHP RESULTS page to list all contact emails and phone numbers who have the POSTed first name (e.g., 'Aaron'): SELECT fname, lname, email, phone FROM contact WHERE fname = '[POST data here]' (See Lab 07.)

    11. [ESTIMATE: Step 11: several hours]

  11. Congratulations you are 50% done with your project! The rest is all downhill... all you have to do now is:
    1. complete the ER Diagram based on feedback and classroom hints
    2. generate your final project MySQL database (use an empty database like [your]_db4)
    3. make some insert scripts (trust me, making scripts is better than inserting bulk data manually in a browser)
    4. decide on which queries you want to implement
    5. write and test your queries in the MySQL Browser
    6. make PHP pages for your queries

    Couldn't be any easier, right? (Good luck!)

  12. P.S. Don't forget to backup your database from time to time using the MySQL Administrator backup/restore feature! (See Lab 05.)