PHP%20and%20MySQL

1

PHP and MySQL
2
Topics
PHP and MySQL
Querying Data with PHP User-Driven Querying Writing Data with PHP and MySQL
3
Querying Data with PHP
PHP and MySQL
Opening and Using a Database Error Handling of MySQL Database Functions Formatting Results Using Include Files
4
Opening and Using a Database
PHP and MySQL
In PHP, there is no consolidated interface. A set of library functions are provided for executing SQL statements, as well as for managing result sets returned from queries, error handling, and setting efficiency options. Connecting to and querying a MySQL DBMS with PHP is a five-step process Connect to the DBMS and use a database. Open a connection to the MySQL DBMS using mysql_connect( ).
5
Opening and Using a Database
PHP and MySQL
There are three parameters The hostname of the DBMS server to use A username A password Once you connect, you can select a database to use through the connection with the mysql_select_db( ) function. The function mysql_connect( ) returns a connection handle. A handle is a value that can be used to access the information associated with the connection.
6
Opening and Using a Database
PHP and MySQL
Run the query using mysql_query( ). The function takes two parameters The SQL query itself The DBMS connection to use The connection parameter is the value returned from the connection in the first step. The function mysql_query( ) returns a result set handle resource that is, a value that can retrieve the outputthe result setof the query in Step 3.
7
Opening and Using a Database
PHP and MySQL
Retrieve a row of results. The function mysql_fetch_row( ) retrieves one row of the result set. The function takes only one parameter The result set handle from the second step Each row is stored in an array row, and the attribute values in the array are extracted in Step 4. A while loop is used to retrieve rows until there are no more rows to fetch. The function mysql_fetch_row( ) returns false when no more data is available.
8
Opening and Using a Database
PHP and MySQL
Process the attribute values. For each retrieved row, a for loop is used to print with an echo statement each of the attributes in the current row. Use mysql_num_fields( ) is used to return the number of attributes in the row that is, the number of elements in the array. The function takes only one parameter The result set handle from the second step The data itself is stored as elements of the array row returned in Step 3.
9
Opening and Using a Database
PHP and MySQL
Close the DBMS connection using mysql_close( ) The function takes only one parameter The connection to be closed. Example 4-1 Example 4-2 Example 4-3 Example 4-4
10
Error Handling of MySQL Database Functions
PHP and MySQL
Database functions can fail. There are several possible classes of failure, ranging from criticalthe DBMS is inaccessible or a fixed parameter is incorrect to recoverable, such as a password being entered incorrectly by the user. Example 4-5
11
Formatting Results
PHP and MySQL
Basic techniques for connecting to and querying a MySQL DBMS using PHP can be extended to produce results with embedded HTML that have both better structure and presentation. Example 4-6
12
Using Include Files
PHP and MySQL
Include directives allow common functions in other files to be accessible from within the body of a script without directly adding the functions to the code. Example 4-7 Example 4-8
13
User-Driven Querying
PHP and MySQL
User Input Passing Data with URLs Passing Data with the HTML ltformgt Environment Passing Data with Embedded Links How PHP Initializes Variables Querying with User Input Combined Scripts
14
User Input
PHP and MySQL
Three techniques can be used to pass data that drives the querying process in a web database application Manual entry of a URL to retrieve a PHP script resource and provide parameters to the resource. For example, a user may open a URL using the Open Page option in the File menu of the Netscape web browser. Data entry through HTML ltformgt environments. For example, ltformgt environments can capture textual input, and input is made by selecting radio buttons, selecting one or more items from a drop-down select list, clicking on buttons, and through other data entry widgets.
15
User Input
PHP and MySQL
Embedded hypertext links that can be clicked to retrieve a PHP script resource and provide parameters to the script.
16
Passing Data with URLs
PHP and MySQL
Before the script is processed by the PHP scripting engine, variables associated with any parameters to the resource are initialized and assigned values. Example 5-1
17
Passing Data with the HTML ltformgt Environment
PHP and MySQL
The second technique that captures data passed from a browser to a server is the HTML ltformgt environment. Example 5-2
18
Passing Data with Embedded Links
PHP and MySQL
The third technique that passes data from a web browser to a web server is embedding links in an HTML document. This technique runs queries in most web database applications and is conceptually similar to manually entering a URL. Example 5-3
19
How PHP Initializes Variables
PHP and MySQL
When the PHP script engine is invoked, the engine declares and initializes variables in a predefined order. The automatic initialization feature works in this order By default, environment variables are initialized first. Variables are initialized from query string parameters passed with the GET method. POST method parameters are initialized. Variables from cookies are initialized.
20
How PHP Initializes Variables
PHP and MySQL
The Apache server internal variables are initialized. The initialization order can be changed from the default by adjusting the variables_order setting in php.ini.
21
Querying with User Input
PHP and MySQL
To introduce querying with user input, we begin by explaining a script that retrieves the wines made in a wine region that is specified by a user. Example 5-5.
22
Combined Scripts
PHP and MySQL
Some approaches separates the HTML ltformgt and the PHP processing script into two files. It is more common to implement both in the same script where the code can produce a ltformgt or run a query, depending if user parameters are supplied. If the script is called with no parameters, the script produces a ltformgt for user input and, if it is called with input from the ltformgt, it runs the query. This is called a combined script. Example 5-6.
23
Writing Data with PHP and MySQL
PHP and MySQL
Database Inserts, Updates, and Deletes Uploading and Inserting Files into Databases Updating data Deleting data
24
Database Inserts, Updates, and Deletes
PHP and MySQL
Simple database insertions and updates are much the same as queries. Inserting, updating, and deleting data does require some additional care. Example 6-1
25
Inserting data
PHP and MySQL
Phase one of the insertion process is data entry. Example 6-5 The second phase of insertion is data validation and then the database operation itself. Example 6-6
26
Updating data
PHP and MySQL
Updating data is usually a more complex process than inserting it. A three-step process for updates is used in most web database applications Using a key value, matching data is read from the database. The data is presented to the user for modification. The data is updated by writing the modified data to the database, using the key value from the first step. Example 6-7 Example 6-8
27
Deleting data
PHP and MySQL
The basic principle of deletion is a two-step process Identify the row or rows to be deleted Remove the data with an SQL DELETE statement. As in an update, the first step requires a key value be provided, and any technique described for capturing keys in updates can be used. We assume here that a unique, primary key value for the row to be deleted is available. Example 6-8-1