| 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, or following code to close the connection and to end the script. For an example showing how you would write these opening and closing lines, see the tutorial in the previous section.
mysql_affected_rows([connection]) | |
This 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( ) 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. ... $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(user, password[, database, connection]) | |
Use this 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. The user information may be changed for a different MySQL connection by specifying it as the fourth argument. If the function is successful, true is returned; false is returned if it's unsuccessful. This function is no longer available as of Version 4 of PHP. Instead, a new connection should be established with a different user by using the mysql_connect( ) function: ... mysql_connect('localhost', 'hui', 'shorty'); ... mysql_change_user('russell','dyer'); ...
In this example, we needed to change the user in the script from hui to russell. If there had been a third argument, we would have changed to another database.
mysql_client_encoding([connection]) | |
This returns the name of the default character set for the current MySQL connection or, if connection is supplied, for that connection. ... $info = mysql_client_encoding( ); print "Encoding in Use: $info \n"; ...
Here are the results of this script on my computer: Encoding in Use: latin1
mysql_close([connection]) | |
This closes the current or last MySQL connection, or a given connection. The function returns true if it's successful; false if unsuccessful. This function will not close persistent connections started with mysql_pconnect( ). ... $connection = mysql_connect('localhost', 'russell', 'dyer'); 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 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( ). #!/usr/bin/php -q <? mysql_connect('localhost', 'russell', 'dyer'); 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 = 'hui'; $user2 = 'russell'; $connection1 = mysql_connect('localhost', $user1, 'shorty'); $connection2 = mysql_connect('localhost', $user2, 'dyer'); mysql_select_db('workrequests'); 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 hui 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 russell has the necessary privileges, so the function is completed successfully. The output from running this script on my server follows. Notice in the script that although a separate mysql_close( ) statement is necessary to close each connection, one mysql_select_db( ) is applied to both connections there's no ambiguity. Could not SELECT with Resource id #1 for hui. Number of Rows Found with Resource id #2 for russell: 528.
mysql_create_db(database[, connection]) | |
Use this 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 a CREATE DATABASE statement instead. ... 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.
mysql_data_seek(connection, row) | |
Use this in conjunction with the mysql_fetch_row( ) function to change the current row being fetched to the one specified in the second argument of this function. The connection identifier is given as the first argument. The function returns true if it's successful; it returns false if it's unsuccessful. ... $sql_stmnt = "SELECT wrid, clientid, description FROM workreq"; $results = mysql_query($sql_stmnt); $count = mysql_num_rows($results); 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 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 less 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.
mysql_db_name(databases, number) | |
This 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 the second argument. ... $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 determined 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.
mysql_db_query(database, sql_statement[, connection]) | |
Use this 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), true will be returned if the function is successful and false if it's unsuccessful. The mysql_query( ) function may be used instead if the statement is to be executed on the current database. ... $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( ).
mysql_drop_db(database[, connection]) | |
Use this to delete the database given from the MySQL server. A different connection identifier may be given as a second argument. The function returns true if it's successful; it returns false if it's unsuccessful. This function has been deprecated. Use the mysql_query( ) function with a DROP DATABASE statement instead. ... mysql_dropdb('old_db'); ...
mysql_errno([connection]) | |
This 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. ... $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. The result of this script follows: 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.
mysql_error([connection]) | |
This returns the error message for the last MySQL statement issued. The function returns nothing if there was no error. Another MySQL connection identifier may be given as an argument for the function. See mysql_errno( ) for an example of how mysql_error( ) may be used.
mysql_escape_string(string) | |
This 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. It's similar to mysql_real_escape_string( ). ... $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 cause the SQL statement to fail. It will fail because the related value in the SQL statement is surrounded by single quotes; an apostrophe would be mistaken for a single quote, which has special meaning in MySQL.
mysql_fetch_array(results[, type]) | |
This 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 is returned or only an associative array is returned, or both, one of the following may be given 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, false is returned, which typically triggers the end of the loop. ... $sql_stmnt = "SELECT wrid, clientid, description FROM workreq"; $results = mysql_db_query('workrequests', $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); 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(results) | |
This 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, false is returned, which will end a loop statement. This function is synonymous with mysql_fetch_array( ) with MYSQL_ASSOC as its second argument. ... $sql_stmnt = "SELECT wr_id, client_id, description FROM workreq"; $results = mysql_db_query('workrequests', $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 could not be used to get the work request number the wr_id key for the associative array stored in $row has to be used instead.
mysql_fetch_field(results[, offset]) | |
This 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 may not 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 datatype; blob, which returns 1 if it's a BLOB datatype; type, which returns the datatype; unsigned, which returns 1 if the column is unsigned; and zerofill, which returns 1 if it's a zero-fill column. ... $sql_stmnt = "SELECT * FROM workreq LIMIT 1"; $results = mysql_db_query('workrequests', $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 datatype, 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(results) | |
This returns an array containing the length of each field of a results set from a MySQL query. ... $sql_stmnt = "SELECT wr_id, description, instructions FROM workreq"; $results = mysql_db_query('workrequests', $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 length of the description field and the instructions field is pulled out of the $length array using the relative index number for each. Here are a few lines from the output of this script: ... 5753: description: 26, instructions: 254 5754: description: 25, instructions: 156 5755: description: 25, instructions: 170
mysql_fetch_object(result) | |
This 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. ... $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_db_query('workrequests', $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 values 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, the wr_count alias is used. Here are a few lines from the output of this script: ... Bracey Logistics 3 Neumeyer Consultants 2 Farber Investments 4
This 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. ... $sql_stmnt = "SELECT wr_id, client_name, description FROM workreq, clients WHERE workreq.clientid = clients.clientid"; $results = mysql_db_query('workrequests', $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( ), the number corresponding to each element must be known. 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 the previous script: WR-5755: Farber Investments - Can't connect to Internet.
mysql_field_flags(results, offset) | |
This returns the field flags for a field of a results set given. See mysql_fetch_field( ) for a description of the flags. Specify the desired field through the offset in the second argument. ... $sql_stmnt = "SELECT * FROM workreq LIMIT 1"; $results = mysql_db_query('workrequests', $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, the elements of the array are retrieved without having to know the number of elements, and they are stored in $flags. Next, the field name is displayed and the flags are printed out using print_r( ). Here is the output of the script for the first field: wrid Array ( [0] => not_null [1] => primary_key [2] => auto_increment )
mysql_field_len(results, index) | |
This returns the length from a field of the results set given. Specify the desired field, through the index in the second argument. ... $sql_stmnt = "SELECT * FROM workreq LIMIT 1"; $results = mysql_db_query('workrequests', $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 the number of fields in the results set is determined by mysql_num_fields( ). 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 ...
mysql_field_name(results, index) | |
This 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. ... $sql_stmnt = "SELECT * FROM workreq LIMIT 1"; $results = mysql_db_query('workrequests', $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 in this example selects one row from the table. Then the results of the query are examined by mysql_num_fields() to determine 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.
mysql_field_seek(results, index) | |
Use this 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. ... $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 that value, 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(results, index) | |
This 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. ... $sql_stmnt = "SELECT wrid, client_name, description FROM workreq, clients WHERE workreq.clientid = clients.clientid"; $results = mysql_db_query('workrequests', $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 in this example 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 the previous script: workreq.wrid clients.client_name workreq.description
mysql_field_type(results, index) | |
This returns the column datatype for a field from the results set given. To specify a particular field, an offset is given as the second argument. ... $sql_stmnt = "SELECT * FROM workreq LIMIT 1"; $results = mysql_db_query('workrequests', $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, the number of rows in the results set is determined using mysql_num_fields( ) 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 datatype 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(results) | |
Use this to free the memory containing the results set given. The function returns true if it's successful; it returns false if it's unsuccessful. ... 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.
This returns the library version of the MySQL client for the current connection. ... $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([connection]) | |
This returns information on the host for the current connection to MySQL. An identifier may be given to retrieve information on a host for a different connection. ... $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([connection]) | |
This returns the protocol version for the current connection to MySQL. An identifier may be given to retrieve the protocol version for a different connection. ... $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([connection]) | |
This returns the MySQL server version for the current connection to MySQL. An identifier may be given to retrieve the server version for a different connection. ... $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
This returns information on the last query for the current connection to MySQL. An identifier may be given to retrieve information on a query for a different connection. ... $sql_stmnt = "SELECT * FROM workreq"; $results = mysql_query($sql_stmnt); print mysql_info( ); ...
Here are the results of this script: String format: 528 rows in set
mysql_insert_id([connection]) | |
This 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, 0 is returned. ... $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
This 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. ... $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(database, table[, connection]) | |
This 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. ... $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([connection]) | |
This 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. An identifier may be given to retrieve information for a different connection. ... $processes = mysql_list_processes($connection); while ($row = mysql_fetch_array($processes)){ print "$row['Id'], $row['Host'], $row['db'], $row['Command']"; } ...
mysql_list_tables(database[, connection]) | |
This returns a results set containing a list of tables for database. An identifier may be given as a second argument to the function 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. ... $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 $num_tables variable.
mysql_num_fields(results) | |
This returns the number from fields of the results set given. ... $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, the number of fields needs to be determined. The mysql_num_fields( ) function is handy for figuring out this bit of information.
This 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( ). ... $sql_stmnt = "SELECT * FROM workreq"; $results = mysql_query($sql_stmnt); $count = mysql_num_rows($results); print "Number of Rows Found: $count \n"; ...
mysql_pconnect(server[:port|socket], user, password[, flags]) | |
Use this 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 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 of a previous one, the existing connection is used instead of creating a new connection. As an optional fourth 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 it's successful; it returns false if it's unsuccessful. mysql_pconnect('localhost', 'russell', 'dyer');
Use this to determine if 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, true is returned. If the connection is not open and cannot be reestablished, false is returned. An identifier may be given to ping a different connection. ... $ping = mysql_ping($connection); print "Info: $ping \n"; ...
This function is available as of Version 4.3 of PHP.
mysql_query(sql_statement[, connection]) | |
Use this to execute an SQL statement given. An identifier may be given 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), true is returned when the function is successful. Otherwise, a reference to a results set is returned. ... $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( ) | |
mysql_real_escape_string(string[, link]) | |
This 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 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.
mysql_result(results, row[, field|offset]) | |
This 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, either a field name (i.e., the column or alias name) or an offset may be given 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. ... $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]).
mysql_select_db(database[, connection]) | |
This 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; it returns false if it's unsuccessful. ... $connection = mysql_connect('localhost','tina','muller'); mysql_select_db('workrequests', $connection); ...
This 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 starting with Version 4.3 of PHP. ... $connection = mysql_connect('localhost', 'jacinta', 'richardson'); $info = explode(' ', mysql_stat($connection)); print_r($info); ...
Using the explode( ) PHP function, the elements of the space-separated values contained in the associative array generated by mysql_stat( ) are listed along with their respective keys.
mysql_tablename(results, index) | |
This returns the table name for a particular table in the results set given by mysql_list_tables( ). An index may be specified to retrieve a particular element of the results set. ... $tables = mysql_list_tables('workrequests'); $tbs = mysql_num_rows($tables); for($index = 0; $index < $tbs; $index++) { print mysql_tablename($tables, $index) . "\n"; } ...
mysql_thread_id([connection]) | |
This returns the thread identification number for the current MySQL connection. An identifier for another connection may be given. This function is available starting with Version 4.3 of PHP. ... $connection = mysql_connect('127.0.0.1', 'russell', 'spenser'); $info = mysql_thread_id($connection); print "Thread ID: $info \n"; ...
| mysql_unbuffered_query( ) | |
mysql_unbuffered_query(sql_statement[, connection]) | |
Use this 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. An identifier may be given as a second argument to the function to interface with a different connection. The function returns false if the query is unsuccessful. For SQL statements that would not return a results set based on their nature (e.g., INSERT), true is returned when the function is successful. This function should be used with care, because an enormous results set could overwhelm the program's allocated memory. ... $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 for 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. | 