1 / 17

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

Learn how to connect, query, and manipulate data in a MySQL database using PHP for web-based applications.

bobbieg
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

  2. 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.

  3. 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

  4. 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)

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

  6. 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

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

  8. 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; }

  9. 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);

  10. 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’);

  11. 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 }

  12. 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 }

  13. 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);

  14. 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.

  15. 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>"; }

  16. 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.

  17. 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.

More Related