IntroductionEstablishing a database connectionQuerying the databaseUsing query resultsClosing the database connectionPHP: DatabasesIntroductionThis document describes how to interact with MySQL databases by establishing a connection, making queries, manipulating the results of those queries, and closing the database connection.Establishing a database connectionTo connect to a database server, use the mysql_connect() function. Once the connection is established, select the name of the database with the mysql_select_db() function. The following example attempts to connect to the database server named “dbs.cs.utk.edu” using the username “my_username” and password “my_password”. Once connected, the script attempts to select the database named “db_name”.$connection = mysql_connect("dbs.cs.utk.edu", "my_username", "my_password");if ($connection) { $db = mysql_select_db("db_name"); if (!$db) print "Failed to select database 'db_name'.\n";} else { print "Failed to connect to the database server.\n";}Each script needs to open its own database connection through which to execute its database queries. The variable $connection is an SQL “linkname” that can be used to differentiate between connections to multiple SQL servers. When you call an SQL command, you can pass the linkname as an optional parameter to tell PHP which connection to use. However, you will typically only have a single connection and can therefore omit the linkname. By default PHP assumes that you are using the last connection established. The variable $db is a boolean value indicating whether or not the database was selected. Possible error conditions that would cause mysql_select_db() to fail include misspelling the database name or lacking access permissions to access the desired database.Querying the databaseTo make queries, use the mysql_query() function. This function returns a resource (i.e., MySQL result index), which contains a pointer to all the rows that matched the given query. The mysql_query() function returns false if the query is syntactically incorrect. NOTE: Do not rely on mysql_query() to return false if no rows match the given query. If the queryis syntactically correct, the function will return an empty resource that contains no rows.To count the number of rows returned by a SELECT query, use the mysql_num_rows() function:$result = mysql_query("SELECT * FROM employees");$numrows = mysql_num_rows($result);print "There are $numrows people in the employees table\n";To count the number of rows affected by an UPDATE, DELETE, or INSERT query, use the mysql_num_affected() function. Queries do not have to be hard-coded: They can be based on PHP variables. The following example performs a query basedon input provided via a Web form (using the GET method).$target_name = $_GET['fname'];$result = mysql_query("SELECT * FROM employees WHERE first_name = '$target_name';");Notice that strings, such as $target_name, must be enclosed in single quotation marks even though the entire query is already enclosed in double quotation marks. To ensure that the content within variables is escaped, use the mysql_real_escape_string() function:$name = "O'Reilly";$target_name = mysql_real_escape_string($name);$unsafe_query = "SELECT * FROM employees WHERE last_name = '$name';");$safe_query = "SELECT * FROM employees WHERE last_name = '$target_name';");print "Unsafe: $unsafe_query\n";print "Safe: $safe_query\n";Here is the output from the above script:SELECT * FROM employees WHERE last_name = 'O'Reilly';SELECT * FROM employees WHERE last_name = 'O\'Reilly';The single-quotation mark has a special meaning within MySQL queries; therefore, caution must be exercised when dealingwith strings that may contain them. The following example demonstrates how non-escaped input can be used to carry out a SQL injection attack.$query = "SELECT * FROM users WHERE user='{$_POST['user']}' AND password='{$_POST['passwd']}'";mysql_query($query);If the values for $_POST['user'] and $_POST['passwd'] were dknuth and ' OR ''=' respectively, the query string would be the following:SELECT * FROM users WHERE user='dknuth' AND password='' OR ''='';This statement selects all rows in the users table where user matches the string dknuth. The password validation component is short-circuited because ''='' part resolves to “true,” thus making the right-hand side of the AND expression always “true.” In other words MySQL interprets the above query as:SELECT * FROM users WHERE user='dknuth' AND (password='' or true);The overall effect is that a hacker could log in as dknuth without having to provide a valid password.Using query resultsTo read data from the MySQL result index, use the mysql_fetch_assoc() function. This function converts a single row from the result to an associative array, where each column name becomes a key and the corresponding data in that column becomes the value. To process the next row (if it exists), call mysql_fetch_assoc() again. If there are no more rows to process, mysql_fetch_assoc() returns false. If there are conflicting column names in the result (e.g., because you performed a JOIN on two tables that have the same column name, such as a name column for student and course), you should instead call mysql_fetch_array(). It will return an array that can be indexed via either column names or numeric indices, where an index corresponds to a column’s numeric position in the array. When a result has conflicting column names, the associative index returns the last column with that name.Suppose there is a table named employees that has three columns: id, first_name, and last_name. The following example will display all rows in the table as HTML markup.$result = mysql_query("SELECT * FROM employees");if ($result && (mysql_num_rows($result) > 0)) { print "<table><tr><th>ID</th><th>First Name</th><th>Last Name</th></tr>\n"; while ($row = mysql_fetch_assoc($result)) { print "<tr>"; foreach($row as $var => $val) print "<td>$val</td>\n"; print "</tr>\n"; } print "</table>\n";} else { print "<p>There are no employees listed.</p>\n";}The associative array $row has three keys – "id", "first_name", and "last_name". The foreach loop enumerates each rowreturned from the query, where $var is the key and $val is the value.Here is how the output may appear in the browser:ID First Name Last Name425 Donald Knuth427 Alan Turing832 Larry PageClosing the database connectionTo close an existing database
View Full Document
Unlocking...