MySQL and PHP

1
MySQL and PHP

• By Trevor Adams

2
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

3
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

4
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

5
MySQL

• Available as both a commercial and open-source
  product
• Implements the SQL standards
• Available on many platforms
• Windows
• Linux
• Mac
• Unix
• Available from http//www.mysql.com/

6
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

7
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

8
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

9
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

10
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

11
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

12
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

13
Connecting to MySQL with PHP

• Use the MySQL connect routine
• mysql_connect(host, user, password)
• user and password will be your account details
• mysql_connect will return a link ID
• link mysql_connect(host, user, password)
• if(!link) echo Unable to connect
• Always check the link to ensure that the database
  connection was successful

14
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

15
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

16
Using a Connection

• Use functions mysql_fetch_row(result) to obtain
  a row from the result set
• Returns false when no rows left
• Example
• 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

17
SQL Query Types

• SELECT
• SELECT fields, FROM table WHERE criteria
  ORDER BY field asc,desc
• 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.
• ForenameTrevor AND SurnameAdams
• field denotes which field to sort by

18
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)

19
SQL Query Types

• UPDATE
• UPDATE table SET fieldvalue, WHERE
  criteria
• table denotes the table to update
• fieldvalue, is a comma separated list of
  values for fields
• criteria a Boolean expression that specifies
  which records to update
• If no criteria is given, all records would be
  updated
• UPDATE students SET forenameTrevor WHERE
  StudentIDAK301390
• With no where clause every record in the table
  would be updated with forenameTrevor

20
SQL Query Types

• DELETE
• DELETE FROM table WHERE criteria
• Simple and dangerous statements
• table to delete from
• criteria specifying records to delete
• No criteria deletes all records
• DELETE FROM students
• Removes all student records with no warning and
  no sympathy for mistakes.
• E.g. DELETE FROM students WHERE
  StudentIDAK301390
• Deletes the student with StudentID of AK301390

21
Quick Example

• query INSERT INTO students (StudentID,
  Forename, Surname, Level) VALUES (AK301390,
  Trevor, Addams, M)
• result mysql_query(query)
• if(!result)
• Echo Insertion failed
• else
• Echo Record inserted

22
Quick Example

• query UPDATE students SET SurnameAdams
  WHERE StudentIDAK301390
• result mysql_query(query)
• If(!result)
• echo Update failed!
• else
• echo Update successful!

23
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,

24
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.

25
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