Transcript MySQL and PHP

MySQL and PHP
By Trevor Adams
Topics Covered



What is SQL?
SQL Standards
MySQL






Database
Tables
Queries
Data Manipulation Language (DML)
Data Definition Language (DDL)
MySQL and PHP



Connecting to MySQL Server
Using a connection
Functions
What is SQL?








Structured Query Language
Allows database operations
Retrieve data
Modify data
Insert new data
Remove data
Create and modify tables
English type syntax
SQL Standards





ANSI (American National Standards Institute)
SQL is available on many platforms and products
Many products implement specific features that are
exclusive
A product must meet the requirements of ANSI SQL
to be considered ANSI SQL compliant
Assists programmers by using a common syntax
MySQL



Available as both a commercial and opensource product
Implements the SQL standards
Available on many platforms





Windows
Linux
Mac
Unix
Available from http://www.mysql.com/
MySQL - Database


A MySQL server is capable of storing many
databases
A database is generally made of a collection
of related tables


Each student will get one database for use with
the module
Every database will be accessible by the student
that owns it
MySQL - Tables



A database is generally made up of related tables
Each table will have a name that is unique within the
database
A table contains records with data
StudentID
Forename
Surname
Level
AK123900
Trevor
Adams
M
AB340948
Bobby
Ratchet
3
AC234887
Johan
Doex
2
MySQL - Queries

A query performed on a database can result in data or some
kind of status




A returned list of required records
Whether a deletion was successful
SELECT StudentID FROM Student
Returns a result set
StudentID
AK123900
AB340948
AC234887
MySQL - Queries

Queries can come in the following forms:





SELECT – extracting data
UPDATE – updates data
DELETE – deletes data
INSERT – inserts data
All of these queries can be used on the
MySQL database software
Data Manipulation Language


Consists of the queries that enable the
developer to modify the data contained
The SQL server processes these queries and
returns a result set or a status notification
Data Definition Language

Defines a set of queries that can be used by the
developer to modify the data structure







CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
We shall not be covering these commands to a
great extent
Use a management tool to generate these
commands automatically
PHP and MySQL

PHP contains all of the functionality required to
interact with MySQL servers



Most PHP MySQL functions are prefixed with ‘mysql_’
Use the PHP homepage to search for mysql_ and examine
the results
MySQL is a client-server based DBMS



Database management system
One (or few) server(s) caters for many clients
Possible for web server and DBMS server to be on the
same system
Connecting to MySQL with PHP

Use the MySQL connect routine



mysql_connect will return a link ID



mysql_connect($host, $user, $password)
$user and $password will be your account details
$link = mysql_connect($host, $user, $password)
if(!$link) { echo “Unable to connect”; }
Always check the link to ensure that the
database connection was successful
Selecting a database

Once a link has been established, select a
database





mysql_select_db($dbname, [$link])
[] optional – uses last created $link if not given
mysql_select_db returns a Boolean indicating
status
$result = mysql_select_db(“students”)
If(!$result) { echo “No database”; }
Using a Connection


Once a connection has been established it is
possible to execute queries
Queries always return a result






Success status
Result Set
Use mysql_query($query_string) to execute
$query = “SELECT * FROM Students”;
$result = mysql_query($query);
$result will contain the desired result set or false if
not available
Using a Connection

Use functions mysql_fetch_row($result) to obtain a
row from the result set


Example:





Returns false when no rows left
$query = “SELECT * FROM Students”;
$result = mysql_query($query);
While($row = mysql_fetch_row($result)){
 // $row will be an array index at 0 per column
}
$row will be equal to false (ending the while loop)
when there are no more rows left
SQL Query Types

SELECT




[fields] can be * for all or field names separated by
commas
[table] is the name of the table to use
[criteria] is a collection of Boolean expressions that
limits returned rows E.g.


SELECT [fields,…] FROM [table] WHERE [criteria] ORDER
BY [field] [asc,desc]
Forename=‘Trevor’ AND Surname=‘Adams’
[field] denotes which field to sort by
SQL Query Types

INSERT INTO





INSERT INTO [table]([fields,…] VALUES([newvalues,…])
[table] indicates which table to insert into
[fields] is a comma separated list of fields that are
being used
[newvalues] is comma separated list of values that
directly correspond to the [fields]
E.g. INSERT INTO students(StudentID, Surname,
Forename, Level) VALUES(‘AK301390’, Adams,
Trevor, M)
SQL Query Types

UPDATE




[table] denotes the table to update
[field=value,…] is a comma separated list of values
for fields
[criteria] – a Boolean expression that specifies which
records to update


UPDATE [table] SET [field=value,…] WHERE [criteria]
If no criteria is given, all records would be updated
UPDATE students SET forename=‘Trevor’ WHERE
StudentID=‘AK301390’

With no where clause every record in the table would be
updated with forename=‘Trevor’
SQL Query Types

DELETE




Simple and dangerous statements
[table] to delete from
[criteria] specifying records to delete



No criteria deletes all records
DELETE FROM students


DELETE FROM [table] WHERE [criteria]
Removes all student records with no warning and no
sympathy for mistakes.
E.g. DELETE FROM students WHERE
StudentID=‘AK301390’
Deletes the student with StudentID of ‘AK301390’
Quick Example



$query = “INSERT INTO students (StudentID,
Forename, Surname, Level) VALUES (‘AK301390’,
‘Trevor’, ‘Addams’, ‘M’)
$result = mysql_query($query);
if(!$result) {


} else {


Echo “Insertion failed”;
}
Echo “Record inserted”;
Quick Example



$query = “UPDATE students SET
Surname=‘Adams’ WHERE
StudentID=‘AK301390’
$result = mysql_query($query);
If(!$result) {


} else {


echo “Update failed!”;
}
echo “Update successful!”;
Quick Example



$query = “SELECT * FROM student”;
$result = mysql_query($query);
If(!$result) {


echo “No result set”;
} else {

while ($row = mysql_fetch_row($result)){
 foreach($row as $value){




}
}
}
echo “$value, ”;
Useful functions





Many mysql functions can take a link identifier but do not need it.
 Simply uses the last one opened
mysql_affected_rows ( [link_identifier] )
 Returns the number of rows affected from the last query
mysql_errno ( [link_identifier] )
 Gets the last error number from the server
mysql_error ( [link_identifier] )
 Returns a string containing error information
mysql_fetch_array (result [,result_type] )
 Retrieves a record from a result set as an array, optional result
time can be MYSQL_ASSOC, MYSQL_NUM or default
MYSQL_BOTH.
Other Resources


SQL is a big topic, a walk-through tutorial will be
available in the lab session
Meanwhile – take a look at the following resources:



http://www.w3schools.com/sql/default.asp
http://www.php.net/mysql
You can obtain MySQL free of charge from



http://dev.mysql.com/downloads/
Version 5.x has just been released
University currently uses 4.x