MySQL in a Nutshell (2008)
Part IV. APIs and Connectors
Chapter 19. PHP API
One of the most popular programming language and database engine combinations for the Web is PHP with MySQL. This combination works well for many reasons, but primarily because of the speed, stability, and simplicity of both applications. The first part of this chapter provides a basic tutorial on how to connect to and query MySQL with PHP. Following the tutorial is a reference of PHP MySQL functions in alphabetical order. For the examples in this chapter, I use the database of a fictitious computer support business. This database contains one table with client work requests (workreq) and another with client contact information (clients).
Using PHP with MySQL
This section presents the basic tasks you need to query a MySQL database from PHP. Prior to PHP 5, MySQL was enabled by default. As of PHP 5, it’s not enabled and the MySQL library is not packaged with PHP. To enable MySQL with PHP, you need to configure PHP with the --with-mysql[=/path_to_mysql] option.
Connecting to MySQL
For a PHP script to interface with MySQL, the script must first make a connection to MySQL, thus establishing a MySQL session. To connect to the fictitious database workrequests, a PHP script might begin like this:
<?php
$host = 'localhost';
$user = 'russell';
$pw = 'dyer';
$db = 'workrequests';
mysql_connect($host, $user, $pw)
or die(mysql_error);
mysql_select_db($db);
?>
This excerpt of PHP code starts by establishing the variables with information necessary for connecting to MySQL and the database. After that, PHP connects to MySQL by giving the host and user variables. If it’s unsuccessful, the script dies with an error message. If the connection is successful, the workrequests database is selected for use. Each PHP script example in this chapter begins with an excerpt of code like this one.
Querying MySQL
In the fictitious database is a table called workreq that contains information on client work requests. To retrieve a list of work requests and some basic information on clients, a PHP script begins by connecting to MySQL, as shown in the previous script excerpt. That is followed by the start of a web page and then the invocation of an SQL statement to retrieve and display the data. You can achieve this with code such as the following:
... // Connect to MySQL
<html>
<body>
<h2>Work Requests</h2>
<?php
$sql_stmnt = "SELECT wrid, client_name,
wr_date, description
FROM workreq, clients
WHERE status = 'done'
AND workreq.clientid = clients.clientid";
$results = mysql_query($sql_stmnt)
or die('Invalid query: ' . mysql_error());
while($row = mysql_fetch_row($results)) {
list($wrid, $client_name, $wr_date, $description) = $row;
print "<a href='detail.php?wrid=$wrid'>$client_name -
$desription ($wr_date)</a><br/>";
}
mysql_close( );
?>
</body>
</html>
After connecting to MySQL (substituted with ellipses here) and starting the web page, a variable ($sql_stmnt) containing the SQL statement is created. Then the database is queried with the SQL statement, and a reference to the results set is stored in a variable ($results). The query is followed by an or statement, a common PHP syntax for error checking. The print statement executes only if no results are found.
Assuming PHP was successful in querying the database, a while statement is used to loop through each row of data retrieved from MySQL. With each pass, using the mysql_fetch_row() function, PHP will temporarily store the fields of data for each row in an array ($row). Within the code block of the while statement, the PHP list() function parses the elements of the $row array into their respective variables. The variables here are named to match their column counterparts. This is not necessary, though—they can be named anything. The array could even be used as it is and the appropriate sequence number referenced to retrieve data. For instance, for the date of the work request, $row[2] could be used because it’s the third in the sequence (0 is first). Naming the variables as they are here, though, makes it easier to read the code and easier for others to follow later.
The second line of code within the while statement displays the data in the format required for the web page. The data is wrapped in a hyperlink with a reference to another PHP script (details.php), which will retrieve all of the details for the particular work request selected by a user. That work request will be identified by the work request number (i.e., wrid), which is a key column for the details.php PHP script. Typically, the value for wrid will automatically be placed in a variable by the same name ($wrid) regardless of what the variable is named in this script. It’s based on the name given in the hyperlink or anchor tag. This will happen if the php.ini configuration file has register_globals set to on, something that is not the case in recent versions of PHP. On Unix and Linux systems, this file is located in the /etc directory. On a Windows system, it’s usually found in the c:\windows directory. If not, the value can be referenced using the $_GET associative array, which is describe in PHP’s online documentation (http://www.php.net).
The output of this script is a line for each incomplete work request found in the database. Each line will be linked to another script that presumably can provide details on the work request selected. In this simple example, only a few of the many PHP MySQL functions are used to display data. In the next section of this chapter, each function is described with script excerpts that show how they are used.
PHP MySQL Functions in Alphabetical Order
The rest of this chapter contains a list of PHP MySQL functions in alphabetical order. Each function is given with its syntax and an explanation. An example script, or script excerpt, is provided to show how you can use the function. To save space, almost all of the script excerpts are shown without the lines of code necessary to start a PHP script and to connect to MySQL, and without the lines that should follow to close the connection and to end the script. For an example showing how to write these opening and closing lines, see the tutorial in the previous section.
Name
mysql_affected_rows()
Synopsis
int mysql_affected_rows([connection])
This function returns the number of rows affected by a previous SQL statement that modified rows of data for the current MySQL session. The function returns –1 if the previous statement failed. It works only after INSERT, UPDATE, and DELETE statements. See mysql_num_rows() later in this section for the number of rows returned by a SELECT statement. The connection identifier may be given as an argument to retrieve the number of rows affected by a different connection. Here is an example:
...
$sql_stmnt = "UPDATE workreq
SET due_date = ADDDATE(due_date, INTERVAL 1 DAY)
WHERE due_date = '2004-07-28'";
mysql_query($sql_stmnt);
$updated = mysql_affected_rows( );
print "Number of Rows Updated: $updated \n";
...
This script changes the due dates for all work requests by one day.
Name
mysql_change_user()
Synopsis
int mysql_change_user(user, password[, database, connection])
This function can be used to change the username for a MySQL connection. The new username is given as the first argument and the password for that user as the second. A different database from the one in use may be given as a third argument. You can change the user information for a different MySQL connection by specifying it as the fourth argument. If the function is successful, it returns true; if it’s unsuccessful, it returns false. This function is no longer available as of version 4 of PHP. Instead, you should establish a new connection with a different user by using themysql_connect() function.
Name
mysql_client_encoding()
Synopsis
string mysql_client_encoding([connection])
This function returns the name of the default character set for the current MySQL connection or, if connection is supplied, for that connection. Here is an example:
...
$info = mysql_client_encoding( );
print "Encoding in Use: $info \n";
...
Here are the results of this script on my server:
Encoding in Use: latin1
Name
mysql_close()
Synopsis
bool mysql_close([connection])
This function closes the current or last MySQL connection, or a given connection. The function returns true if it’s successful, and false if it’s unsuccessful. This function will not close persistent connections started with mysql_pconnect(). Here is an example:
...
$connection = mysql_connect('localhost', 'ricky', 'adams');
mysql_select_db('workrequests', $connection);
...
mysql_close($connection);
...
If a script has opened only one connection to MySQL, it’s not necessary to specify the connection link to close as shown here.
Name
mysql_connect()
Synopsis
mysql_connect(server[:port|socket], user, password[,
new_link, flags])
Use this function to start a MySQL connection. The first argument of the function is the server name. If none is specified, localhost is assumed. A port may be specified with the server name (separated by a colon) or a socket along with its path. If no port is given, port 3306 is assumed. The username is to be given as the second argument and the user’s password as the third. If a connection is attempted that uses the same parameters as a previous one, the existing connection will be used and a new connection link will not be created unless new_link is specified as the fourth argument of this function. As an optional fifth argument, client flags may be given for the MySQL constants MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE, MYSQL_CLIENT_INTERACTIVE, and MYSQL_CLIENT_SSL. The function returns a connection identifier if successful; it returns false if it’s unsuccessful. Use mysql_close() to close a connection created by mysql_connect(). Here is an example:
#!/usr/bin/php -q
<?
mysql_connect('localhost', 'ricky', 'adams');
mysql_select_db('workrequests');
...
To be able to identify the connection link later, especially when a script will be using more than one link, capture the results of mysql_connect(). Here is a complete script that sets up two connections to MySQL and captures the resource identification number for each link:
#!/usr/bin/php -q
<?
$user1 = 'elvis';
$user2 = 'fats';
$connection1 = mysql_connect('localhost', $user1, 'ganslmeier123');
$connection2 = mysql_connect('localhost', $user2, 'holzolling456');
mysql_select_db('workrequests', $connection1);
mysql_select_db('workrequests', $connection2);
counter($connection1,$user1);
counter($connection2,$user2);
function counter($connection,$user) {
$sql_stmnt = "SELECT * FROM workreq";
$results = mysql_query($sql_stmnt, $connection);
if(mysql_errno($connection)){
print "Could not SELECT with $connection for $user. \n";
return;
}
$count = mysql_num_rows($results);
print "Number of Rows Found with $connection for $user:
$count. \n";
}
mysql_close($connection1);
mysql_close($connection2);
?>
In this example, two links are established with different usernames. The counter() subroutine is called twice, once with each connection identifier and username passed to the user-defined function. For the first connection, the user elvis does not have SELECT privileges, so the SQL statement is unsuccessful. An error is generated and the number of rows is not determined due to the return ending the function call. For the second connection, the user fats has the necessary privileges, so the function is completed successfully. Here is the output from running this script on my server:
Could not SELECT with Resource id #1 for elvis.
Number of Rows Found with Resource id #2 for fats: 528.
Name
mysql_create_db()
Synopsis
resource mysql_create_db(database[, connection])
Use this function to create a database in MySQL for the current connection. The name of the database to create is given as the first argument of the function. A different MySQL connection identifier may be given as a second argument. The function returns true if it’s successful, false if unsuccessful. This function is deprecated; use the mysql_query() function with the CREATE DATABASE statement instead. Still, here is an example:
...
mysql_create_db('new_db');
$databases = mysql_list_dbs( );
while($db = mysql_fetch_row($databases)) {
print $db[0] . "\n";
}
...
This script will create a new database and then display a list of databases to allow the user to confirm that it was successful.
Name
mysql_data_seek()
Synopsis
bool mysql_data_seek(connection, row)
Use this function in conjunction with the mysql_fetch_row() function to change the current row being fetched to the one specified in the second argument. The connection identifier is given as the first argument. The function returns true if it’s successful; false if it’s unsuccessful. Here is an example:
...
$sql_stmnt = "SELECT wrid, clientid, description
FROM workreq";
$results = mysql_query($sql_stmnt);
$count = mysql_num_rows($results);
if ($count > 6) mysql_data_seek($results, $count - 6);
$row = mysql_fetch_row($results);
while($row = mysql_fetch_object($results)) {
print "WR-" . $row->wrid . " Client-" . $row->clientid .
" - " . $row->description . "\n";
}
...
In this script excerpt, the SQL statement is selecting the work request identification numbers for all rows in the table. The results set is stored in $results. Using the mysql_num_rows() function, the number of rows is determined and placed in the $count variable. To be able to display only the last five work requests, the script calls mysql_data_seek(). The results set is given as the first argument. In order to get the first row of a results set, the offset would be set to 0—so if a results set contains only one row, the row count of 1 minus 1 would need to be given as the second argument of mysql_data_seek(). For the example here, to get the last five records of the results set, the number of rows is reduced by six to move the pointer to the row before the fifth-to-last row. Here is the last line of the output of this script:
WR-5755 Client-1000 - Can't connect to network.
Name
mysql_db_name()
Synopsis
string mysql_db_name(databases, number)
This function returns the name of the database from the results of the mysql_list_dbs() function, which returns a pointer to a results set containing the names of databases for a MySQL server. The reference to the list of databases is given as the first argument. A number identifying the row to retrieve from the list is given as the second argument. Here is an example:
...
$databases = mysql_list_dbs( );
$dbs = mysql_num_rows($databases);
for($index = 0; $index < $dbs; $index++) {
print mysql_db_name($databases, $index) . "\n";
}
...
In this script excerpt, a results set containing a list of databases is retrieved and stored in the $databases variable using the mysql_list_dbs() function. That results set is analyzed by mysql_num_rows() to determine the number of records (i.e., the number of database names) that it contains. Using a for statement and the number of databases ($dbs), the script loops through the results set contained in $databases. With each pass, mysql_db_name() extracts the name of each database by changing the second argument of the function as the value of $indexincrements from 0 to the value of $dbs.
Name
mysql_db_query()
Synopsis
resource mysql_db_query(database, sql_statement[, connection])
This function can be used to query the database given—for the current MySQL connection, unless another is specified—and to execute the SQL statement given as the second argument. If there isn’t currently a connection to the server, it will attempt to establish one. For SQL statements that would not return a results set (e.g., UPDATE statements), the function will return true if it's successful and false if it’s unsuccessful. This function is deprecated, so use mysql_query() instead. Here is an example:
...
$sql_stmnt = "SELECT wrid, clientid, description
FROM workreq";
$results = mysql_db_query('workrequests', $sql_stmnt);
while($row = mysql_fetch_object($results)) {
print "WR-" . $row->wrid . ",
Client-" . $row->clientid . " " .
$row->description . "\n";
}
...
Basically, using mysql_db_query() eliminates the need to use mysql_select_db() and mysql_query().
Name
mysql_drop_db()
Synopsis
bool mysql_drop_db(database[, connection])
Use this function to delete the database given from the MySQL server. A different connection identifier may be given as a second argument. This function returns true if it’s successful, and false if it’s unsuccessful. This function has been deprecated; use the mysql_query() function with aDROP DATABASE statement instead. Here is an example:
...
mysql_dropdb('old_db');
...
Name
mysql_errno()
Synopsis
int mysql_errno([connection])
This function returns the error code number for the last MySQL statement issued. The function returns 0 if there was no error. Another MySQL connection identifier may be given as an argument for the function. Here is an example:
...
$sql_stmnt = "SELECT * FROM workreqs";
$results = mysql_db_query('workrequests', $sql_stmnt)
or die (mysql_errno( ) . " " . mysql_error( ) . "\n");
$count = mysql_num_rows($results);
print "Number of Rows Found: $count \n";
...
I’ve intentionally typed the name of the table incorrectly in the preceding SQL statement. It should read workreq and not workreqs. Here is the result of this script:
1146 Table 'workrequests.workreqs' doesn't exist
Notice that the error number code is given by mysql_errno() and the message that follows it is given by mysql_error(), which provides an error message rather than a code.
Name
mysql_error()
Synopsis
string mysql_error([connection])
This function returns the error message for the last MySQL statement issued. It returns nothing if there was no error. Another MySQL connection identifier may be given as an argument for the function. See mysql_errno() earlier in this section for an example of how mysql_error()may be used.
Name
mysql_escape_string()
Synopsis
string mysql_escape_string(string)
This function returns the string given with special characters preceded by backslashes so that they are protected from being interpreted by the SQL interpreter. This function is used in conjunction with mysql_query() to help make SQL statements safe. However, it is deprecated, so usemysql_real_escape_string() instead. Here is an example:
...
$clientid = '1000';
$description = "Can't connect to network.";
$description = mysql_escape_string($description);
$sql_stmnt = "INSERT INTO workreq
(date, clientid, description)
VALUES(NOW( ), '$clientid', '$description')";
mysql_query($sql_stmnt);
...
The string contained in the $description variable contains an apostrophe, which would normally cause the SQL statement to fail because the related value in the SQL statement is surrounded by single quotes. Without mysql_escape_string(), an apostrophe would be mistaken for a single quote, which has special meaning in MySQL.
Name
mysql_fetch_array()
Synopsis
array mysql_fetch_array(results[, type])
This function returns an array containing a row of data from an SQL query results set. Data is also stored in an associative array containing the field names as the keys for the values. Field names are derived from either column names or aliases. To choose whether only an array or only an associative array is returned, or both are returned, you may give one of the following as a second argument to the function, respectively: MYSQL_NUM, MYSQL_ASSOC, or MYSQL_BOTH. This function is typically used with a loop statement to work through a results set containing multiple rows of data. When there are no more rows to return, it returns false, which typically triggers the end of the loop. Here is an example:
...
$sql_stmnt = "SELECT wrid, clientid, description
FROM workreq";
$results = mysql_query($sql_stmnt);
while($row = mysql_fetch_array($results)) {
print "WR-" . $row[0] . ", Client-" .
$row['clientid'] . " " . $row['description'] . "\n";
}
...
Notice that both methods of extracting data from the row fetched are used here: the work request number is retrieved using a standard array data retrieval method (i.e., placing the index number of the array element in square brackets); and the other pieces of data are retrieved using the associative array method (i.e., placing the field name and the key name in brackets).
Name
mysql_fetch_assoc()
Synopsis
array mysql_fetch_assoc(results)
This function returns an associative array containing a row of data from an SQL query results set. Field names of the results set are used as the keys for the values. Field names are derived from column names unless an alias is employed in the SQL statement. This function is typically used with a loop statement to work through a results set containing multiple rows of data. When there are no more rows to return, it returns false, which will end a loop statement. This function is synonymous with mysql_fetch_array() using MYSQL_ASSOC as its second argument. Here is an example:
...
$sql_stmnt = "SELECT wr_id, client_id, description
FROM workreq";
$results = mysql_query($sql_stmnt);
while($row = mysql_fetch_assoc($results)) {
print "WR-" . $row['wr_id'] . ", Client-" .
$row['client_id'] . " " . $row['description'] . "\n";
}
...
This loop is identical to the one for mysql_fetch_array() except that, with the mysql_fetch_assoc() function, the index for a standard array cannot be used to get the work request number—so the wr_id key for the associative array stored in $row has to be used instead.
Name
mysql_fetch_field()
Synopsis
object mysql_fetch_field(results[, offset])
This function returns an object containing information about a field from a results set given. Information is given on the first field of a results set waiting to be returned; the function can be called repeatedly to report on each field of a SELECT statement. A number may be given as the second argument to skip one or more fields. The elements of the object are as follows: name for column name; table for table name; max_length for the maximum length of the column; not_null, which has a value of 1 if the column cannot have a NULL value; primary_key, which has a value of 1 if the column is a primary key column; unique_key, which returns 1 if it’s a unique key; multiple_key, which returns 1 if it’s not unique; numeric, which returns 1 if it’s a numeric data type; blob, which returns 1 if it’s a BLOB data type; type, which returns the data type;unsigned, which returns 1 if the column is unsigned; and zerofill, which returns 1 if it’s a zero-fill column. Here is an example:
...
$sql_stmnt = "SELECT * FROM workreq LIMIT 1";
$results = mysql_query($sql_stmnt);
$num_fields = mysql_num_fields($results);
for ($index = 0; $index < $num_fields; $index++) {
$info = mysql_fetch_field($results, $index);
print "$info->name ($info->type $info->max_length) \n";
}
...
Here, all of the columns for one record are selected and placed in $results. The number of fields is determined by mysql_num_fields() for the for statement that follows. The for statement loops through each field of the results set and uses mysql_fetch_field() to return the field information in the form of an object. Then the example prints out the name of the field, the data type, and the maximum length. Here are the first few lines of the output from this script:
wr_id (int 4)
wr_date (date 10)
clientid (string 4)
...
Name
mysql_fetch_lengths()
Synopsis
array mysql_fetch_lengths(results)
This function returns an array containing the length of each field of a results set from a MySQL query. Here is an example:
...
$sql_stmnt = "SELECT wr_id, description, instructions
FROM workreq";
$results = mysql_query($sql_stmnt);
while($row = mysql_fetch_object($results)) {
$length = mysql_fetch_lengths($results);
print "$row->wr_id: description: $length[1],
instructions: $length[2] \n";
}
...
In this example, each work request number is selected, along with the brief description and the lengthy instructions. Looping through each row that is retrieved as an object with mysql_fetch_object() and a while statement, the code determines the length of the data for all three fields with mysql_fetch_lengths() and places them in an array. Within the statement block of the while statement, the value of the wr_id field is extracted, and the lengths of the description field and the instructions field are pulled out of the $length array using the relative index number for each. Here are a few lines of output from this script:
...
5753: description: 26, instructions: 254
5754: description: 25, instructions: 156
5755: description: 25, instructions: 170
Name
mysql_fetch_object()
Synopsis
object mysql_fetch_object(result)
This function returns a row of data as an object from the results set given. The function returns false if there are no more rows to return. The field names of the results set are used to retrieve data from the object returned. Here is an example:
...
$sql_stmnt = "SELECT count(wrid) AS wr_count, client_name
FROM workreq, clients
WHERE status <> 'done'
AND workreq.clientid = clients.clientid
GROUP BY workreq.clientid
ORDER BY wr_count DESC";
$results = mysql_query($sql_stmnt);
while($row = mysql_fetch_object($results)) {
print $row->client_name . " " . $row->wr_count . "\n";
}
...
This script is written to generate a list of clients that have outstanding work requests and to give a count of the number of requests for each, in descending order. Within the while statement that follows, each row of the results set is processed with mysql_fetch_object(). The value of each element of the object created for each row is displayed by calls using the field names, not the column names. For instance, to get the data from the field with the number of work requests, you use the wr_count alias. Here are a few lines from the output of this script:
...
Bracey Logistics 3
Neumeyer Consultants 2
Farber Investments 4
Name
mysql_fetch_row()
Synopsis
array mysql_fetch_row(results)
This function returns an array containing a row of data from a results set given. This function is typically used in conjunction with a loop statement to retrieve each row of data in a results set. Each loop retrieves the next row. Individual fields appear in the array in the order they appeared in the SELECT statement, and can be retrieved by an array index. The loop ends when rows are used up because the function returns NULL. Here is an example:
...
$sql_stmnt = "SELECT wr_id, client_name, description
FROM workreq, clients
WHERE workreq.clientid = clients.clientid";
$results = mysql_query($sql_stmnt);
while($row = mysql_fetch_row($results)) {
print "WR-$row[0]: $row[1] - $row[2] \n";
}
...
To get the data for each element of the $row array created by mysql_fetch_row(), you must know the number corresponding to each element. The index of the elements begins with 0, so $row[0] is the first element and, in this case, the work request number because wr_id was the first field requested by the SELECT statement. Here’s one line of the output from this script:
WR-5755: Farber Investments - Can't connect to Internet.
Name
mysql_field_flags()
Synopsis
string mysql_field_flags(results, offset)
This function returns the field flags for a field of a results set given. See mysql_fetch_field() earlier in this chapter for a description of the flags. Specify the desired field through the offset in the second argument. Here is an example:
...
$sql_stmnt = "SELECT * FROM workreq LIMIT 1";
$results = mysql_query($sql_stmnt);
$num_fields = mysql_num_fields($results);
for ($index = 0; $index < $num_fields; $index++) {
$field_name = mysql_field_name($results, $index);
$flags = explode(' ', mysql_field_flags($results, $index));
print "$field_name \n";
print_r($flags);
print "\n\n";
}
...
After retrieving one row as a sampler—using a for statement and the number of fields in the results set—this example determines the field name with mysql_field_name() and the flags for each field using mysql_field_flags(). The mysql_field_flags() function assembles the flags into an array in which the data is separated by spaces. By using the explode() PHP function, you can retrieve the elements of the array without having to know the number of elements, and they are stored in $flags. Next, print_r() displays the field name and prints out the flags. Here is the output of the script for the first field:
wrid
Array
(
[0] => not_null
[1] => primary_key
[2] => auto_increment
)
Name
mysql_field_len()
Synopsis
int mysql_field_len(results, index)
This function returns the length from a field of the results set given. Specify the desired field via the index in the second argument. Here is an example:
...
$sql_stmnt = "SELECT * FROM workreq LIMIT 1";
$results = mysql_query($sql_stmnt);
$num_fields = mysql_num_fields($results);
for ($index = 0; $index < $num_fields; $index++) {
$field_name = mysql_field_name($results, $index);
print "$field_name - " .
mysql_field_len($results, $index) . "\n";
}
...
Here, one row has been retrieved from a table and mysql_num_fields() determines the number of fields in the results set. With a for statement, each field is processed to determine its name using mysql_field_name() and the length of each field is ascertained withmysql_field_len(). Here are a few lines of the output of this script:
wrid - 9
wr_date - 10
clientid - 4
...
Name
mysql_field_name()
Synopsis
string mysql_field_name(results, index)
This function returns the name of a field from the results set given. To specify a particular field, the index of the field in the results set is given as the second argument—0 being the first field. Here is an example:
...
$sql_stmnt = "SELECT * FROM workreq LIMIT 1";
$results = mysql_query($sql_stmnt);
$num_fields = mysql_num_fields($results);
for ($index = 0; $index < $num_fields; $index++) {
$field_name = mysql_field_name($results, $index);
print $field_name . "\n";
}
...
The SQL statement here selects one row from the table. Then mysql_num_fields() examines the results of the query and determines the number of fields. The loop processes each field, starting with field 0 using the mysql_field_name() function to extract each field name. The second argument is changed as the $index variable is incremented with each loop.
Name
mysql_field_seek()
Synopsis
bool mysql_field_seek(results, index)
Use this function to change the pointer to a different field from the results set given. The amount by which to offset the pointer is given as the second argument. Here is an example:
...
$sql_stmnt = "SELECT * FROM workreq LIMIT 1";
$results = mysql_db_query('workrequests', $sql_stmnt,
$connection);
$num_fields = mysql_num_fields($results);
mysql_field_seek($results, $num_fields - 3);
for ($index = 0; $index < 3; $index++) {
$field = mysql_fetch_field($results, $index);
print "$field->name \n";
}
...
This example determines the number of fields and their values, and then gives the result as the second argument of the mysql_field_seek() function to choose the last three fields of the results set. The for statement prints out the field names of the last three fields usingmysql_fetch_field().
Name
mysql_field_table()
Synopsis
string mysql_field_table(results, index)
This function returns the name of the table that contains a particular field from the results set given. An offset for the field is given as the second argument. This is useful for a results set derived from an SQL statement involving multiple tables. Here is an example:
...
$sql_stmnt = "SELECT wrid, client_name, description
FROM workreq, clients
WHERE workreq.clientid = clients.clientid";
$results = mysql_query($sql_stmnt);
$num_fields = mysql_num_fields($results);
for ($index = 0; $index < $num_fields; $index++) {
$table = mysql_field_table($results, $index);
$field = mysql_field_name($results, $index);
print "$table.$field \n";
}
...
The SQL statement here selects columns from two different tables. Using mysql_field_table() inside of the for statement, the code determines the name of the table from which each field comes. The mysql_field_name() function gets the field’s name. Here are the results of this script:
workreq.wrid
clients.client_name
workreq.description
Name
mysql_field_type()
Synopsis
string mysql_field_type(results, index)
This function returns the column data type for a field from the results set given. To specify a particular field, give an offset as the second argument. Here is an example:
...
$sql_stmnt = "SELECT * FROM workreq LIMIT 1";
$results = mysql_query($sql_stmnt);
$num_fields = mysql_num_fields($results);
for ($index = 0; $index < $num_fields; $index++) {
$name = mysql_field_name($results, $index);
$type = mysql_field_type($results, $index);
print "$name - $type \n";
}
...
In this example, after one row of data is selected as a sample, mysql_num_fields() determines the number of rows in the results set so that a counter limit may be set up ($num_fields) in the for statement that follows. Within the for statement, the name of the field is extracted usingmysql_field_name() and the data type using mysql_field_type(). Here are a few lines of the output of this script:
wrid - int
wr_date - date
clientid - string
...
Name
mysql_free_result()
Synopsis
bool mysql_free_result(results)
Use this function to free the memory containing the results set given. The function returns true if it’s successful, and false if it’s unsuccessful. Here is an example:
...
mysql_free_result($results);
mysql_close( );
?>
There’s not much to this function. It merely flushes out the data for the location in memory referenced by the variable given.
Name
mysql_get_client_info()
Synopsis
string mysql_get_client_info()
This function returns the library version of the MySQL client for the current connection. Here is an example:
...
$info = mysql_get_client_info( );
print "Client Version: $info \n";
...
Here are the results of this script on one of my computers:
Client Version: 3.23.40
Name
mysql_get_host_info()
Synopsis
string mysql_get_host_info([connection])
This function returns information on the host for the current connection to MySQL. You may give an identifier to retrieve information on a host for a different connection. Here is an example:
...
$info = mysql_get_client_info( );
print "Connection Info: $info \n";
...
Here are the results of this script when you run it on the host containing the server:
Connection Info: 127.0.0.1 via TCP/IP
Name
mysql_get_proto_info()
Synopsis
int mysql_get_proto_info([connection])
This function returns the protocol version for the current connection to MySQL. You may give an identifier to retrieve the protocol version for a different connection. Here is an example:
...
$info = mysql_get_proto_info( );
print "Protocol Version: $info \n";
...
Here are the results of running this script:
Protocol Version: 10
Name
mysql_get_server_info()
Synopsis
string mysql_get_server_info([connection])
This function returns the MySQL server version for the current connection to MySQL. You may give an identifier to retrieve the server version for a different connection. Here is an example:
...
$info = mysql_get_server_info( );
print "MySQL Server Version: $info \n";
...
Here are the results of running this script:
MySQL Server Version: 4.1.1-alpha-standard
Name
mysql_info()
Synopsis
string mysql_info([connection])
This function returns information on the last query for the current connection to MySQL. You may give an identifier to retrieve information on a query for a different connection. Here is an example:
...
$sql_stmnt = "SELECT * FROM workreq";
$results = mysql_query($sql_stmnt);
print mysql_info( );
...
Here are the results of running this script:
String format: 528 rows in set
Name
mysql_insert_id()
Synopsis
int mysql_insert_id([connection])
This function returns the identification number of the primary key of the last record inserted using INSERT for the current connection, provided the column utilizes AUTO_INCREMENT and the value was not manually set. Otherwise, it returns 0. Here is an example:
...
$sql_stmnt = "INSERT INTO workreq
(date, clientid, description)
VALUES(NOW( ), '1000', 'Network Problem')";
mysql_query($sql_stmnt);
$wrid = mysql_insert_id( );
print "Work Request ID: $wrid \n";
...
Here is the output of this script:
Work Request ID: 5755
Name
mysql_list_dbs()
Synopsis
resource mysql_list_dbs([connection])
This function returns a pointer to a results set containing the names of databases hosted by the MySQL server. The mysql_db_name() function or any function that extracts data from a results set may be used to retrieve individual database names. Here is an example:
...
$databases = mysql_list_dbs( );
$dbs = mysql_num_rows($databases);
for($index = 0; $index < $dbs; $index++) {
print mysql_db_name($databases, $index) . "\n";
}
...
Name
mysql_list_fields()
Synopsis
resource mysql_list_fields(database, table[, connection])
This function returns a results set containing information about the columns of a table given for a database specified. The mysql_field_flags(), mysql_field_len(), mysql_field_name(), and mysql_field_type() functions can be used to extract information from the results set. An identifier may be given as a third argument to the function to retrieve information for a different MySQL connection. This function is deprecated, though. Use the mysql_query() function with the SHOW COLUMNS statement instead. Here is an example:
...
$fields = mysql_list_fields('workrequests', 'workreq');
$num_fields = mysql_num_fields($fields);
for ($index = 0; $index < $num_fields; $index++) {
print mysql_field_name($fields, $index) . "\n";
}
...
After connecting to MySQL, in the first line the example uses mysql_list_fields() to retrieve a list of column names from the database and table given as arguments. To assist the for statement that follows, the mysql_num_fields() function determines the number of fields in the results set, returning a field for each column. Then PHP loops through the for statement for all the fields and displays the name of each column using mysql_field_name(). Here are a few lines from the output of this script:
wrid
wr_date
clientid
...
Name
mysql_list_processes()
Synopsis
resource mysql_list_processes([connection])
This function returns a results set containing information on the server threads for the current connection: the connection identifier, the hostname, the database name, and the command. You may give an identifier to retrieve information for a different connection. Here is an example:
...
$processes = mysql_list_processes($connection);
while ($row = mysql_fetch_array($processes)){
print "$row['Id'], $row['Host'],
$row['db'], $row['Command']";
}
...
Name
mysql_list_tables()
Synopsis
resource mysql_list_tables(database[, connection])
This function returns a results set containing a list of tables for database. You may give an identifier as a second argument to retrieve information for a different connection. The mysql_tablename() function can be used to extract the names of the tables from the results set of this function. This function is deprecated, though. Use the mysql_query() function with the SHOW TABLES statement instead. Here is an example:
...
$tables = mysql_list_tables('workrequests');
$num_tables = mysql_num_rows($tables);
for($index = 0; $index < $num_tables ; $index++) {
print mysql_tablename($tables, $index) . "\n";
}
...
The first line shown here gives the database name as an argument for the mysql_list_tables() function. The results are stored in the $tables variable. Next, the number of rows and the number of tables found are determined and stored in $num_tables. Using a for statement to loop through the list of tables in the results set, each table name is printed out with the assistance of mysql_tablename(). The second argument of mysql_tablename() is adjusted incrementally by using the $index variable, which will increase from 0 to the value of the $num_tablesvariable.
Name
mysql_num_fields()
Synopsis
int mysql_num_fields(results)
This function returns the number of fields of the results set given. Here is an example:
...
$fields = mysql_list_fields('workrequests', 'workreq');
$num_fields = mysql_num_fields($fields);
for ($index = 0; $index < $num_fields; $index++) {
print mysql_field_name($fields, $index) . "\n";
}
...
As this example shows, mysql_num_fields() can be useful in conjunction with other functions. Here, a list of fields for a table is retrieved using mysql_list_fields(). In order to help the code display the names of the fields using a for statement, we need to determine the number of fields. The mysql_num_fields() function is handy for figuring out this bit of information.
Name
mysql_num_rows()
Synopsis
int mysql_num_rows(results)
This function returns the number of rows in the results set given, generated by issuing a SELECT statement. For other types of SQL statements that don’t return a results set, use mysql_affected_rows(). Here is an example:
...
$sql_stmnt = "SELECT * FROM workreq";
$results = mysql_query($sql_stmnt);
$count = mysql_num_rows($results);
print "Number of Rows Found: $count \n";
...
Name
mysql_pconnect()
Synopsis
resource mysql_pconnect(server[:port|socket], user, password[, flags])
Use this function to open a persistent connection to MySQL. The connection will not end with the closing of the PHP script that opened the connection, and it cannot be closed with mysql_close(). The first argument of the function is the server name. If none is specified, localhost is assumed. A port may be specified with the server name (separated by a colon) or a socket along with its path. If no port is given, port 3306 is assumed. The username is given as the second argument and the user’s password as the third. If you attempt a connection that uses the same parameters as a previous one, it uses the existing connection instead of creating a new connection. As an optional fourth argument, you can give client flags for the MySQL constants MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE, MYSQL_CLIENT_INTERACTIVE, andMYSQL_CLIENT_SSL. The function returns a connection identifier if it’s successful; it returns false if it’s unsuccessful.
Here is an example:
mysql_pconnect('localhost', 'russell', 'dyer');
Name
mysql_ping()
Synopsis
bool mysql_ping([connection])
Use this function to determine whether the current MySQL connection is still open. If it’s not open, the function attempts to reestablish the connection. If the connection is open or reopened, the function returns true. If the connection is not open and cannot be reestablished, it returns false. You may give an identifier to ping a different connection. Here is an example:
...
$ping = mysql_ping($connection);
print "Info: $ping \n";
...
This function is available as of version 4.3 of PHP.
Name
mysql_query()
Synopsis
resource mysql_query(sql_statement[, connection])
Use this function to execute an SQL statement given. You may give an identifier as a second argument to query through a different connection. The function returns false if the query is unsuccessful. For SQL statements not designed to return a results set (e.g., INSERT), the function returns trueif successful. If not successful, it returns a reference to a results set. Here is an example:
...
$sql_stmnt = "SELECT wrid, client_name, description
FROM workreq, clients
WHERE workreq.clientid = clients.clientid";
$results = mysql_query($sql_stmnt, $connection);
while($row = mysql_fetch_row($results)) {
print "WR-$row[0]: $row[1] - $row[2] \n";
}
...
Here’s one line from the output of this script:
WR-5755: Farber Investments - Can't connect to network.
Name
mysql_real_escape_string()
Synopsis
string mysql_real_escape_string(string[, link])
This function returns the string given with special characters preceded by backslashes so that they are protected from being interpreted by the SQL interpreter. Use this in conjunction with the mysql_query() function to make SQL statements safe. This function does not escape % or _ characters, but it does take into account the character set of the connection. A different connection may be specified as the second argument to the function. This function is similar to mysql_escape_string(), but it escapes a string based on the character set for the current connection.
Name
mysql_result()
Synopsis
string mysql_result(results, row[, field|offset])
This function returns the data from one field of a row from results. Normally, this statement returns the next row and can be reused to retrieve results sequentially. As a third argument, you can give either a field name (i.e., the column or alias name) or an offset to change the pointer for the function. This function is typically used in conjunction with a loop statement to process each field of a results set. Here is an example:
...
$sql_stmnt = "SELECT client_name FROM clients";
$results = mysql_query($sql_stmnt);
$num_rows = mysql_num_rows($results);
for ($index = 0; $index < $num_rows; $index++) {
print mysql_result($results, $index) . "\n";
}
...
This script queries the database for a list of client names. Using the mysql_num_row() function, the number of rows contained in the results set is determined. Using that bit of data, a for statement is constructed to loop through the results set using mysql_result() to extract one field of data per row. Otherwise, a function such as mysql_fetch_array() would have to be used in conjunction with the usual method of retrieving data from an array (e.g., $row[0]).
Name
mysql_select_db()
Synopsis
bool mysql_select_db(database[, connection])
This function sets the database to be used by the current MySQL connection, but you also can use it to set the database for another connection by supplying it as a second argument. The function returns true if it’s successful, and false if it’s unsuccessful. Here is an example:
...
$connection = mysql_connect('localhost','tina','muller');
mysql_select_db('workrequests', $connection);
...
Name
mysql_set_charset()
Synopsis
bool mysql_set_charset(char_set[, connection])
This function sets the default character set for the current connection to MySQL, or for a connection given with the function. For a list of acceptable character set names that may be given as an argument to this function, execute SHOW CHARACTER SET; from the mysql client. Here is an example:
...
mysql_set_charset('utf8', $connection);
...
Name
mysql_stat()
Synopsis
string mysql_stat([connection])
This function returns the status of the server for the current MySQL connection, but you also can use it to get the status for another connection. The function returns—as a space-separated list—the flush tables, open tables, queries, queries per second, threads, and uptime for the server. This function is available as of version 4.3 of PHP. Here is an example:
...
$connection = mysql_connect('localhost',
'jacinta', 'richardson');
$info = explode(' ', mysql_stat($connection));
print_r($info);
...
The explode() PHP function lists the elements of the space-separated values contained in the associative array generated by mysql_stat() along with their respective keys.
Name
mysql_tablename()
Synopsis
string mysql_tablename(results, index)
This function returns the table name for a particular table in the results set given by mysql_list_tables(). You can specify an index to retrieve a particular element of the results set. This function is deprecated, though. Use the mysql_query() function with the SHOW TABLES statement instead. Here is an example:
...
$tables = mysql_list_tables('workrequests');
$tbs = mysql_num_rows($tables);
for($index = 0; $index < $tbs; $index++) {
print mysql_tablename($tables, $index) . "\n";
}
...
Name
mysql_thread_id()
Synopsis
int mysql_thread_id([connection])
This function returns the thread identification number for the current MySQL connection. You may give an identifier for another connection. This function is available as of version 4.3 of PHP. Here is an example:
...
$connection = mysql_connect('127.0.0.1', 'russell', 'spenser');
$info = mysql_thread_id($connection);
print "Thread ID: $info \n";
...
Name
mysql_unbuffered_query()
Synopsis
resource mysql_unbuffered_query(sql_statement[, connection])
Use this function to execute an SQL statement given without buffering the results so that you can retrieve the data without having to wait for the results set to be completed. You may give an identifier as a second argument to interface with a different connection. The function returns false if the query is unsuccessful. For SQL statements that do not return a results set based on their nature (e.g., INSERT), the function returns true when successful. Use this function with care because an enormous results set could overwhelm the program’s allocated memory. Here is an example:
...
$sql_stmnt = "SELECT wrid, client_name, description
FROM workreq, clients
WHERE workreq.clientid = clients.clientid";
$results = mysql_unbuffered_query($sql_stmnt, $connection);
while($row = mysql_fetch_row($results)) {
print "WR-$row[0]: $row[1] - $row[2] \n";
}
...
There’s no difference in the syntax of mysql_unbuffered_query() and mysql_query(), nor in the handling of the results. The only differences in this function are the speed for large databases and the fact that functions such as mysql_num_row() and mysql_data_seek() cannot be used, because the results set is not buffered and therefore cannot be analyzed by these functions.