Connecting to a database in PHP

1. Connecting to a database in PHP Bryan Duggan

2. PHP Manual • http://www.php.net/manual/en/index.php

3. Overview of MySQL • Relational database management system (RDBMS) • Free • Website @ http://www.mysql.com/

4. MySQL • Database Basics • Common SQL Statements • INSERT • SELECT • UPDATE • DELETE • Simple Join • Entity-Relationship (ER) Modeling • An Easy Way to Manage Your MySQL DBs

5. MySQL (cont.) • Database Basics • A relational database manager (MySQL) manages databases which holds tables which has records (rows) with attributes (columns) • Each record must have a unique ID, also known as a Primary Key. When used as an identifier in another table it’s called a Foreign Key. Used for joins. • Each attribute has to have a data type. (e.g. int, text, varchar) • A database language (SQL) is used to create and delete databases and manage data

6. MySQL (cont.) • Table structure for following examples: • Created two tables, ‘oscarpool’ & ‘bestdirector’ • Instead of using SQL to create tables use phpMyAdmin url: http://www.phpmyadmin.net/ CREATE TABLE oscarpool ( uid int(4) auto_increment, username varchar(255), email varchar(255), bestpicture int(2), PRIMARY KEY (uid) ) CREATE TABLE bestdirector ( bdid int(4) auto_increment, name varchar(255), PRIMARY KEY (bdid) )

8. MySQL (cont.) • Common SQL Statement: INSERT INSERT INTO oscarpool (username,email,bestpicture) VALUES (‘dolsen',‘dave@usablecode.com',1) • Creates a new row in the table ‘oscarpool’ • Columns need to have ‘s. • Tip: If you have an ‘ in your data you need to escape it before inserting it. Can use the PHP function addslashes(). Example: ‘John O\’Brien’

9. MySQL (cont.) • Common SQL Statement: SELECT SELECT uid,username FROM oscarpool • Selects the columns ‘uid’ and ‘username’ from every row in ‘oscarpool’ • SELECT is how you query the database. You can also: • limit the number of records returned with LIMIT, • limit retrieval to those records that match a condition with WHERE, • sort the data after the query has been evaluated using ORDER BY • Tip: To easily select every column replace ‘uid’ with ‘*’

10. MySQL (cont.) • Common SQL Statement: UPDATE UPDATE oscarpool SET email = ‘david.olsen@mail.wvu.edu’ WHERE uid = 1 • Updates the email address where ‘uid = 1’ in the table ‘oscarpool’ • In this case I know that uid 1 is what my record was. In many cases you’d pass a uid variable from a form.

11. MySQL (cont.) • Common SQL Statement: DELETE DELETE FROM oscarpool WHERE uid = 1 • Deletes the record where ‘uid = 1’ in the table ‘oscarpool’ • DELETE only removes the record from the table. To remove an entire table from the database you need to use the SQL statement DROP. • Tip: To remove every record in a table but not remove the table just don’t include the WHERE clause.

12. MySQL (cont.) • Simple Join SELECT bd.name FROM oscarpool op, bestdirector bd WHERE op.uid = 1 and op.bestdirector = bd.bdid • Selects the name of the Best Director that the user with ‘uid = 1’ has chosen • bestdirector is a Foreign Key of the Primary Key for the table BestDirector • Tip: Try to not have fields from two different tables have the same name. Gets confusing when trying to output this data when we connect with PHP.

13. Selecting in PHP • 6 Steps • Connect to DBMS: mysql_connect • Select the database to use: mysql_select_db • Run the query mysql_query • Retrieve a row of results: mysql_fetch_array • For each row, process the row • Finally close the connection: mysql_close

14. Example: <p> List of ships from the database: <p> <table border="1"> <?php // Step 1 $conn = mysql_connect('localhost', 'root'); // Step 2 mysql_select_db('startrek', $conn); // Step 3 $result = mysql_query('select * from ship', $conn); // Step 4 while ($row =mysql_fetch_array($result)) { // Step 5 echo "<tr><td> $row[id]</td><td> $row[shipname] </td> <td> $row[position] </td> </tr>"; } // Step 6 mysql_close($conn); ?> </table>

15. Error handling • In PHP die(“Message”); will quit the script and print the message • Most of the MySQL commands return NULL (false) if an error occurred • We can use 2 functions: • mysql_errno() – Gets the error number • mysql_error() – Gets the error string

16. Example: function showError() { die("Sorry a problem occured: ". mysql_errno(). ":". mysql_error()); } if (!($conn = mysql_connect('localhost', 'root'))) { showError(); } if (!(mysql_select_db('startrek', $conn))) { showError(); } if (!($result = mysql_query('select * from shjip', $conn))) { showError(); } while ($row =mysql_fetch_array($result)) { // Step 5 echo "<tr><td> $row[id]</td><td> $row[shipname] </td> <td> $row[position] </td> </tr>"; } mysql_close($conn);

17. Generates: Note using “” does not expand functions, so we have to use: die("Sorry a problem occured: ". mysql_errno(). ":". mysql_error());

18. Paramaterised queries: • Take the value from a form • use it in a query or insert update statement: update ship set shipname=\"$_POST[shipName]\", position=\"$_POST[position]\" where id = $_POST[id] • Note form fields already have addslashes called on them!!

19. More useful functions: • mysql_data_seek – Allows you to specify a start row • mysql_num_rows – Returns the number of rows in a result set • mysql_change_user • mysql_create_db • mysql_drop_db • mysql_tablename – returns the table names • Can also use “LIMIT 3” at the end of an SQL statement to limit the number of results returned

20. PHP Configuration File • Use a securely positioned ‘config’ file to store variables. • Other PHP pages can include and use the variables as their own. <?php // database configuration $db = array('Server' => 'localhost', 'user' => 'root', 'password' => '', database => 'startrek'); ?>