CS2623: Server-Side Programming
Weekly Materials - Week 10
Chapter 7: Working with Database and MySQL
Click on a topic to open a panel | Open All | Close All
Objectives
  • Study the basics of databases and MySQL
  • Work with MySQL databases
  • Define database tables
  • Work with database records
  • Work with phpMyAdmin
Assignments and Due Dates


Assignments and Due Dates: View
CS2623 Coding Standards - requirements for all class assignments

Required Readings
HIGHLY Recommended Tutorials

Personally, Lynda.com does a much better job of covering the material and uses a more to date version of phpMyAdmin. However, it is realized that some students may not be able to come to campus to watch the videos or will not be purchasing access to Lynda.com. The videos from Apollo Hosting cover the material using an older version of phpMyAdmin and are a usable resource. Also check Recommended Materials

 Lynda.com

Up and Running with phpMyAdmin by David Powers

1. What PHPMYAdmin Does (All videos)
2. Setting Up phpMyAdmin Locally

HIGHLY Recommended:
Creating a database
Recommended:
Setting up a user account

3. Creating Tables (All videos)
4. Importing and Exporting Data

HIGHLY Recommended:
Exporting a database the quick way (Note: Even though Importing is listed first, I would watch exporting first since you will be exporting before importing.)
Importing data from a .sql dump file
Recommended
Creating a custom export for a single database (Note: Browse this one to gain a better understanding of the quick way)
Exporting partial data (Note: Browse since we will not be using this but it is good background information for future use.)

5. Running Queries

HIGHLY Recommended:
Performing a simple search
Using query by example (QBE) to perform a simple search
Using QBE to set multiple conditions in a WHERE clause
Joining multiple tables with QBE
Recommended:
Building a query manually on a single table
Building a query manually on multiple tables

Recommended:
PHP and MYSQL Essential Training by Kevin Skoglund
(Note: This are a good visual reference of the command line version shown in the text.)

13. MySQL Basics

    • MySQL Introduction
    • Create a database in MySql
    • Populating a MySQL database

15. Building a Management Content System

        • Building the CMS Database
Apollo Hosting

Additional PHPMyAdmin Videos (from Apollo Hosting which is not associated with our Apollo accounts. Uses a previous version of PHPMyAdmin but might be helpful.)

Most Helpful

    1. Creating MySQL database tables with phpMyAdmin
    2. Learning about MySQL databases and phpMyAdmin
    3. Exporting MySQL tables and databases with phpMyAdmin
    4. Importing MySQL tables and databases with phpMyAdmin

Also helpful

    1. Copying MySQL database tables with phpMyAdmin
    2. Renaming tables in a MySQL database with phpMyAdmin
    3. Inserting fields into MySQL database tables with phpMyAdmin
    4. Modifying fields in MySQL database tables with phpMyAdmin
    5. Deleting fields from MySQL database tables with phpMyAdmin
    6. Deleting tables from a MySQL database with phpMyAdmin
    7. Running SQL queries on a MySQL database with phpMyAdmin
    8. Searching a MySQL database with phpMyAdmin
Sample Code
None this week.
Recommended Materials
Required Software

PHPMyAdmin and MySQL which was included with your WAMP|MAMP download will be used for work with databases.

Chapter Questions and Test Reviews

Chapter 7: Review Questions (.rtf file)
Test 3: Review Sheet

Required Assignments
  • SSP08: MySQL Tables (Complete, upload to Apollo1, then submit URL in the LMS)
  • Weekly Quiz: Chapter 7 (Located in the LMS)
Professor's Class Notes and Comments

IMPORTANT:

The text uses the command line to create databases, tables and other related items. Please read through that material, but in this class PHPMyAdmin which contains a user interface will be used. In some ways, it is a bit primitive looking but it is free and it is easier than using command line. For this part of the course PHPMyAdmin MUST be used.

p. 428

Since the book only peovides a very brief introduction to phpMyAdmin be sure to

 

If you have not taken a database course (or CS1103 or CS2113) and not familiar with SQL (Structured Query Language) a PowerPoint is available in the Supplemental Materials panel which covers the basics which will be sufficient for this course. if you have used Microsoft Access was used as the DBMS (DataBase Management System). The database was saved as a file with a .mdb or .accdb file extension. This file could then be copied, moved, and so on. In the majority of other data base systems, there is not just one file that can be easily accessed. Each database is actually integrated into the system so the content of the data base must be exported and then imported. The process is a little more involved. Videos have been added to the course with appropriate instructions.

To access PHPMyAdmin on apollo1 the URL apollo1.occc.edu. On that web page, use the same login and password as you you have been using to FTP files. In this account there will be four generic databases: user#####a, user#####b, user#####c, and user#####d where ##### is replaced by your login number. These are the four databases you are to use for all of your course work. An example of how this will work is the text book may instruct you to create a database such as ChineseZodiac. You can create this locally and do the development on a local computer which (as the semester progresses) is preferable since it is usually faster. Once created, the tables in this local database will be exported to a .sql file and imported into your account on Apollo1. In other words, the tables from ChineseZodiac will be imported into one of the generic databases. For instance, the database may be named ChineseZodiac when accessing through localhost, but user#####b when accessing through Apollo1. I realize this is a bit confusing, but there should only be a very limited number of places in the code where the name of the database will need to be changed (and this will be annotated at the appropriate time). The names of the tables will remain the same so that is one less area for possible confusion.

This week we will concentrate on creating databases including appropriate tables, exporting the tables in a local database (localhost), and importing them into a database on a remote server (apollo1)