Accessing Your MySQL Database from the Web with PHP (Ch 11)

Accessing Your MySQL Database from the Web with PHP (Ch 11)

The Web Database Architecture… Architecture for delivering a DB-backed website (= 3-tier web application) • Browser issues HTTP request for page x.php • Web server receives request, retrieves page, passes it for processing to PHP engine • PHP engines parses script; if statements to connect to a DB and execute queries PHP opens a connection to the MySQL server, sends the appropriate queries • MySQL server receives query, processes it, sends results to the PHP engine • PHP finishes processing the script, which usually involves formatting query results (= a set of tuples for SELECT) in HTML; returns resulting HTML to the web server • Web server passes the HTML document back to browser, which renders it Note: the process is the same regardless of what web & DB servers are used, or if they are on the same or on different machines.

Database Libraries • PHP supports libraries for connecting to a large number of DBMS products: Oracle, Ms SQL Server, PostgreSQL, MySQL • The mySQL library is specific to the MySQL system we use: MySQL Improved library → our focus; • The PEAR::DB database library is database independent • Specific backends support many different databases • More limited as it has to cover the common features of all DBMSs to which it can connect. • In general, the principles of connecting to and querying any db through different libraries are the same • Some differences in function names, functionality for different libraries • You should be able to easily adapt your knowledge from one to another

Accessing MySQL DBs from PHP • How to connect a MySQL db to a web-based front end. • Key topics: • Setting up connections to a MySQL server • Selecting a db to use • Querying the db and retrieving query results • Changing the data in the db • Using prepared statements • Disconnecting from the MySQL server • (http://us.php.net/manual/en/intro.mysqli.php)

Chapter11/search.html • http://cscdb.nku.edu/csc301/frank/Chapter11/search.html • http://www.nku.edu/~frank/csc301/Examples/MySQL/search_html.pdf

Querying a DB from the Web • A web-based front end search form: <form action="results.php" method="post"> Choose Search Type:<br /> <select name="searchtype"> <option value="author"> Author </option> <option value="title"> Title </option> <option value="isbn"> ISBN </option> </select> <br /> <br /> Enter Search Term:<br /> <input name="searchterm" type="text" size="40" /> <br /> <input type="submit" name="submit" value="Search" /> </form> • results.php = the script that queries the db and presents results

Chapter11/result.php • http://www.nku.edu/~frank/csc301/Examples/MySQL/results_php.pdf

Querying a DB from the Web • Checking and filtering input data coming from the user • strip any whitespace from the appropriate form variables: $searchtype = $_POST['searchtype']; $searchterm = trim($_POST['searchterm']); • verify that the data your script needs has been entered: if (!$searchtype || !$searchterm) { // … display a warning message and exit script; }

Querying a DB from the Web • Checking and filtering input data coming from the user • filter user input for control characters when submitting it to a db;  un-escape data coming from the dbif magic quotes is on: if (!get_magic_quotes_gpc()){ $searchtype = addslashes($searchtype); $searchterm = addslashes($searchterm); } … $data = stripslashes($dbdata); • use htmlspecialchars() to encode for display the db data that might contain characters with special meaning in HTML (&, <, > etc.) echo htmlspecialchars($data);

Querying a DB from the Web • Setting up a connection to MySQL server(mysqli library) mysqli_resource mysqli_connect ( string $server, string $username, string $password, string $database) • mysqli_connect() returns a resource representing the connection to a database on success, and false on failure • The resource returned will be passed to all the other mysqli functions that work over that connection → similar to file-handling functions • @ $db =mysqli_connect('localhost',’frank',’mypassword’,’db_frank’);

Querying a DB from the Web • Check if the attempted connection succeeded: • if (!$db) { echo '<p>'. mysqli_connect_error().'</p></body></html>'; exit; // = display error string message from previous mysqli_connect() & exit }

Querying a DB from the Web • Choosing a database to use: • When using MySQL from a command-line interface: use db_books; • When connecting from PHP: bool mysqli_select_db (mysqli_resource $link, string $db_name ) Returns true on success or falseon failure. Example: if (!mysqli_select_db ($link, "db_books")) { echo '<p>Error db select ' . mysqli_error($link) . '</p> </body> </html>'; exit; // = display error string message from previous mysqli operation & exit }

Querying a DB from the Web • Querying the database: • Set up as a string the query you want to run: $query = “select * from books where ”.$searchtype.“ like ‘%”.$searchterm.“%’”; Note:the query you send to MySQL doesn’t need “;” at the end! • Run the query and store the result in a variable for later use: mixed mysqli_query(mysqli_resource $link ,string $query) ForSELECTquery,returns a result resource on success, or F on error. $result = mysqli_query($link, $query);

Querying a DB from the Web • Retrievingthequeryresults: • Retrievethenumberofrowsreturnedbythequery: int mysqli_num_rows(mysqli_result_resource $result) $num_results = mysql_num_rows($result); • Retrieve each row from the resultset and process it →inourexample,displayit. arraymysqli_fetch_assoc(mysqli_result_resource $result); Returns: • nextrowfromtheresultset,inanassociativearray:keys=attributenames,values= thevaluesintherow → eachfieldaccessible as an array element; • falseif there are no more rows.

Querying a DB from the Web • Retrievingthequeryresults: for ($i=0; $i <$num_results; $i++) { $row = mysqli_fetch_assoc($result); echo "<p><strong>".($i+1).". Title: "; echo htmlspecialchars(stripslashes($row['title'])); echo "</strong><br />Author: "; echo stripslashes($row['author']); echo "<br />ISBN: "; echo stripslashes($row['isbn']); echo "<br />Price: "; echo stripslashes($row['price']); echo "</p>"; }

Querying a DB from the Web • Retrievingthequeryresults-alternatives: • arraymysqli_fetch_row(mysqli_result_resource $result); Returns: • nextrowfromtheresultset,inanumerically indexed array:indexes start at 0, valuesare thevaluesintherow; • falseif there are no more rows. • arraymysqli_fetch_array (mysqli_result_resource $result[, int $result_type= MYSQL_BOTH ] ); Returns: • nextrow,inanarraywith: associative keys for MYSQL_ASSOC,number indices for MYSQL_NUM,associative keys & number indices for MYSQL_BOTH • falseif there are no more rows.

Querying a DB from the Web • Disconnectingfromthedatabase: • Freeuptheresultset: void mysqli_free_result(mysqli_result_resource $result) Note:notstrictlynecessary,asall associated result memory is automatically freed at the end of the script's execution; good idea if you use more memory-intensive resources before the end of script • Closethedatabase connection: boolmysqli_close( mysqli_resource $link) This functionreturns true on success or falseon failure.

Accessing Your MySQL Database from the Web with PHP (Ch 11)