Class 3 MySQL

1
Class 3MySQL

• Robert Mudge

Reference http//www.w3schools.com/php/
2
MySQL Configuration/Installation

• Installation from scratch
• my.ini file
• Passwords (old and new)
• Command line operation

3
MySQL Tools

• Review of Tools
• MySQL Administrator
• MySQL Query Browser
• MySQL PhpMyAdmin
• dbDesigner4

4
MySQL Create database

• Best way MySQL Administrator
• Create user
• Assign permission
• Control Panel Creation Example
• Create user
• Create database
• Assign user to database
• Watch username prefix

5
Create table of users

• Create a table using phpMyAdmin for users
  (suggest lower case for variables and UPPER case
  for SQL)
• username
• password
• realname
• date
• Add users to table using phpMyAdmin

6
Query Select Statement

• Reference W3Schools
• http//www.w3schools.com
• Using MySQL Query browser
• SELECT FROM users
• SELECT FROM users WHERE usernamesmith

7
PHP MySQL Access - connect
// -----------------------------------------------
----------------------------- function
connect_to_database() // host
get_server() host 'localhost'
dbusername 'bragn_admin' dbpassword
'bragn_admin' database 'bragnbracelets'
// setup the connection to the database _at_ db
mysql_connect(host, dbusername, dbpassword)
if (!db) echo 'ltbgtErrorlt/bgt Could not
connect to databaseltbrgt' echo "Host
hostltbrgt" echo 'ltbrgt' echo 'ltigtPlease
try again or contact the system
administratorlt/igtltbrgt' echo 'lthrgt'
logger(mysql_error()) exit _at_ conn
mysql_select_db(database) if (!conn)
logger(mysql_error()) exit return
db
8
PHP MySQL Access - connect
function get_table_row(table, key,
constraint) row 0 // connect to db
db connect_to_database() query "select
from table where key 'constraint'"
result mysql_query(query, db) if
(result) if (mysql_num_rows(result) gt
1) echo 'ltbgtWarninglt/bgt Query
returns a non-unique result in enrollment.ltbrgt'
echo 'ltpregt' echo "Table
tableltbrgt" echo "Key keyltbrgt"
echo "Constraint constraintltbrgt"
echo 'lt/pregt' echo 'Showing the first
result foundltbrgt' echo 'Please examine the
table dataltbrgtltbrgt' row
mysql_fetch_array(result) else
echo "ltbgtErrorlt/bgt No row found in table for
key constraintltbrgtltbrgt" return row
9
Homework 3

• Create a role based database design. Create the
  database, populate with the given data and write
  PHP to access the database and perform the
  following queries.
• Show all user names with the role of
  administrator.
• Show what roles Susan Johnson has.
• Show all the users, all their data
• Show all the roles in ascending order of their
  level.
• Turn in printed PHP source code and screen
  captures from your running program.

10
Users and Roles

• User Table
• userid int(10) pkey
• username char(20)
• password char(20)

• Role Table
• userid int(10) fkey
• roleid int(10) fkey

• RoleTypes Table
• roleid int(10) pkey
• name char(20)
• level int(10)

11
User Data

• User Table
• Record 1
• userid 1000
• username jsmith
• password jsmith
• realname John Smith
• Record 2
• userid 1001
• username tjones
• password tjones
• realname Tom Jones
• Record 3
• userid 1002
• username sjohnson
• password sjohnsom
• realname Susan Johnson

12
Role Types Data

• RoleTypes Table
• Record 1
• roleid 100
• name guest
• level 1
• Record 2
• roleid 200
• name user
• level 10
• Record 3
• roleid 300
• name manager
• level 100
• Record 4
• roleid 400
• name administrator
• level 1000

13
Role Data

• RoleTypes Table
• Record 1
• Userid 1002
• roleid 300
• Record 2
• Userid 1002
• roleid 400
• Record 3
• Userid 1000
• roleid 100
• Record 4
• Userid 1001
• roleid 300
• Record 5
• Userid 1001
• roleid 200
• Record 6
• Userid 1002
• roleid 200