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. • Forename=‘Trevor’ AND Surname=‘Adams’ • [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 [field=value,…] WHERE [criteria] • [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 • 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’

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 StudentID=‘AK301390’ • 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 Surname=‘Adams’ WHERE StudentID=‘AK301390’ • $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