CECS 5100 PHP and Database

1
CECS 5100PHP and Database

• Dr. Jones

2
Review

• Assignment 2 - Example Posted
• Assignment 3 Questions ?
• Other Questions ?

3
Process

• Connect to Database Server
• Select Database
• SQL statements used to read and write from
  database

4
Connect to Database Server mysql_connect()

• Page 322
• lt?php
• conn mysql_connect ("elm.cecs.unt.edu",
  "cecs5100","cecs")
• echo conn
• ?gt
• Outcome
• Resource id 2

5
Connecting to Database

• Page 323, Listing 16.3
• lt?php
• echo "ltbgtMySql Connectionlt/bgtltbrgt"
• conn mysql_connect ("elm.cecs.unt.edu",
  "cecs5100","cecs")
• echo "Database --gt'conn'ltbrgt"
• mysql_select_db("gjones",conn)
• ?gt

6
SQL is how we talk to the database

• Structured Query Language
• SQL is an ANSI standard computer language for
  accessing and manipulating databases.
• http//www.w3schools.com/sql/sql_intro.asp
• is a good tutorial of SQL
• Create (new), Insert (write), Select (read),
  Delete (delete), Update (overwrite)

7
Creating a Table

• Page 323, Listing 16.4
• lt?php
• echo "ltbgtMySql Connectionlt/bgtltbrgt"
• conn mysql_connect ("elm.cecs.unt.edu","cecs510
  0","cecs")
• echo "Database --gt'conn'ltbrgt"
• mysql_select_db("gjones",conn)
• sql"CREATE TABLE gregTable (id int not null
  primary key auto_increment, testFied varchar
  (75))"
• result mysql_query(sql, conn)
• echo result
• ?gt
• Output result 1 if successful.

8
Database Fields

• Field Types
• Int
• Double
• Char
• Varchar
• Date
• Time
• Etc
• You need to know what the field type is before
  you write a value into it.
• Autoincrement allows us to assign a unique value
  that can be used later -- like for delete.

9
Navicat or Mysqlcc

• Navicat (trial)
• (http//www.navicat.com/)
• Mysqlcc (free)
• (http//www.mysql.com/products/mysqlcc/)
• These are database management tools.
• You can use them to manage and examine what your
  database tables are looking like.
• After you create your examples table you can see
  if it worked with this software.

10
Insert

• Page 327, Listing 16.9
• sql INSERT INTO testTable values ( , some
  value)
• result mysql_query(sql,conn) or dir
  (mysql_error())

11
Select

• Page 330, Listing 16.11
• sql SELECT FROM testTable
• result mysql_query(sql,conn) or dir
  (mysql_error())
• number_of_rows mysql_num_rows(result)
• Then use while loop to examine all

12
Delete

• sql DELETE FROM testTable WHERE testField
  Jones'
• result mysql_query(sql,conn) or dir
  (mysql_error())
• This would find all records in the database that
  have Jones in testField and delete the entries.
• The reason we have ID which is an auto increment
  field is to use ID to delete records and avoid
  deleting more than one record.

13
Update

• sql UPDATE testTable SET testField 'Nina
  WHERE ID 1
• result mysql_query(sql,conn) or dir
  (mysql_error())
• This would find a record with ID 1 and then
  update the testField to be Nina.

14
Error messages

• Page 324
• mysql_error()
• Line 10 as an example
• result mysql_query(sql,conn) or dir
  (mysql_error())
• This will tell you what problem you might have

15
Assignment 4 - Part 1

• MEMBERSHIP Table
• create a table named MEMBERSHIP
• Membership (ID int not null primary key
  auto_increment, First Name varchar 75, Last
  Name varchar 75, Address varchar 75, City
  varchar 75, State varchar 75, Zip varchar
  75, Phone varchar 75, Email varchar 75,
  Password varchar 75, Admin int)

16
Assignment 4 - Entry Page

• Upon entry to the page, start or resume a session
  ID.
• The entry page will present a group or store
  front (of your own creation). Graphics, What we
  do, etc.
• It will provide the user with the following
  links.
• New Member
• Membership Information
• Admin
• Discussion Forum

17
Assignment 4 - New Member

• Present the user with a form asking for
• First Name, Last Name, e-mail address, etc
• present a link back to the first page.
• upon form submission
• Check to ensure all fields have been entered
• then check the above fields against the
  MEMBERSHIP table
• If the user exists, then inform the user that
  they are already a member and provide them a link
  to the membership information page.

18
Assignment 4 - New member

• if they are not in the MEMBERSHIP_FILE
• Generate a password (it doesn't have to be secure
  for this assignment)
• append to the MEMBERSHIP_FILE the new members
  information in the format as provided for at the
  top of the assignment. First Name, Last Name,
  e-mail Address, and the password you just
  assigned -- all seperated by commas.
• present the user with a page thanking them for
  joining and inform them what their new password
  is.
• provide them with a link to home or to the
  membership information page

19
Assignment 4 - Member Info/Edit

• if user has already entered a password, then
  display the membership information and skip the
  rest of this task.
• If not already logged in, then ask for the users
  First Name, Last Name, and Password

20
Assignment 4 - Member Info/Edit

• Check the MEMBERSHIP table for that information
• If it exists in the MEMBERSHIP table, set a
  session ID variable to indicate that the user is
  logged in and then display a page that shows all
  the information in the file for that user
• If the First and Last Name match, but the
  password is wrong, say that the password is
  incorrect and provide a link to the password help
  page. Save the First and Last Name to session ID
  variables.
• If first and last name do not exist, give the
  user an option of going to the new member page or
  home.

21
Assignment 4 - Member Info/Edit

• Provide the user with the option of editing their
  membership information.

22
Assignment 4 - Password Help

• Ask the user for their e-mail address
• If the e-mail address appears in the MEMBERSHI
  table, then e-mail that person the necessary
  information on how to login.

23
Assignment 4 - Admin Page

• A user with Admin capability (as indicated in the
  membership table - use the Admin field) can do
  the following things on this page
• delete any user (probably need some type of drop
  down or other method to select users in the
  database)
• edit any user (same selection issue)
• if editing a user as admin, an admin has the
  capability to turn on or off admin capability to
  that user (additional field that only someone
  with admin will see)
• can send an e-mail to all users in the database
  (see chapter 17 for some examples).

24
Assignment 4 - Simple Discussion Page

• Using code from Chapter 19, integrate the simple
  discussion forum into this assignment, such that
• a user has to be logged into to access the
  forums,
• since they are logged in you can supply e-mail,
  name, etc and don't have to have them type it in
  again
• that a user with admin ability can delete a
  message.
• Note The code for this part is included on the
  CD that came with the book.

25
Questions ?