PHP – MySQL Interaction

1. PHP – MySQL Interaction

2. PHP MySQL Connection Functions <? $db = mysql_connect($server, $user, $password); mysql_select_db("users"); $r = mysql_query("SELECT user FROM users"); while ($row = mysql_fetch_assoc($r)) { print $row['user']; } mysql_close(); ?>

3. MySQL and PHP - Select <?php $dbLink = mysql_connect("localhost", $dbUser, $dbPass); $sql = "SELECT * FROM books"; $res = mysql_db_query("test", $sql, $dbLink); $row = mysql_fetch_array ($res); $title = $row["title"]; $subtitle = $row["subtitle"]; $author = $row["author"]; ?> <table border="1"><tr> <td><b>Title</b></td><td><b>Sub Title</b></td><td><b>Author</b></td></tr> <tr> <?php echo "<td>$title</td><td>$subtitle</td><td>$author</td>";?> </tr></table>

4. MySQL and PHP - Insert You can enter a specific string or the value from a PHP variable: $tablename = “pet”; $field3 = “something”; mysql_query(“INSERT INTO $tablename VALUES (‘field2value’,’$field3’)” ); Remember to escape the " marks if you create an insert statement within a $sql="" statement in PHP: $sql = "INSERT INTO $tablename VALUES (\"field2value\“,\"$field3\")"; mysql_query($sql);

5. MySQL and PHP - Update The update statement requires caution: If used alone, it will modify ALL records with one command. e.g. the following SQL statement, if put into a query, would set all firstnames to Fred: $tablename = “pet”; mysql_query(“UPDATE $tablename SET firstname = \"Fred\“”); To set only Fred's record to firstname=Fred, you must specify that it only applies to his record. e.g. if the database contains a unique field called 'id' and the value for id in Fred's record is 84: mysql_query(“UPDATE $tablename SET firstname = ‘Fred’ WHERE id=84”);

6. MySQL and PHP - Delete The delete statement requires caution: If used alone, it will delete ALL records with one command. e.g. the following SQL statement, if put into a query, would delete all data from a table: $tablename = “users”; mysql_query(“DELETE FROM $tablename”); To delete specific records the query would include a "WHERE" clause: mysql_query(“DELETE FROM $tablename WHERE(field1='value‘)”);

7. MySQLand PHP – More Detail

8. Opening a MySQLdatabase connection $username=“fred”; $password=“fred”; $database=“eiw”; mysql_connect("localhost",$username,$password); mysql_select_db($database) or die( "Unable to select database"); Assumes that the machine running the server is “localhost” You can easily use a MySQL server that is running on a remote machine.

9. mysql_connect()  This is used to establish a connection to the mySQL server. It is typically of the form mysql_connect('host', 'user', 'password'); Example $link= mysql_connect('localhost',‘summer',‘beach'); You can use localhost as the host name but you could also connect to other Internet hosts, if you have permission.

10. mysql_select_db()  This command has the syntax mysql_select_db('database') where database is the name of a database. It returns a Boolean. This tells mySQL that you now want to use the databasedatabase. mysql_select_db('seasons');

11. mysql_query()  mysql_query(query) send the query queryto mySQL. $link = mysql_connect("localhost", "summer", "beach");  // you can add some connection checks $query="SELECT * FROM seasons.holidays"; $result=mysql_query($query); The result is in $result.

12. Submitting a query to the server $query = "SELECT uid from users WHERE username = ‘fred’"; $res = mysql_query($query); // no result - no user so return false if (! $res) { … no result (error!) }

13. Accessing/Using the results of a query • Mysql_query: returns an object we can use to get at the rows of the result of the query. • mysql_num_rows($resultvar): number of rows in the result. • mysql_result($resultvar,$index,$field) • Returns a single column value • $index is the row • $field is the field name (column name)

14. Example $res = mysql_query(“SELECT * FROM users”); if (! $res) { …handle error…} $numrows = mysql_num_rows($res) for ($i=0;$i<$numrows;$i++) { $name = mysql_result($res,$i,”username”); $pass = mysql_result($res,$i,”password”); … Do something with $name and $password… }

15. Accessing/Using the results of a query • mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both • mysql_fetch_assoc — Fetch a result row as an associative array • mysql_fetch_array($resultvar): an array of rows in the result. • mysql_fetch_assoc($resultvar) • Both called the same way • But the array returned by mysql_fetch_assoc can only be indexed by the names of the table fields/columns

16. Accessing/Using the results of a query • mysql_fetch_array(result) returns an array that is the result row(s) for the query. • The results array contains columns that can be accessed both by number and by column name: while($columns=mysql_fetch_array($result)) {   print 'name: '.$columns['name'];   print 'first column:'. $columns[0]; //if you used mysql_fetch_assoc, you cannot use numbers to access result rows }

17. Tracing Errors with echo() Statements • Tracing is the examination of individual statements in an executing program • The echo() statement provides one of the most useful ways to trace PHP code • Place an echo() method at different points in your program and use it to display the contents of a variable, an array, or the value returned from a function • When your code is working correctly, comment out the echo statements

18. Tracing Errors with echo() Statements An advantage of writing your query in the manner below is that it makes it easier to trace errors in your SQL statement. $query = "SELECT uid from users WHERE username = ‘fred’"; echo $query; $res = mysql_query($query); When your select is written like: $res = mysql_query("SELECT uid from users WHERE username = ‘fred’"); You have to rewrite your code to do error checking/tracing.

19. Connection Errors

20. Handling MySQL Errors • Reasons for not connecting to a database server include: • The database server is not running • Insufficient privileges to access the data source • Invalid username and/or password

21. Handling MySQL Errors (continued) • Make sure you are using a valid username and password • Make sure you have spelt the name of the database correctly.

22. Terminating Script Execution • The die() and exit() functions terminate script execution • The die() version is usually used when attempting to access a data source • Both functions accept a single string argument • Call the die() and exit() functions as separate statements or by appending either function to an expression with the Or operator

23. Terminating Script Execution (continued) $DBConnect = mysql_connect("localhost", "root", "paris"); if (!$DBConnect) die("<p>The database server is not available.</p>"); echo "<p>Successfully connected to the database server.</p>"; $DBSelect = mysql_select_db($DBConnect, "flightlog"); if (!$DBSelect) die("<p>The database is not available.</p>"); echo "<p>Successfully opened the database.</p>"; // additional statements that access the database mysql_close($DBConnect);

24. Reporting MySQL Errors $User = 'username'; $Password = 'password'; $DBConnect = mysql_connect("localhost", $User, $Password) Or die("<p>Unable to connect to the database server.</p>“ . "<p>Error code " . mysql_connect_errno() . ": " . mysql_connect_error()) . "</p>"; echo "<p>Successfully connected to the database server.</p>"; mysql_select_db($DBConnect, "flightlog") Or die("<p>The database is not available.</p>"); echo "<p>Successfully opened the database.</p>"; // additional statements that access the database mysql_close($DBConnect);

25. Reporting MySQL Errors (continued) Figure 9-4 Error number and message generated by an invalid username and password

26. Reporting MySQL Errors $User = 'username'; $Password = 'password'; $DBConnect = mysql_connect("localhost", $User, $Password) Or die("<p>Unable to connect to the database server.</p>" . "<p>Error code " . mysql_connect_errno() . ": " . mysql_connect_error()) . "</p>"; echo "<p>Successfully connected to the database server.</p>"; mysql_select_db($DBConnect, "flightplan") Or die("<p>Unable to select the database.</p>" . "<p>Error code " . mysql_errno($DBConnect) . ": " . mysql_error($DBConnect)) . "</p>"; echo "<p>Successfully opened the database.</p>"; // additional statements that access the database mysql_close($DBConnect);

27. Reporting MySQL Errors (continued) Figure 9-5 Error code and message generated when attempting to select a database that does not exist

28. Creating a database driven application

29. Pseudo Code • Simple e-commerce site • Users login in (with just a user name) • View products (including search) • Add products to shopping cart • Remove products from shopping cart

30. Database Tables • users – information about all the customers that use the system. • products – information about all the products we sell. • cartentries – shopping cart items (relates a user to a product)

31. Table: users • uid: integer id number (autoincrement) • firstname, lastname: strings. varchar(20) • username: string – login name. varchar(20) • email: string. varchar(30)

32. Table: products • pid: integer id number (autoincrement) • name: string – product name. varchar(30) • price: floating point number.

33. Tables: people & products

34. Table: cartentries • uid: integer user id number • pid: integer product id number • quantity: integer (# products).

35. Table: cartentries

36. Table Relationship

37. Some Queries • Get list of all products: • “SELECT * FROM products” • Get list of all the entries in joe’s (user 22) shopping cart: • “SELECT * FROM cartentries WHERE uid=22” • Check the actual code in the demo for more complex queries…

38. Some Queries • For example, we would like to see a list of all products in all shopping carts, but we want to see product names (not ids). • Joining products and cartentries SELECT * FROM cartentries,products WHERE cartentries.productid=products.productid --or-- SELECT * FROM cartentries JOIN products ON ( cartentries.productid = products.productid )

39. Some Queries

40. Sample Code: main.php • main.php: the main program • takes care of the session (session variable userid) • Determines what the query is and takes appropriate action. • Many actions defined in other PHP files that are included using require • Generates the base HTML for the document (including a small “header”).

41. Database Code: db.php • php functions that interact with the database. This file is always “required” by main.php. • Creates connection to the mysql server. • Functions login, product_list, show_cart, add_to_cart, remove_from_cart and some HTML generating functions.

42. add.php • Called from main.php when user is adding an item to cart: • require(“add.php”) • Takes care of the logic for adding an item to the shopping cart for current user. • Makes sure item exists.

43. login.php • Called from main.php when user is trying to log in: • require(“login.php”) • Takes care of the logic for login process: • Decides what to send back if valid/invalid login.

44. logout.php • Called from main.php when user is trying to log out: • require(“logout.php”) • Takes care of the logic for log out: • Terminates the session.

45. plist.php • Called from main.php when user wants to see a list of products: • require(“plist.php”) • Just calls product_list function provided by db.php

46. remove.php • Called from main.php when user is trying to remove an item from shopping cart: • require(“remove.php”) • Gets user id (from session) and product id (from HTTP query) • Calls remove_from_cart • Sends back resulting cart as HTML.

47. search.php • Called from main.php when user is trying to search for products: • require(“search.php”) • If a search query is found in the HTTP query, processes the search. • If no search query found, sends back a form that can be used to submit a search.

48. show.php • Called from main.php when user is trying to see their shopping cart in: • require(“show.php”) • Just calls show_cart function found in db.php