CS435 In-lab Exercise: DBMS, SQL and PHP (an intro)
In this exercise we'll jump start you on using MySQL and PHP. For
those of you who are experts already, there is a challenge problem at
the end we'd like you to work on.
If you are taking CS462 or have taken CS462, much of this will seem easy and familiar.
We urge you to start off helping the students with no experience,
acting as tutors, and the move on to one of the challenge problems.
On the challenge problems, you may work in groups. Zip up your
files and email them to the instructor with the names of your team, and
we'll demo the best or most interesting system. That team will
get a small but fabulous prize!
First, we have a database set up for you all to play with. Here are the details:
Database name: CS453
student user: cs453student
student pass: cs453pass
Second, you can log into a Web-based MySQL interface for this database
server using the login info about. Go to this link to access
Note that those of you using MAMP etc on your own machine have a phyMyAdmin installed there.
There is a nice one-chapter intro to databases and SQL in a Java
book I like. This is available on line if you are on Grounds or
using VPNLite. Here's the info:
The booK: Just Java, by Peter van der Linden.
Chapter 23 on Relational Databases and SQL. Direct link (I think).
Link to Safari to the entire book in its online version (must be on UVa network).
Beginner Activity #1
We'll ask you to create an example database table based on the example in the Just Java book.
(1) Log into phpMyAdmin
(2) We'll create a table from Just Java book example. Here's the SQL from the book:
CREATE TABLE Person (
name VARCHAR(100) PRIMARY KEY,
But you don't have to type in SQL. To create the table, click on the CS453 database you see in phpMyAdmin.
See the list of tables that are already there? At the bottom, there's
a form to create a new table.
You should name your table PersonJJxxx where xxx is your initials or
user-id. (So we can have lots of students create tables.)
(3) Select the table, and use the phpMyAdmin commands to look at its structure.
Use commands to Insert some information into that table. Add several rows.
Use phpMyAdmin commands to browse the database to see what's in it.
(4) Use the Search command to search your table for values that have a certain age or for Persons older than a certain age.
Look at the SQL that's generated by the form to see the structure of what the WHERE clause does.
Beginner Activity #2
We want you to write some code that reads data from the table you just
created. Also, modify this code to select rows with certain
Look at these examples and modify them to do things to your database table.
- Read data from a table: Execute the PHP script. Read the code.
- Do user authentication based on info in table. Execute the PHP script after filling in this form. Read the code.
Challenge Activity #1
Build me a student tracking system!
b) update information
a) Report of students by major / minor
b) Generate email list by major / minor
c) Enter comment about student
d) View info about student
e) Remove student
f) Remove students who have graduated
b) reset password
Ideas for data to be stored:
last name, first name, UVa email-ID
Major (BSCS, BSCpE, BACS or name of other), minor
First term at UVa
Last term at UVa (so we can remove info after this date)
date info last updated
list of comment entries (comment text, by whom, date)
login ID (UVa email ID)
date last logged in
Use of PHP sessions:
remember login id from form to form
remember if facutlty or if admin from form to form
Challenge Activity #2
Facebook user? Explore how Facebook can be used with PHP.
Go to this link: