1 / 30

Chapter 9 Manipulating MySQL Databases with PHP PHP Programming with MySQL

Chapter 9 Manipulating MySQL Databases with PHP PHP Programming with MySQL. Opening a MySQL Connection. Open a connection to a MySQL database with mysqli_connect() Returns a positive integer if connection is successful Returns false if it is not Assign the return value to a variable

damara
Download Presentation

Chapter 9 Manipulating MySQL Databases with PHP PHP Programming with MySQL

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. Chapter 9Manipulating MySQLDatabases with PHPPHP Programming with MySQL

  2. Opening a MySQL Connection • Open a connection to a MySQL database with mysqli_connect() • Returns a positive integer if connection is successful • Returns false if it is not • Assign the return value to a variable • $connection = mysqli_connect("host"[,"user","password","database"]) • host specifies where MySQL database server resides • user and password specify MySQL username and password • database selects a database with which to work PHP Programming with MySQL

  3. Selecting a Database • Select a database when connecting OR • Use mysqli_select_db() function • Syntax • mysqli_select_db(connection, database) • Use connection variable returned by mysqli_connect() • Return value • True if database is selected successfully • False if it is not • Also enables you to change current database PHP Programming with MySQL

  4. 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 • Releases memory • mysqli_close($connection) PHP Programming with MySQL

  5. Example $connect = mysqli_connect(“localhost”, “tostrand”, “tjo123”); mysqli_select_db($connect, “e-commerce”); mysqli_close($connect); PHP Programming with MySQL

  6. 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 PHP Programming with MySQL

  7. Suppressing Errors with the Error Control Operator • Writing code that anticipates and handles potential problems is called bulletproofing • Bulletproofing techniques include: • Validating submitted form data • Using the error control operator (@) to suppress error messages • Providing your own custom (user-friendly) error messages PHP Programming with MySQL

  8. 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 PHP Programming with MySQL

  9. Terminating Script Execution $DBConnect = @mysqli_connect("localhost", "root", "paris"); if (!$DBConnect) die("The db server is not available.<br />"); echo "Successfully connected to the db server.<br />"; $DBSelect = @mysqli_select_db($DBConnect, "flightlog"); if (!$DBSelect) die("The database is not available.<br />"); echo "Successfully opened the database.<br />"; // additional statements that access the database mysqli_close($DBConnect); PHP Programming with MySQL

  10. Terminating Script Execution $DBConnect = @mysqli_connect("localhost", "dongosselin", "rosebud") Or die("The db server is not available.<br />"); echo "Successfully connected to the db server.<br />"; @mysqli_select_db($DBConnect, "flightlog") Or die("The database is not available.<br />"); echo "Successfully opened the database.<br />"; // additional statements that access the database server mysqli_close($DBConnect); PHP Programming with MySQL

  11. MySQL Error Reporting Functions PHP Programming with MySQL

  12. Reporting MySQL Errors $User = $_GET['username']; $Password = $_GET['password']; $DBConnect = @mysqli_connect("localhost", $User, $Password) Or die("Unable to connect to the database server.<br />" . "Error code " . mysqli_connect_errno() . ": " . mysqli_connect_error()) . "<br />"; echo "Successfully connected to the database server.<br />"; @mysqli_select_db($DBConnect, "flightlog") Or die("Unable to select the database.<br />" . "Error code " . mysqli_errno($DBConnect) . ": " . mysqli_error($DBConnect)) . "<br />"; echo "Successfully opened the database.<br />"; // additional statements mysqli_close($DBConnect); PHP Programming with MySQL

  13. Executing SQL Statements • mysqli_query() function sends SQL statements to MySQL database mysqli_query(connection, query) • Returns one of three values: • For SQL statements that do not return results (e.g.CREATE TABLE, INSERT), returns true if the statement executes successfully • For SQL statements that return results (e.g. SELECT), returns a result pointer that represents the query results • Refers to the currently selected row in a resultset • Returns false for any SQL statements that fail, regardless of whether they return results PHP Programming with MySQL

  14. Working with Query Results • mysqli_fetch_row() • returns fields in the current row into an indexed array • mysqli_fetch_assoc() • returns fields in the current row into an associative array • mysqli_fetch_array() • returns fields in the current row into an indexed array orassociative array PHP Programming with MySQL

  15. mysqli_fetch_row() • Returns the fields in the current row of a resultset into an indexed array • Moves the result pointer to the next row echo "<table width='100%‘ border='1'>"; echo "<tr><th>Make</th><th>Model</th> <th>Price</th><th>Quantity</th></tr>"; $Row = mysqli_fetch_row($QueryResult); do { echo "<tr><td>{$Row[0]}</td>"; echo “ <td>{$Row[1]}</td>"; echo “ <td align='right'>{$Row[2]}</td>"; echo “ <td align='right'>{$Row[3]}</td></tr>"; $Row = mysqli_fetch_row($QueryResult); } while ($Row); PHP Programming with MySQL

  16. PHP Programming with MySQL

  17. mysqli_fetch_assoc() • The function returns the fields in the current row of a resultset into an associative array • Moves the result pointer to the next row • Each field name is used as an array key • echo "<table width='100%‘ border='1'>"; • echo "<tr><th>Make</th><th>Model</th> • <th>Price</th><th>Quantity</th></tr>"; • do { • $Row = mysqli_fetch_assoc($QueryResult); • echo "<tr><td>{$Row[‘make’]}</td>"; • echo “ <td>{$Row[‘model’]}</td>"; • echo “ <td align='right'>{$Row[‘price’]}</td>"; • echo “ <td align='right'>{$Row[‘quantity’]}</td></tr>"; • $Row = mysqli_fetch_row($QueryResult); • } while ($Row); PHP Programming with MySQL

  18. Accessing Query Result Information • mysqli_num_rows() returns the number of rows in a query result • mysqli_num_fields() returns the number of fields in a query result • Both functions accept a database connection variable as an argument PHP Programming with MySQL

  19. $SQLstring = "SELECT * FROM inventory"; $QueryResult = @mysqli_query($DBConnect, $SQLstring) Or die("Unable to execute the query.<br />" . "Error code “ . mysqli_errno($DBConnect) . ": " . mysqli_error($DBConnect)) . "<br />"; echo "Successfully executed the query.<br />"; $NumRows = mysqli_num_rows($QueryResult); $NumFields = mysqli_num_fields($QueryResult); if ($NumRows != 0 && $NumFields != 0) echo "Your query returned " . mysqli_num_rows($QueryResult) . " rows and " . mysqli_num_fields($QueryResult) . " fields.<br />"; else echo "Your query returned no results.<br />"; mysqli_close($DBConnect); PHP Programming with MySQL

  20. PHP Programming with MySQL

  21. Closing Query Results • When you are finished working with query results, use the mysqli_free_result() function to close the resultset. • Takes query result as a parameter • mysqli_free_result($queryResult); • Only works when the SQL statement returns results. PHP Programming with MySQL

  22. Creating Databases • Use the CREATEDATABASE statement with the mysqli_query() function to create a new database • Use mysqli_db_select() to check whether a database exists before you create or delete it • To use a new database, select it by executing the mysqli_select_db() function • Use the DROPDATABASE statement with the mysqli_query() function to delete a database PHP Programming with MySQL

  23. $DBName = "real_estate"; ... if (@mysqli_select_db($DBConnect, $DBName)) echo "The $DBName database already exists!<br />"; else { $SQLstring = “CREATE DATABASE $DBName"; $QueryResult = @mysqli_query($DBConnect, $SQLstring) Or die("Unable to execute the query.<br />" . "Error code “ . mysqli_errno($DBConnect) . ": “ . mysqli_error($DBConnect)) . "<br />"; echo "Successfully created the database.<br />"; mysqli_select_db($DBConnect, $DBName); } PHP Programming with MySQL

  24. Creating Tables • To create a table, use the CREATE TABLE statement with the mysqli_query() function • Execute the mysqli_select_db() function before executing the CREATE TABLE statement • Prevents the new table from being created in the wrong database • Use mysqli_query() to ensure that the table doesn’t already exist PHP Programming with MySQL

  25. $DBName = "real_estate"; ... $SQLstring = "CREATE TABLE commercial (city VARCHAR(25), state VARCHAR(25),sale_or_lease VARCHAR(25), type_of_use VARCHAR(40), price INT, size INT)"; $QueryResult = @mysqli_query($DBConnect, $SQLstring) Or die("Unable to execute the query.<br />" . "Error code " . mysqli_errno($DBConnect) . ": " . mysqli_error($DBConnect)) . "<br />"; echo "Successfully created the table.<br />"; mysqli_close($DBConnect); PHP Programming with MySQL

  26. Adding Records • To add records to a table, use an INSERT statement with the mysqli_query() function • INSERT INTO tableName VALUES (val1, val2, val3…) • The values entered in the VALUES list must be in the same order as the table fields • Specify NULL in any fields for which you do not have a value • Field definition must allow NULL values or an error will occur PHP Programming with MySQL

  27. Updating Records • To update records in a table, use an UPDATE statement with the mysqli_query() function • The WHERE keyword determines which records to update in the table • UPDATE tableName SET field = valueWHERE condition; • UPDATE books SET title = “PHP Made Simple”WHERE isbn = ‘123-45-6889’; PHP Programming with MySQL

  28. Deleting Records • To delete records in a table, use a DELETE statement with the mysqli_query() function • The WHERE keyword determines which records to delete in the table • DELETE FROM tableName WHERE condition; • DELETE FROM books WHERE isbn = ‘123-45-6889’; PHP Programming with MySQL

  29. More Functions • mysqli_num_rows() • Find the number of records returned from a SELECT query • mysqli_affected_rows() • Determine the number of rows affected by an INSERT, UPDATE or DELETE query PHP Programming with MySQL

  30. $SQLstring = "UPDATE inventory SET price=368.20 WHERE make='Fender' AND model='DG7'"; $QueryResult = @mysqli_query($DBConnect, $SQLstring) Or die("Unable to execute the query.<br />" . "Error code " . mysqli_errno($DBConnect) . ": " . mysqli_error($DBConnect)) . "<br />"; echo "Successfully updated " . mysqli_affected_rows($DBConnect) . " record(s).<br />"; PHP Programming with MySQL

More Related