1 / 62

A Web-Based Introduction to Programming

A Web-Based Introduction to Programming. Chapter 14 Connecting to a Database: Working with MySQL. Intended Learning Outcomes. Describe the basic structure of a relational database. Identify records and fields in a sample database table.

ileneg
Download Presentation

A Web-Based Introduction to Programming

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. A Web-Based Introduction to Programming Chapter 14 Connecting to a Database: Working with MySQL Chapter 14

  2. Intended Learning Outcomes • Describe the basic structure of a relational database. • Identify records and fields in a sample database table. • Identify key characteristics of a Database Management System (DBMS). • Explain the general purpose of Structured Query Language (SQL). • Write PHP code to open and close a connection to a MySQL database. • Identify the purpose and result of MySQL SELECT queries, that may include FROM, WHERE, and ORDER BY clauses, and relational and logical operators. • Design, code and submit syntactically correct SELECT queries in PHP applications. Chapter 14

  3. Intended Learning Outcomes • Write PHP code to receive and process the result sets that are returned by MySQL SELECT queries. • Identify the purpose and result of MySQL INSERT, UPDATE, and DELETE queries. • Design, code and submit syntactically correct INSERT, UPDATE, and DELETE queries in PHP applications. • Apply a basic error-handling template to PHP code that interacts with a MySQL database. • Use a PHP include statement to maintain MySQL connection values more efficiently and securely. Chapter 14

  4. Introduction • Chapter 6 demonstrated ways to work with persistent data stored in text files. • We are now ready to learn to work with persistent data stored in relationaldatabases. • A relationaldatabaseallows us to store data in one or more related tables. • Each table in a database contains records of some kind. • Each record contains a set of specific data values, stored in fields. Chapter 14

  5. Table Example: personnel Chapter 14

  6. Table Example: personnel • The personnel table consists of 10 employee records, displayed as rows. • Each record contains five fields, displayed as columns. • Each field contains a specific data value associated with the employee record: empID (the employee's unique ID), firstName, lastName, jobTitle, and hourlyWage • The personnel table is included in the test database of your MySQL installation. Chapter 14

  7. The Relational Database Management System (RDBMS) • A relational database may include any number of related tables. • A Relational Database Management System(RDBMS) provides a full range of management tools for storing, managing, and using any number of relational databases, for example: • creating, modifying and removing databases and tables • adding, modifying, and deleting records; • searching (querying) tables • generating reports • assigning user accounts with very controlled privileges to access specific databases, tables and fields. Chapter 14

  8. The Database Administrator • Database administration is a highly skilled and in-demand profession. • Responsible for managing a RDBMS to deliver databases and tables. • Security, maintenance, backup and performance • Design, creation, and maintenance of databases and tables • Creation and removal of user accounts and related access permissions that specify what each user can or cannot do • Oversight of procedures and related functions that deliver useful services to programmer and end-users. Chapter 14

  9. Structured Query Language • Unlike text files, databases and tables in an RDBMS are not directly accessible. • Requests must be submitted using the Structured Query Language (SQL) that the RDBMS provides. • The RDBMS processes each request (called a query) and returns a result. • Use of an SQL to interact with a database: • Avoids the needs for custom code which avoids unnecessary duplication, saves time,and reduces errors • Enhances security • Ensures fast and efficient processing of each request Chapter 14

  10. MySQL • Each RDBMS provides its own version of SQL. • MySQL is a freeware RDBMS and query language, widely used for Web-based applications. • A version of MySQL is installed with your Web server • We will learn to create, submit, and process MySQL queries in this chapter.. • First we need to learn how to start and stop MySQL, and to create a user account and add two tables to the test database for use in our examples.. Chapter 14

  11. Starting your MySQL Server • Before you can work with the MySQL RDBMS you must start your MySQL server. • Windows users: • Click the Start buttons for both ApacheandMySQL in the Control Panel to start both of these servers. • Remember to stop both servers before exiting the Control Panel. • Macintosh users running MAMP: • Run MAMP and check the MAMP control panel. It should indicate that both ApacheandMySQL servers are running. • Start the MySQL server if is not already running Chapter 14

  12. Configuring MySQLfor use with this Textbook • You only need to follow these instructions once (repeat if you ever mess up your tables): • Start your Web server and MySQL server • Open a Web browser and type http://localhostto connect to the Web server • Click the samplesfolder and run MySqlSetup.html • Thecomplete URL is http://localhost/samples/MySqlSetup.html • This program will create a MySQL user named wbip with a password wbip123, and will also create two tables in the test database, named personnel and timesheet • Follow these instructions for Mac or Windows Chapter 14

  13. Three Ways to Work with MySQL • From the command line (in your console window). • Through a graphical interface (the standard MySQL interface, included in your installation, is PHPMyAdmin. • From a custom application developed in a programming language such as PHP. •  In this chapter we will learn how to submit and process MySQL queries from custom PHP applications. • See the textbook Web site for help accessing MySQL from the command line, or using PHPMyAdmin. Chapter 14

  14. Working with PHP and MySQL • We will used PHP functions designed to submit and process MySQL queries. • In order to work with MySQL we must: • Open a connection to a MySQL server • Connect to a MySQL database • Submitqueries as needed and receive and handle the results of these queries • Closetheconnection Chapter 14

  15. Using PHP to OPEN a Connection to a MySQL Server • Provide four arguments to PHP mysqli_connect() • The URL of the MySQL RDBMS: localhost • A user ID that has been registered with the system: wbip • The user password: wbip123 • The name of the specific database we wish to work with: test $connect=mysqli_connect('localhost', 'wbip', 'wbip123', 'test'); Chapter 14

  16. Using PHP to CLOSE a Connection to a MySQL Server $connect=mysqli_connect('localhost', 'wbip', 'wbip123', 'test'); • The mysqli() function returns a reference to the connection which is assigned to $connect (can be any name). • This variable is then used in subsequent instructions whenever it is necessary to refer to the connection. • When we are finished using this database, we call the mysqli_close() function, using $connect as an argument: mysqli_close($connect); Chapter 14

  17. Using Variables to Store Connecting Values • $server = "localhost"; • $user = "wbip"; • $pw = "wbip123"; • $db = "test"; • $connect=mysqli_connect($server, $user, $pw, $db); • Next, test to see if $connect contains FALSE, which would indicate that the connection failed.. Chapter 14

  18. PHP Code Template:Connecting to a MySQL Database $server = "localhost"; $user = "wbip"; $pw = "wbip123"; $db = "test"; $connect=mysqli_connect($server, $user, $pw, $db); if(!$connect) { die("ERROR: Cannot connect to database $db on server $server using user name $user (".mysqli_connect_errno(). ", ".mysqli_connect_error().")"); } // place the code here to work with the database mysqli_close($connect); // close the connection Chapter 14

  19. The die() and exit() Functions and MySQL Error-reporting Functions if(!$connect) { die("ERROR: Cannot connect to database $db on server $server using user name $user (".mysqli_connect_errno().", ".mysqli_connect_error().")"); } • Tests if $connect is FALSE (connection has failed). • The die() function causes the application to terminate with any error message that you provide. Or you can use exit(). • The mysqli_connect_errno() function returns the standard MySQL error number for reference. • The mysqli_connect_error() function returns the standard MySQL error message. Chapter 14

  20. The MySQL SELECT Query • We are ready to use our MySQL code template to submit MySQL SELECT queries. • MySQL has its own syntax and commands. • The SELECT query is used to search a database for specific records and fields. • We will work with the personnel table in the test database which contains 10 employee records • Each record contains values in 5 fields: empID, firstName, lastName, jobTitle, and hourlyWage Chapter 14

  21. Table Example: personnel Chapter 14

  22. Using MySQL SELECT • To search for all fields in all records in the personnel table: SELECT * FROM personnel • The asterisk * indicates all fields. • FROM is followed by the table(s) to be searched. • The results that the RDBMS returns are the resultset. • In this example the result set will contain the values from the empID, firstName, lastName, jobTitle, and hoursWorked fields from all of the 10 records in this table. Chapter 14

  23. Selecting Specific Fields • To constrain our search to a specific field: SELECT lastName FROM personnel •  The result set will only contain the values from the firstName field in all of the 10 records in this table. • To constrain our search to a number of fields: SELECT firstName, lastName FROM personnel •  The result set will only contain the values from the firstName and lastName fields in all of the 10 records. Chapter 14

  24. Selecting Specific Records • Add a WHERE clause to select specific records: SELECT * FROM personnel WHERE jobTitle='Accountant‘ • Returns all fields in records where job title is ‘Accountant’ • This query may return 0 or more records • Similarly, use empID to search for a single employee: SELECT * FROM personnel WHERE empID=‘12347' Chapter 14

  25. Selecting Specific Fields AND Records • To obtain the empID, firstName and lastName of all accountants: SELECT empID, firstName, lastName FROM personnel WHERE jobTitle='accountant' • Note the syntax: • Use of SELECT, FROM and WHERE • Use of Commas to separate items in a list • Use of single quotes to enclose field values • Use of = to search for specific fields, NOT == Chapter 14

  26. Relational Operators in MySQL • MySQL syntax includes the relational operators: SELECT firstName, lastName FROM personnel WHERE hourlyWage < '15.00' • This test obtains the firstName and lastName in all records where hourlyWage is lessthan 15.00. • MySQL syntax includes the standard relational operators: = <= < > >= != • MySQL also allows other relational operators.. Chapter 14

  27. The MySQL BETWEEN Operator SELECT firstName, lastName FROM personnel WHERE hourlyWage BETWEEN '10.00' AND '15.00' • The BETWEEN operator finds values between two values • This test obtains the firstName and lastName in all records where hourlyWage is greaterthan 10.00 and lessthan 15.00. Chapter 14

  28. The MySQL LIKE Operator • The LIKE operator is used for pattern-matching. • This operator can include wildcard characters * and _ SELECT firstName, lastName FROM personnel WHERE firstName LIKE 'Ann%' • The % operator allows 0 or more characters in this location. • This test obtains the firstName and lastName in all records where the firstNamebegins with Ann. • LIKE 'Ann%' will find 'Ann', 'Anne', 'Anney', 'Annette', etc. Chapter 14

  29. The MySQL LIKE Operator • The '_' wildcard character allows exactly one unknown character in a specific location. • You can combine wildcards in anycombination: SELECT firstName, lastName FROM personnel WHERE lastName LIKE '%m_t%‘ • Finds the firstName and lastName in all records where lastName begins with 0 or more characters, followed by 'm' followed by exactly one character, followed by 't', followed by 0 or more characters. • EXAMPLES: 'Smith' or 'Lamotte' or 'Mitchell' but NOT 'Smart' or 'Stormont‘ .. Why not? Chapter 14

  30. The Logical Operators AND and OR • Use AND and OR to combine tests, similar to PHP.. • To obtain the first and last names of employees who are either accountants or sales people: SELECT firstName, lastName FROM personnel WHERE jobTitle ='accountant' OR jobTitle = 'sales' •  To obtain the first and last names of accountants who earn less than 25.00 an hour: SELECT firstName, lastName FROM personnel WHERE jobTitle ='accountant' AND hourlyWage < '25.00' Chapter 14

  31. Gotchas When Using AND and OR • Be careful when to use AND or OR in your WHERE clauses: WHERE jobTitle ='accountant' AND jobTitle = 'sales' • NojobTitle is both 'accountant' AND 'sales person'. This should be OR. • As in PHP, you must provide a complete test on either side of AND or OR: WHERE jobTitle ='accountant' OR 'sales' • Should be: WHERE jobTitle ='accountant' OR jobTitle ='sales' Chapter 14

  32. Ordering your Query Results • Use ORDER BY to order your results: SELECT * FROM personnel ORDER BY lastName SELECT firstName, lastName FROM personnel WHERE jobTitle ='accountant' ORDER BY lastName • The field you use to order the results does not need to be included in the result set: SELECT firstName, lastName FROM personnel ORDER BY jobTitle Chapter 14

  33. Ordering your Query Results • To order by two fields (result set will be ordered, first by the first field, and then by the second field): SELECT firstName, lastName FROM personnel ORDER BY lastName, firstName • To order results in descending order, add the DESC keyword after the field name(s), for example: SELECT firstName, lastName FROM personnel ORDER BY jobTitle DESC • You can also specify ASC but this is assumed by default. Chapter 14

  34. Viewing your Query Results in PHP • Use mysqli_query()to submit a query: $userQuery = "SELECT * FROM personnel";   $result = mysqli_query($connect, $userQuery); • $userQuery contains the query to be submited. • $result will receive the result of the query. • Remember to open a connection to the database before using mysqli_query() to submit any queries! • Note that the first argument is $connect which references the database connection. • The second argument is the query to be submitted. Chapter 14

  35. Testing the Query Result • If there is an error and the query cannot be processed (no connection to the database, MySQL syntax error, etc), then msqli_query() returns false. • If successful, function returns a result set with the requested fields of the 0 or more records that satisfy the query. • Before processing the result set, we want to: • Test $result to see if there was an error. • Use the mysqli_num_rows() function to test if the query found 0 records.. Chapter 14

  36. Code: Testing the Query Result if (!$result) { die("Could not successfully run query ($userQuery) from $db: ".mysqli_error($connect) ); } if (mysqli_num_rows($result) == 0) print("No records were found with query userQuery"); else { // process the result set } Chapter 14

  37. Processing the Result Set • There are different ways to extract the records from the result set. • The mysqli_fetch_assoc() function extracts the next record from the result set as an associative array, or returns false if no more records are found. • Each array element contains a field value, and each array key contains the field name. • The mysqli_fetch_assoc() function can be used to control a loop which will continue to extract records from the result set until no more records are found.. Chapter 14

  38. Processing the Result Set • Here is the code to process the results of "SELECT firstName, lastName FROM personnel" print("<h1>LIST OF EMPLOYEES</h1>"); while($row = mysqli_fetch_assoc($result)) { print ("<p>".$row['firstName']. " " .$row['lastName']."</p>"); } Chapter 14

  39. Processing the Result Set while ($row = mysqli_fetch_assoc($result)) • Each time the lop repeats, $row is assigned the next record in the result set as an associative array. • The result set of this query contains just two fields: • $row['firstName'] contains the first name • $row['lastName'] contains the last name • If you want $row to contain other fields (such as empID), you must modify your original query: "SELECT empID, firstName, lastName FROM personnel" Chapter 14

  40. Displaying the Results as an HTML Table print("<h1>LIST OF EMPLOYEES</h1>");   print("<table border = \"1\">"); print("<tr><th>First Name</th><th>Last Name</th></tr>"); while ($row = mysqli_fetch_assoc($result)) { print ("<tr><td>".$row['firstName']. "</td><td>".$row['lastName']."</td></tr>"); } print("</table"); Chapter 14

  41. Complete Code for mysql1.php (Slide 1 of 3) $server = "localhost"; $user = "wbip"; $pw = "wbip123"; $db = "test"; $connect=mysqli_connect($server, $user, $pw, $db); if( !$connect) { die("ERROR: Cannot connect to database $db on server $server using user name $user (".mysqli_connect_errno().", ".mysqli_connect_error().")"); } Chapter 14

  42. Complete Code for mysql1.php(Slide 2 of 3) $userQuery = "SELECT firstName, lastName FROM personnel"; $result = mysqli_query($connect, $userQuery);   if (!$result) { die("Could not successfully run query ($userQuery) from $db: ".mysqli_error($connect) ); }   if (mysqli_num_rows($result) == 0) { print("No records found with query $userQuery"); } Chapter 14

  43. Complete Code for mysql1.php(Slide 3 of 3) else { print("<h1>LIST OF EMPLOYEES</h1>");   print("<table border = \"1\">"); print("<tr><th>First Name</th><th>Last Name</th></tr>");   while ($row = mysqli_fetch_assoc($result)) print ("<tr><td>".$row['firstName']. "</td><td>".$row['lastName']."</td></tr>"); print("</table"); }   mysqli_close($connect); // close the connection Chapter 14

  44. Using Input from an HTML Form to Construct a Query (mysql4.php) • The form in mysql4.html asks the user to submit a last name to search for in the personnel table • mysql4.php constructs a query using the last name: $searchName = $_POST['searchName']; $userQuery = "SELECT * FROM personnel WHERE lastName ='$searchName'"; Chapter 14

  45. Processing Queries with a Single Result (mysql5.php) • IF a query will not require more than a single record, a WHILE loop is not required for processing: $userQuery = "SELECT jobTitle, hourlyWage FROM personnel WHERE empID='12347'"; $row = mysqli_fetch_assoc($result); print ("<p>ID: ".$searchID."<br />Job title: ".$row['jobTitle']."<br />HourlyWage: $".number_format($row['hourlyWage'], 2)."</p>"); Chapter 14

  46. Performing Calculations with the Result Set (mysql6.php $userQuery = "SELECT firstName, lastName, hourlyWage FROM personnel WHERE jobTitle='cleaner'"; print("<h1>PAY CHECKS</h1>"); while ($row = mysqli_fetch_assoc($result)) { $weeklyPay = $row['hourlyWage'] * 35; print ("<p>PAY TO: ".$row['firstName']. " ".$row['lastName']." THE SUM OF $". number_format($weeklyPay,2)."</p>"); } Chapter 14

  47. Performing Aggregate Operations on MySQL Queries (mysql7.php) • MySQL provides a number of aggregationfunctions, for example COUNT, SUM, AVG, MIN, MAX, • Use the functions instead of coding these calculations in PHP. • To find the average hourly wage of all employees: $userQuery = "SELECT AVG(hourlyWage) FROM personnel"; •  The average is stored in the associative array with the key name AVG(hourlyWage): $row = mysqli_fetch_assoc($result);   print ("<p>Average hourly wage: $".number_format($row['AVG(hourlyWage)'], 2)."</p>"); Chapter 14

  48. More Example of Aggregate Operations • Highest wage of accountants: $userQuery = "SELECT MAX(hourlyWage) FROM personnel WHERE jobTitle='accountant'"; • We must now refer to MAX(hourlyWage) in our print statement: print ("<p>Highest wage for accountants: $" .number_format($row[MAX(hourlyWage)'], 2)."</p>"); • Count the number of cleaners: $userQuery = "COUNT(empID) FROM personnel WHERE jobTitle='cleaner'"; • Now the print statement becomes: print ("<p>Number of cleaners: ".$row['COUNT(empID)']."</p>"); Chapter 14

  49. Performing JOIN Operations on Multiple Tables • Your MySQL test database also contains a Timesheet table: Chapter 14

  50. Performing JOIN Operations on Multiple Tables • An RDBMS allows queries on multiple tables • The personnel and timesheet tables both include an empID field. • This allows us to relate the two tables based on the employee's ID • This type of action is termed a JOIN • For example we can associate the hours worked by an employee record in the timesheet table with the same employee's firstName, lastName, and hourlyWage in the personnel table by looking for records with the sameempID in each table… Chapter 14

More Related