PHP Databases Introduction This 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 connection To 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 database To 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 query is 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 based on 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 dealing with 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 be the following OR respectively the query string would 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 results To 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 row returned from the query where var is the key and val is the value Here is how the output may appear in the browser ID 425 427 832 First Name Donald Alan Larry Last Name Knuth Turing Page Closing the database connection To close an existing database connection use the mysql close function If you have multiple connections open you can provide the linkname obtained from mysql connect to close a specific connection You are not required to explicitly close the connection However there are two advantages for doing so 1 Computer resources are saved because memory is required to maintain a pointer to an open database connection 2 Database connections remain available The database server allows a limited number of connections If there are several clients holding connections that have not been closed new connections may be refused When the script
View Full Document
Unlocking...