PHP-Database Integration

1. PHP-DatabaseIntegration

2. Data-Driven Websites • Startzone.highline.edu • Officehours.highline.edu • Classes.highline.edu • Angel.highline.edu • Amazon.com • Ebay.com

3. Introduction to Databases • A database is a collection of information which a program can access • A database is made up of tables • Each table represents a group of things • Students, Employees, Books, Orders, etc. • A table consists of rows and columns • Each row represents a single item • A column represents an attribute or characteristic of an item • phpMyAdmin: http://ned.highline.edu/phpMyAdmin • Log in as highline / highline • Select database hcc

4. Database Tables Rows Columns Employee table

5. Primary Keys • A primary key is a column that uniquely identifies the rows in a table • A primary key column can not contain duplicate values

6. Querying a database • A query is a structured set of instructions for retrieving, adding, modifying, and deleting data • Structured query language (SQL) is a standard data manipulation language • SQL keywords are not case sensitive, but are all upper case by convention • Database and table names are casesensitive on UNIX/Linux systems • Column names are case insensitive on all platforms

7. Structured Query Language • SELECT – retrieves data from a table • INSERT – adds a new row to a table • UPDATE – modifies existing rows in a table • CREATE TABLE – creates a new table • DELETE – removes rows from a table • DROP TABLE – completely removes a table and all of its data

8. Retrieving Data • Use a SELECT query to retrieve columns and rows from a table: SELECT columns FROM table_name; • Use the asterisk (*) wildcard with the SELECT statement to retrieve all columns from a table • To return multiple columns, separate column names with a comma SELECT sid, first, lastFROM student;

9. Sorting Query Results • Use ORDER BY with the SELECT statement to sort the results returned from a query • To perform a reverse (descending) sort, add the DESC keyword SELECT sid, first, lastFROM studentORDER BY last; SELECT sid, first, lastFROM studentORDER BY last, first; SELECT sid, first, lastFROM student ORDER BY last DESC;

10. Filtering Query Results • Specify which rows to return using the WHERE keyword with a relational operator (=, !=, <, >, <=, >=) • Use the keywords AND and OR to specify more detailed conditions SELECT sid, first, lastFROM studentWHERE gpa > 2.0; SELECT sid, first, lastFROM studentWHERE gpa > 2.0 AND birthdate = '1987-12-27';

11. Try it • Display the sid, first and last names, and birthdates of all students. • Display all fields for sid 343-66-7777. • Display the names and advisor numbers of students with advisor 1. • Display the names and advisor numbers of students with advisor 1 or 2. • Display the names and birthdates of students born after January 1, 1960. (Remember to use MySQL's date format: YYYY-MM-DD.) • Display names and GPA's of students with GPAs between 3.0 and 4.0.

12. Adding Rows • Use the INSERT statement to add rows to a table • INSERT INTO table_name (col1, col2, …)VALUES(value1, value2, …); • The values entered in the VALUES list must be in the same order as the columns list • Specify NULL for missing values and AUTO_INCREMENT columns • Strings and dates need to be quoted • Numbers do not need to be quoted, but they can be • MySQL is NOT case-sensitive, except for table names • Remember that primary keys must be unique! INSERT INTO student (sid, last, first, birthdate, gpa, advisor) VALUES ('888-777-6666', 'Kaur', 'Gurpreet', '1990-05-05', NULL, 2);

13. Updating Records • To update records in a table, use the UPDATE statement UPDATE table_name SET column_name=value WHERE condition; • Without the WHERE clause, every row in the table will be updated! • Safest to use primary key UPDATE student SET advisor = 3 WHERE sid = '888-777-6666';

14. Deleting Records • Use the DELETE statement to delete records in a table DELETE FROM table_name WHERE condition; • Without the WHERE clause, every row in the table will be deleted! • Safest to use primary key DELETE FROM student WHERE sid = '888-777-6666';

15. PHP Programming with MySQL Opening a PHP-MySQL Connection • Open a connection to a MySQL database with mysql_connect() • Returns a positive integer if connection is successful • Returns false if it is not • Assign the return value to a variable • $cnxn = mysql_connect("host”,"user","password”) • host specifies where MySQL database server resides, usually localhost • user and password specify MySQL username and password

16. PHP Programming with MySQL Example //Connect to the database server $cnxn = mysql_connect("localhost", "highline", "highline"); if($cnxn) print "Successfully connected to the db server!"; else die("Connection to the db server failed.");

17. PHP Programming with MySQL Terminating Script Execution • The die() function terminates script execution • Accepts a single string argument • Call the die() as a separate statement OR • Append the function call to an expression with the Or operator

18. PHP Programming with MySQL Shortcut //Connect to the database server $cnxn = mysql_connect ("localhost", "highline", "highline") OR die("Connection to the db server failed."); print "Successfully connected to the dbserver!";

19. PHP Programming with MySQL Selecting a Database • Use mysql_select_db() function • Syntax • mysql_select_db(database) • Return value • True if database is selected successfully • False if it is not • Enables you to change current database

20. PHP Programming with MySQL Example //Select the database mysql_select_db("hcc") OR die("Error accessing database."); print "Successfully selected the database.";

21. PHP Programming with MySQL Handling MySQL Errors • Reasons for failure to connect to a database server include: • The database server is not running • Insufficient privileges to access the data source • Invalid username and/or password

22. PHP Programming with MySQL Bulletproofing • Bulletproofing is writing code that anticipates and handles potential problems • Bulletproofing techniques include: • Validating submitted form data • Using the error control operator (@) to suppress MySQL error messages • Error messages can give away too much information! • Providing your own custom (user-friendly) error messages

23. PHP Programming with MySQL Suppressing Errors //Connect to the database server $cnxn = @mysql_connect("localhost", "highline", "highline") OR die("Connection to the db server failed."); //Select the database @mysql_select_db("teejo_db") OR die("Error accessing database."); This suppressesthe default error message.

24. Retrieving Rows • Send a SELECT statement to the mysql_query() function • $result = mysql_query($query) OR die("Error executing query"); • Get the number of rows in the result set • if(mysql_num_rows($result) • Process the query results using a while loop • mysql_fetch_array returns a row as an array • while($row = mysql_fetch_array($result)) • Process the query results • echo $row['column_name'];

25. PHP Programming with MySQL Example • echo "<h2>Student Roster</h2>"; • $query = "SELECT sid, last, first • FROM student"; • $result = mysql_query($query) OR • die("Error executing query"); • if(mysql_num_rows($result) == 0) • echo "Your query returned no rows."; • else { • while ($row = mysql_fetch_array($result)) • { • echo $row['sid'] . " - "; • echo $row['first'] . " "; • echo $row['last'] . "<br>"; • } • } Column Names

26. Practice • Using PHP, display a list of advisors and their offices. (Use the hcc database advisor table.) • echo "<h2>Advisor List</h2>"; • $query = "SELECT * FROM advisor"; • $result = mysql_query($query) OR • die("Error executing query"); • if(mysql_num_rows($result) == 0) • echo "Your query returned no rows."; • else { • while ($row = mysql_fetch_array($result)) { • echo $row['advisor_id'] . " - "; • echo $row['advisor_first'] . " "; • echo $row['advisor_last'] . "<br>"; • } • }

27. MySQL Data Types

28. Creating Tables • The CREATE TABLE statement specifies the table and column names and the data type for each column CREATE TABLE table_name (column_nameTYPE, ...); CREATE TABLE tblProduct( productId INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), price DOUBLE );

29. PHP Programming with MySQL Creating Tables • Send a CREATE TABLE statement to the mysql_query() function • $sqlCreate = "CREATE TABLE IF NOT EXISTS tblProduct( • productIdINT AUTO_INCREMENT PRIMARY KEY, • name TEXT(20), • price DOUBLE • )"; • @mysql_query($sqlCreate) OR • die("Unable to create the table."); • echo "Table created.<br>"; Used to automatically number table rows

30. Deleting Tables • The DROP TABLE statement removes all data and the table definition DROP TABLE table; • You must have DROP privileges to delete a table DROP TABLE tblProduct;

31. PHP Programming with MySQL Accessing Query Result Information • SQL statements that do not return results:CREATE TABLE, INSERT, DELETE, UPDATE • return true if successful • return false otherwise • mysql_affected_rows($cnxn) • returns the number of rows affected by an INSERT, UPDATE or DELETE query

32. Inserting Rows • Send an INSERT statement to the mysql_query() function $sqlInsert = "INSERT INTO tblProductVALUES(NULL, 'gizmo', 3.99)"; @mysql_query($sqlInsert) OR die("Unable to insert the row."); echo mysql_affected_rows($cnxn) . " row(s) inserted.<br />"; Use NULL for AutoNumber columns

33. PHP Programming with MySQL Updating Rows • Send an UPDATE statement to the mysql_query() function $sqlUpdate = "UPDATE tblProduct SET price = 4.99 WHERE name = 'gizmo'"; @mysql_query($sqlUpdate) OR die("Unable to update the row(s)."); echo mysql_affected_rows($cnxn) . " row(s) updated.<br />";

34. PHP Programming with MySQL Deleting Rows • Send a DELETE statement to the mysql_query() function $sqlDelete = "DELETE FROM tblProduct WHERE name = 'gizmo'"; @mysql_query($sqlDelete) OR die("Unable to delete row(s)."); echo mysql_affected_rows($cnxn) . " row(s) deleted.<br />";

35. PHP Programming with MySQL Closing a Database Connection • Any open database connections are closed automatically when a PHP script ends • Should be closed explicitly when you are finished with the connection to release memory • mysql_close($cnxn) //Close the connection mysql_close($cnxn);

36. Validating User Input • Escape and validate all string data before inserting it into a database!!! $name = mysql_real_escape_string($_POST['name']); if(is_numeric($_POST['price'])) $price = $_POST['price']; $sqlInsert = "INSERT INTO tblProduct VALUES(NULL, '$name', $price)"; @mysql_query($sqlInsert) OR die("Error inserting row."); echo "Row successfully inserted.";

37. Using an Include File //db.php <?php //Connect to the db server $cnxn = mysql_connect ("localhost", "highline", "highline") OR die("Connection to the db server failed."); //Select the database mysql_select_db("hcc") OR die("Error accessing database."); • Protect sensitive data using an include file • Best to store outside of public_html <?php include("db.php");