19.2. 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.
| mysql_affected_rows( ) | |
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.
| mysql_change_user( ) | |
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 the
mysql_connect( ) function.
| mysql_client_encoding( ) | |
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
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.
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:
Code View:
#!/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.
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.
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.
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 $index increments from 0 to the value of
$dbs.
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( ).
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 a DROP
DATABASE statement instead. Here is an example:
...
mysql_dropdb('old_db');
...
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.
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.
| mysql_escape_string( ) | |
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 use
mysql_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.
| mysql_fetch_array( ) | |
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).
| mysql_fetch_assoc( ) | |
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.
| mysql_fetch_field( ) | |
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)
...
| mysql_fetch_lengths( ) | |
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
| mysql_fetch_object( ) | |
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
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.
| mysql_field_flags( ) | |
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
)
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 with mysql_field_len( ).
Here are a few lines of the output of this script:
wrid - 9
wr_date - 10
clientid - 4
...
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.
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 using
mysql_fetch_field( ).
| mysql_field_table( ) | |
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
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 using
mysql_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
...
| mysql_free_result( ) | |
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.
| mysql_get_client_info( ) | |
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
| mysql_get_host_info( ) | |
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
| mysql_get_proto_info( ) | |
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
| mysql_get_server_info( ) | |
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
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
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
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";
}
...
| mysql_list_fields( ) | |
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
...
| mysql_list_processes( ) | |
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']";
}
...
| mysql_list_tables( ) | |
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_tables
variable.
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.
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";
...
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, and
MYSQL_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');
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.
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.
| mysql_real_escape_string( ) | |
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.
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]).
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);
...
| mysql_set_charset( ) | |
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);
...
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.
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";
}
...
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";
...
| mysql_unbuffered_query( ) | |
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.
No comments:
Post a Comment