Transcript Advanced PHP with MySQL

Advanced PHP: Using PHP with
MySQL
C. Daniel Chase
The University of Tennessee at Chattanooga
Overview
●
●
●
●
●
●
Introduction to PHPMyAdmin to Manage
MySQL
MySQL Management
Introduction to Database Design
Creation of Databases
Using PHP with MySQL
Useful PHP Functions
PHPMyAdmin
●
●
●
●
Web-based interface for managing MySQL
Download from phpmyadmin.net
Can install in user web space—ours is in
system-wide location:
http://localhost/phpmyadmin/
Setup by editing config.inc.php
–
For auth_type config
Set controluser & controlpass
– Read-only access to mysql/user
– Read-only access to mysql/db
Set user & pass
●
●
PHPMyAdmin Configuration
●
Setup by editing config.inc.php
–
For auth_type config
Set controluser & controlpass
●
Read-only access to mysql/user
– Read-only access to mysql/db
Set user & pass
–
●
–
For auth_type http
Set auth_type to http
● Set controluser & controlpass as above
● Set user & pass to “”
● Do Not Set Apache to require authentication!
Trick: With NEW MySQL Install, Use PHPMyAdmin to
create & set passwords in MySQL BEFORE doing above
●
●
Managing MySQL with
PHPMyAdmin
●
●
●
●
Adding Users
Setting passwords
Note: These are Exclusive to MySQL!
Granting Rights
–
–
–
–
–
Database level access control
Table level control
Field level control
Operation control
Remote location limits
Database Design
●
●
Database Normalization
First Normal form
–
–
–
–
–
●
Each column contains atomic values
Each column has unique name
Table has unique Primary Key
No two rows identical
No repeating groups of data
Second Normal Form
–
–
Applies only to multi-column primary keys
Move data only dependent on part of primary key
Database Design
●
Third Normal Form
–
●
Remove transitive dependencies—column not
directly reliant on primary key, but on another
that is
Relationships
–
–
–
One-to-Many
One-to-One
Many-to-Many
Creation of Databases
●
●
●
●
New Database require MySQL root privileges
If using http authentication, admin can create
database & users authorized to access, then
allow access for user to create actual tables,
etc.
Grant appropriate Rights to User
Suggest admin creating
–
–
Database level admin user with full rights
Database level read-only user with SELECT only
rights
Using PHP with MySQL
●
Create connection to MySQL
–
–
●
Select database
–
●
$conn = mysql_connect(“localhost”,
“username”, “password”) or die (“Could not
connct to database”);
$conn = mysql_pconnect(“localhost”,
“username”, “password”) or die (“Could not
connct to database”);
$db = mysq_select(“database”) or
die(“Could not select database”);
Send Query
–
$result = mysql_query(“SELECT * FROM
table”) or die(mysql_error());