Integrating Databases into the Web with PHPMySQL

1
Integrating Databases into the Web with PHP/MySQL

• CS 4000

2
Getting Started

• PHP and MySQL are a powerful team for developing
  fully interactive database-driven Web sites.
• Starting with a Web server (like Apache), you
  simply download and install MySQL and then
  download and install and configure PHP on the
  server (in that order).

3
Intro to MySQL

• Once you have MySQL and PHP installed and
  configured for your server, you need to create
  the database to hold the information for your
  application.
• Go into the mysql folder on your server, then
  into the bin folder and type mysql.
• This begins the MySQL command interpreter (your
  command line prompt will change to mysqlgt).

4
Creating a database

• You can see what databases currently exist on the
  server with the command
• SHOW DATABASES
• To create a database, use the command CREATE
  DATABASE followed by the name of the database
  you want to create (simple, huh?). For example,
  to create a database to hold information on
  employees, you could type
• CREATE DATABASE employdb

5
Selecting a Database for Use

• You must then select the database so that you can
  enter data into it. To do this, use the USE
  command
• USE employdb
• Now you have created a database and selected it
  for use.

6
Tables for Data

• The next step is putting in some information to
  use in your queries for your Web page. This is
  done in the form of tables. You can see the
  tables associated with your database by using the
  SHOW command
• SHOW TABLES
• But, since you just created your database, there
  are no tables yet associated with your database.

7
Creating Tables of Data

• To create one or more tables for your database,
  you should first consider carefully
• what kind of information you want to include in
  the table, as well as
• the field type and length of each data element.

8
Creating Tables

• For example, to create a table named
  emp_position in the above database, with fields
  for employee number (9 characters), job title (20
  characters), job code (an integer), and hourly
  pay rate (a decimal value), we could do the
  following
• CREATE TABLE emp_position (emp_num VARCHAR(9),
  jbtitle VARCHAR(20), job_code INT, hrly_rate
  FLOAT)

9
Adding Data to Tables

• To verify that your table was successfully
  created, type DESCRIBE emp_position. Now you are
  ready to import data into your database. If you
  are starting a table from scratch, create a
  simple text file with tabs separating the values
  on each line, as below
•  
• 111223333 Janitorial Staff 1142 11.50
• 252069876 ICAPP Professor 5489 8.00
• 415998844 Web Designer 2001 25.00

10
Adding Data cont.

• With each column corresponding to the order in
  our table, we would have an employee 111223333
  who is a member of the Janitorial Staff, job code
  1142, whose hourly pay rate is 11.50, and so on.
  
• Save this text file as EmployeePositionList.txt,
  for example.

11
Loading Data into a Table

• Once you have created a tab-delimited text file
  (tabs separate the fields on a row), use the LOAD
  command to load the data into the database
• LOAD DATA LOCAL INFILE EmployeePositionList.txt
  INTO TABLE emp_position
• All of the data in the text file will be put into
  the table.
• If you are starting with a spreadsheet or other
  file of information that you need to import into
  the database, format it as a text document with
  tab delimiters and follow the procedure above.

12
Inserting Rows One at a Time

• You may also insert rows one-by-one, using the
  INSERT command
• INSERT INTO emp_position VALUES (997883333,
  ICAPP Student, 3131, 100.00)
• While you might not want to enter many values
  this way at the command prompt, a good use for
  this is to insert values from a form into a Web
  database using PHP, as is covered in the PHP
  section.

13
Retrieving Information SELECT

• To retrieve information from a database table,
  the SELECT statement is used. A general form of
  the statement is 
• SELECT fields_to_show FROM name_of_table WHERE
  condition_needed ORDER BY field_name_to_sort_by

14
SELECTing rows columns

• So, to show all fields from the emp_position
  table for employees whose pay rate is over 10.00
  per hour, sorted by pay rate, we would use
• SELECT FROM emp_position WHERE hrly_rategt10.00
  ORDER BY hrly_rate
• That should be just enough mySQL to give us what
  we need to do the database interface in PHP.

15
Next Time

• Well talk about PHP