PHP MySQL Functions

The PHP functions are closely related to the C API functions. The functions listed here are the native PHP functions. Also, a number of libraries add a layer of abstraction to interfacing with MySQL in PHP, the most important of which are ADODB, PEAR, Metabase, and the older PHPLib.

mysql_affected_rows

int mysql_affected_rows([resource mysql_connection])

Returns the number of rows affected by the last statement that has made a change to the data (INSERT, UPDATE, DELETE, LOAD DATA, REPLACE), or –1 if the query failed. Remember that REPLACE INTO will affect two rows for each affected row in the original table (one DELETE and one INSERT). If the database connection is not specified, the most recent opened open connection is used.

If you're using transactions, call mysql_affected_rows() before calling COMMIT.

To return the number of rows returned by a SELECT statement, use mysql_num_rows().

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password); // update an unknown number of fields in the database table mysql_query("UPDATE $table SET field1=2 WHERE field1=3"); // store the number of rows updated $num_rows_updated =  mysql_affected_rows();

mysql_change_user

boolean mysql_change_user(string username, string password  [, string database [, resource mysql_connection]])

Changes the current MySQL user (the one that logged in) to another user (specifying that user's username and password). You can also change the database at the same time or specify a new connection; otherwise the current connection and database will be used. Returns true if successful and false if not, in which case the existing user and details are maintained.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);    $change_succeeded = mysql_change_user($new_user, $new_password, $database,    $connect); 

mysql_client_encoding

int mysql_client_encoding ([resource mysql_connection])

Returns the default character set (for example latin1) for the specified connection or the most recently opened open connection if none is specified. For example:

 // open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  $charset = mysql_client_encoding($connect); print "The current character set is $charset"; 

mysql_close

boolean mysql_close([resource mysql_connection])

Closes the specified connection or the most recent opened open connection. Does not close persistent connections. For example:

// open a connection to the database $connect = mysql_connect($hostname, $username, $password); // ... do some processing mysql_close($connect);

mysql_connect

mysql_connection mysql_connect([string hostname [, string username  [, string password [, boolean new_connection [, int client_flags]]]]])

Establishes a connection to a MySQL server (specified by hostname, username, and password, if required) and returns a link identifier to be used by other functions. If a second, identical call is made later in the code, the same link identifier is returned, unless the new_ connection parameter is set to true.

The hostname can also take a port (the port follows a colon after the host name).

The final parameter can be one or more of the following flags, which determine elements of MySQL's behavior when connected:

mysql_client_compress  Uses compression protocol.

mysql_client_ignore_space  Allows space after function names.

mysql_client_interactive  Waits for the value of the interactive_timeout instead of the wait_timeout mysqld variable before closing an inactive connection.

mysql_client_ssl  Uses the SSL protocol.

For example:

// set connection settings (should usually be done outside the script) $hostname = "localhost:3306"; $username = "guru2b"; $password = "g00r002b"; // open a connection to the database $connect = mysql_connect($hostname, $username, $password, MYSQL_CLIENT_COMPRESS); 

mysql_create_db

boolean mysql_create_db (string database [, resource mysql_connection])

Creates a new database on the server using the specified connection or the most recently opened open connection if none is specified. Returns true if it succeeded and false if not.

The newly created database does not become the active database. You need to use the mysql_select_db() function to do that.

This function replaces the deprecated mysql_createdb() function, which still works.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  if (mysql_create_db("new_db", $connect)) {   print "Database new_db successfully created"; } else {   print "Database new_db was not created"; }

mysql_data_seek

boolean mysql_data_seek (resource query_result, int row)

Moves the internal row pointer (0 is the first row) associated with the query result to a new position. The next row that is retrieved (from mysql_fetch_row() or mysql_fetch_array(), for example) will be the specified row.

Returns true if the move succeeded and false if not (usually because the query result does not have any or as many associated rows).

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);    // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // number of rows returned $x = mysql_num_rows($result); // if the tenth row exists, go straight to it if ($x >= 10) {   mysql_data_seek($result, 9); } // return the data from the 10th row $row = mysql_fetch_array($result); print "Field1: " . $row["field1"] . "<br>\n";  print "Field2: " . $row["field2"];  

mysql_db_name

string mysql_db_name (resource query_result, int row[, mixed unused])

Returns the name of a database. The query result would have been returned from an earlier call to the mysql_list_dbs() function. The row specifies which element of the query result set (which starts at 0) to return.

The mysql_num_rows() function returns the number of database returned from mysql_list_dbs().

For example:

 // open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // return the list of databases on the connection $result = mysql_list_dbs($connect); // loop through the results, returning the database names one by one for ($i=0; $i < mysql_num_rows($result); $i++) {      print mysql_db_name($result, $i) . "<br>\n"; }

mysql_db_query

query_result mysql_db_query ( string database, string query  [, resource mysql_connection])

Returns a query result resource if the query is processed successfully and false if the query fails. The query is sent to a specified database using the specified connection (or the most recently opened open connection if none is specified).

This function has been deprecated, so instead use mysql_select_db() and mysql_query().

mysql_drop_db

boolean mysql_drop_db(string database [, resource mysql_connection]) 

Drops the specified database for the specified connection or the most recently opened open connection if none is specified. Returns true if successful and false if the database could not be dropped.

This function and the even older mysql_dropdb() function are deprecated. You should use the mysql_query() function to drop a database instead.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // drop the old_db database if (mysql_drop_db("old_db", $connect)) {   print "Database old_db is gone"; else {   print "Database old_db could not be dropped"; }

mysql_errno

int mysql_errno([resource connection])

Returns the error number of the most recently performed MySQL function or zero if there was no error. Uses the specified connection (or the most recently opened open connection if none is specified).

This function will return zero after any successful MySQL-related function has been executed, except for mysql_error() and mysql_errno(), which leave the value unchanged.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // attempt to use a database that you've just dropped mysql_select_db("old_db", $connect); // Displays the error code - 1049 if (mysql_errno()) {   print "MySQL has thrown the following error: ".mysql_errno(); }

mysql_error

string mysql_error([resource mysql_connection])

Returns the text of the error message of the most recently performed MySQL function or an empty string ('') if there was no error. Uses the specified connection (or the most recently opened open connection if none is specified).

This function will return an empty string after any successful MySQL-related function has been executed, except for mysql_error() and mysql_errno(), which leave the value unchanged.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password); 

// attempt to use a database that you've just dropped mysql_select_db("old_db", $connect); // Displays the error text - Unknown database 'old_db' if (mysql_errno()) {   print "MySQL has thrown the following error: ".mysql_error(); }

mysql_escape_string

string mysql_escape_string (string stringname)

Returns a string with all characters that could break the query escaped (with a backslash placed before them). The characters that are escaped include null (\x00), new line (\n), carriage return (\r), backslash (\), single quote ('), double quote ("), and Ctrl+Z (\x1A).

Does not escape percentage (%) and underscore (_) characters.

This makes a query safe to use. Anytime user input is used in a query, this function should be used to make the query safe. You can also use the slightly less complete addslashes() function.

For example:

// orginal unsafe string $field_value = "Isn't it true that the case may be"; // escape the special characters $field_value = mysql_escape_string($field_value); // now it's safe and displays: Isn\'t it true that the case may be print "$field_value";

mysql_fetch_array

array mysql_fetch_array (resource query_result [, int array_type])

Returns an array of strings based upon a row from a query result returned from a function such as mysql_query(), and returns false if it fails or there are no more rows available. The row returned is based upon the position of the internal row pointer, which is then incremented by one. (The row pointer starts at 0 immediately after a query is run.)

The second parameter specifies how the data is to be returned. If the array type is set to MYSQL_ASSOC, data is returned as an associative array (the same if you'd used the mysql_fetch_ assoc() function). If the array type is set to MYSQL_NUM, the data is returned as a numeric array (the same as if you'd used the mysql_fetch_row() function). The third option, MYSQL_BOTH, is the default used if no option is specified, and it allows you to access the data as an associative or numeric array.

The associative array takes as a key the names of the fields only (dropping any table name prefix). If there are duplicate field names, you need to use an alias; otherwise the last-mentioned value will overwrite the earlier one.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // return the data in both associative and numeric arrays (default) // loop through the rows, printing the data while ($row = mysql_fetch_array($result)) {   print "Field1: ".$row["field1"]."<br>\n";   print "Field2: ".$row["field2"]."<br>\n"; }

mysql_fetch_assoc

array mysql_fetch_assoc (resource query_result)

Returns an array of strings based upon a row from a query result returned from a function such as mysql_query(), and returns false if it fails or there are no more rows available. The row returned is based upon the position of the internal row pointer, which is then incremented by one. (The row pointer starts at 0 immediately after a query is run.)

The data is returned as an associative array, which takes as a key the names of the fields only (dropping any table name prefix). If there are duplicate field names, you need to use an alias; otherwise the last-mentioned value will overwrite the earlier one. This function is the same as using mysql_fetch_array() with the MYSQL_ASSOC parameter.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // return the data in associative array format and loop through // the result, printing the row values while ($row = mysql_fetch_assoc($result)) {   print "Field1: ".$row["field1"]."<br>\n";   print "Field2: ".$row["field2"]."<br>\n"; } 

mysql_fetch_field

object mysql_fetch_field(resource query_result [, int offset ])

Returns an object containing information about a field based upon a row from a query result returned from a function such as mysql_query(). If the offset is not specified, the next unretrieved field is returned (so you can call this function multiple times to get information about all the fields); otherwise it will be the one determined by the offset (0 for the first field).

The properties of the object are as follows:

name  Field name

table  Table name to which the field belongs

max_length  Maximum length of the field

not_null  1 if the field is cannot contain nulls

primary_key  1 if the field is a primary key

unique_key  1 if the field is a unique key

multiple_key  1 if the field is a nonunique key

numeric  1 if the field is numeric

blob  1 if the field is a BLOB

type  The type of the field

unsigned  1 if the field is unsigned

zerofill  1 if the field is zero-filled

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // return a list of all fields in database1.table1 $result = mysql_list_fields("database1", "table1"); // loop through the fields and display the field name, type // and maximum length while ($row = mysql_fetch_field($result)) {   $max_length = $row->max_length;    $name = $row->name;   $type = $row->type;   print "Name:$name <br>\n";   print "Type:$type <br>\n";   print "Maximum Length:$max_length <br><br>\n\n"; } 

mysql_fetch_lengths

array mysql_fetch_lengths(resource query_result)

Returns an array of the lengths of each field in the last row fetched from a query result (the length of that result, not the maximum length), and returns false if it wasn't successful. Use the mysql_field_len() function to return maximum length for a field.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // return the data in associative array format and loop through // the result, retrieving the length of the fields, and printing // the row values and lengths while ($row = mysql_fetch_assoc($result)) {   $lengths = mysql_fetch_lengths($result);   print "Field1: ".$row["field1"]."Length: ".$lengths[0]."<br>\n";   print "Field2: ".$row["field2"]."Length: ".$lengths[1]."<br>\n"; }

mysql_fetch_object

object mysql_fetch_object(resource query_result)

Returns an object with properties based upon a row from a query result returned from a function such as mysql_query(). The row returned is based upon the position of the internal row pointer, which is then incremented by one. (The row pointer starts at 0 immediately after a query is run.)

Each property of the object is based upon a name (or alias) of the field from the query.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // loop through the rows returning each as an object // and display the fields while ($row = mysql_fetch_object($result)) {   print "Field1: ".$row->field1."<br>\n";   print "Field2: ".$row->field2."<br>\n"; } 

mysql_fetch_row

array mysql_fetch_row(resource query_result)

Returns an array of strings based upon a row from a query result returned from a function such as mysql_query(), or returns false if it fails or there are no more rows available. The row returned is based upon the position of the internal row pointer, which is then incremented by one. (The row pointer starts at 0 immediately after a query is run.)

The data is returned as a numeric array (the same as if you had used the mysql_fetch_array() function with the MYSQL_NUM parameter).

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // loop through the rows returning each as a numeric array // and display the fields while ($row = mysql_fetch_row($result)) {   print "Field1: ".$row[0]."<br>\n";   print "Field2: ".$row[1]."<br>\n"; }

mysql_field_flags

string mysql_field_flags(resource query_result, int offset)

Returns a string containing flags of the specified field based upon a row from a query result returned from a function such as mysql_query(). The offset determines which field is examined (0 for the first field).

The flags include not_null, primary_key, unique_key, multiple_key, blob, unsigned, zerofill, binary, enum, auto_increment, and timestamp.

The old mysql_fieldflags() function does the same, but it's deprecated.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // Display the properties for fields 1 and 2 print "Field1 flags: ".mysql_field_flags($result, 0)."<br>\n"; print "Field1 flags: ".mysql_field_flags($result, 1)."<br>\n"; 

mysql_field_len

int mysql_field_len (resource query_result, int offset)

Returns the maximum length (determined by the database structure) of the specified field based upon a row from a query result returned from a function such as mysql_query(). The offset (starting at 0) determines the field.

The old mysql_fieldlen() function does the same, but it's deprecated.

Use the mysql_fetch_lengths() function to determine the specific length of a returned field.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // Display the properties for fields 1 and 2 print "Field1 maximum length: ". mysql_field_len($result, 0). "<br>\n"; print "Field1 maximum length: ". mysql_field_len($result, 1). "<br>\n";

mysql_field_name

string mysql_field_name(resource query_result, int offset)

Returns the name of the specified field based upon a row from a query result returned from a function such as mysql_query(). The offset (starting at 0) determines the field.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT * FROM table1"; $result = mysql_query($sql, $connect); // loop through the fields and display the name for($i=0; $i < mysql_num_fields($result); $i++) {   print "Field name: ".mysql_field_name($result, $i). "<br>\n"; } 

mysql_field_seek

boolean mysql_field_seek(resource query_result, int offset)

Moves the internal pointer to a new field of the query result, based on the offset (starting at 0 with the first field). The next call to the mysql_fetch_field() function will start at this offset. This is not that useful because you can move the pointer directly with the mysql_ fetch_field() function.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT * FROM table1"; $result = mysql_query($sql, $connect); // jump to the 2nd field mysql_field_seek($result, 1); $field = mysql_fetch_field($result); print "The name of the 2nd field is: " . $field->name;

mysql_field_table

string mysql_field_table(resource query_result, int offset)

Returns the name of the table the field in a query result determined by the offset (starting at 0) refers to, or returns false if there's an error. The deprecated mysql_fieldtable() function is identical.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1,table2 WHERE field1=field2"; $result = mysql_query($sql, $connect); // Get the name of the table for field1 (offset 0) echo "field 1 belongs to the table: ".mysql_field_table($result, 0); 

mysql_field_type

string mysql_field_type(resource query_result, int offset)

Returns the type of a field in a query result determined by the offset (starting at 0), or returns false if there's an error. Examples of field type include int, real, string, and blob.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1,table2 WHERE field1=field2"; $result = mysql_query($sql, $connect); for($i=0;$i<mysql_num_fields($result);$i++) {   echo "Field $i is of type: ".mysql_field_type($result, $i) . "<br>\n"; }

mysql_free_result

boolean mysql_free_result(resource query_result)

Frees all memory used by the resource query_result, making it available for use again. Returns true on success and false on failure. Memory is automatically freed at the end of a script even without calling this function. The deprecated mysql_freeresult() function is identical.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // loop through the rows returning each as a numeric array // and display the fields while ($row = mysql_fetch_row($result)) {   print "Field1: ".$row[0]."<br>\n";   print "Field2: ".$row[1]."<br>\n"; } // free the resources associated with the query mysql_free_result($result); 

mysql_get_client_info

string mysql_get_client_info()

Returns a string containing the MySQL client library version (4.0.2, for example).

For example:

// displays - Client library version is: 4.0.2 (for example) print "Client library version is: ".mysql_get_client_info();

mysql_get_host_info

string mysql_get_host_info([resource mysql_connection])

Returns a string containing the connection information (for example, "Localhost via UNIX socket"). The information is from the specified connection (or the most recently opened open connection if none is specified).

For example:

// displays - Type of connection: Localhost via UNIX socket // (for example) print "Type of connection: ".mysql_get_host_info();

mysql_get_proto_info

int mysql_get_proto_info([resource mysql_connection])

Returns an integer containing the protocol version (for example, 10) used by the connection. The information is from the specified connection (or the most recently opened open connection if none is specified).

For example:

// displays - Protocol version: 10 (for example) print "Protocol version: ".mysql_get_proto_info();

mysql_get_server_info

string mysql_get_server_info([resource mysql_connection])

Returns a string containing the MySQL server version (for example, 4.0.3). The information is from the specified connection (or the most recently opened open connection if none is specified).

For example:

// displays - Server version: 4.0.3-beta-log (for example) print "Server version: ".mysql_get_server_info(); 

mysql_info

string mysql_info ( [resource mysql_connection])

Returns a string containing detailed information about the most recent query. Detailed information includes records, rows matched, changes, and warnings. The information is from the specified connection (or the most recently opened open connection if none is specified).

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "UPDATE table1 set field1 = 2 WHERE field2=3"; $result = mysql_query($sql, $connect); // displays: // Query info: String format: Rows matched: 19 Changed: 19 Warnings: 0 //(for example) print "Query info: ".mysql_info();

mysql_insert_id

int mysql_insert_id([resource mysql_connection])

Returns an integer containing the most recent AUTO_INCREMENT value for that connection, or returns false if it fails (there have been no AUTO_INCREMENT values set for the connection). The information is from the specified connection (or the most recently opened open connection if none is specified).

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "INSERT INTO table1(field1, field2) VALUES(3,4)"; $result = mysql_query($sql, $connect); // Displays: AUTO_INCREMENT value: 10 (for example) print "AUTO_INCREMENT value: ".mysql_insert_id();

mysql_list_dbs

query_result mysql_list_dbs([resource mysql_connection]) 

Returns a resource pointing to a list of databases available on the connection, or returns false on failure. The information is from the specified connection (or the most recently opened open connection if none is specified). The result can be processed by a function such as mysql_db_ name() or mysql_result().

The deprecated mysql_listdbs() function is identical.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // return the list of databases on the connection $result = mysql_list_dbs($connect); // loop through the results, returning the database names one by one for ($i=0; $i < mysql_num_rows($result); $i++) {      print mysql_db_name($result, $i) . "<br>\n"; }

mysql_list_fields

query_result mysql_list_fields(string database, string table  [, resource mysql_connection])

Returns a resource pointing to a list of fields from a given database and table or false on failure. The information is from the specified connection (or the most recently opened open connection if none is specified).

The deprecated mysql_listfields() function is identical.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // return a list of all fields in database1.table1 $result = mysql_list_fields("database1", "table1"); // loop through the fields and display the field name, type // and maximum length while ($row = mysql_fetch_field($result)) {   $max_length = $row->max_length;    $name = $row->name;   $type = $row->type;   print "Name:$name <br>\n";   print "Type:$type <br>\n";   print "Maximum Length:$max_length <br><br>\n\n"; }

mysql_list_processes

query_result mysql_list_processes ([resource mysql_connection])

Returns a resource containing a list of the current MySQL processes or false on failure. You can then use a function such as mysql_fetch_assoc() to return an array containing the elements: Id, Host, db, Command, and Time. The information is from the specified connection (or the most recently opened open connection if none is specified).

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // return all the processes $result = mysql_list_processes($connect); // loop through the rows displaying the various process elements while ($row = mysql_fetch_assoc($result)){   print $row["Id"];   print $row["Host"];   print $row["db"];   print $row["Command"];   print $row["Time"];   print "<br>\n" }

mysql_list_tables

query_result mysql_list_tables(string database[, resource mysql_connection])

Returns a resource pointing to a list of tables from a given database, or returns false on failure. The information is from the specified connection (or the most recently opened open connection if none is specified).

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // returns the list of tables $result = mysql_list_tables("database1"); // loops through the rows of tables, and displays the names for($i=0; i < mysql_num_rows($result); $i++) {   print "Table name: ".mysql_tablename($result, $i)."<br>\n"; }

mysql_num_fields

int mysql_num_fields(resource query_result)

Returns an integer containing the number of fields in a query result or NULL on error.

The deprecated mysql_numfields() function is identical.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // return a list of all fields in database1.table1 $result = mysql_list_fields("database1", "table1"); // Displays: Num fields in database1: 6 (for example) print "Num fields in database1: ".mysql_num_fields($result); 

mysql_num_rows

int mysql_num_rows(resource query_result)

Returns an integer containing the number of rows in a query result, or returns NULL on error. This will not work if the query result was obtained using the mysql_unbuffered_query() function. You should use mysql_affected_rows() to return the number of rows of data changed by a query (after an INSERT or UPDATE, for example).

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // return the list of databases on the connection $result = mysql_list_dbs($connect); // loop through the results, returning the database names one by one for ($i=0; $i < mysql_num_rows($result); $i++) {      print mysql_db_name($result, $i) . "<br>\n"; }

mysql_pconnect

mysql_connection mysql_pconnect([string hostname  [, string username [, string password [, int client_flags]]]])

Establishes a persistent connection (one that can be reused) to a MySQL server (specified by hostname, username, and password, if required) and returns a link identifier to be used by other functions. If one already exists, it will be reused. The final parameter can be one or more of the following flags, which determine elements of MySQL's behavior when connected:

mysql_client_compress  Uses compression protocol

mysql_client_ignore_space  Allows space after function names

mysql_client_interactive  Waits for the value of the interactive_timeout instead of the wait_timeout mysqld variable before closing an inactive connection

mysql_client_ssl  Uses SSL protocol

MySQL closes persistent connections after the wait_timeout seconds (a mysqld variable) or after the process that spawned the connection closes. For example, your web server process can reuse the same connection for several scripts, but the connection will close once the web server process closes.

For example:

// set connection settings (should usually be done outside the script) $hostname = "localhost:3306"; $username = "guru2b"; $password = "g00r002b"; // open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password); 

mysql_ping

boolean mysql_ping ([resource mysql_connection])

Returns true if the MySQL server is up and false if not. The ping is attempted using the specified connection (or the most recently opened open connection if none is specified). If the ping fails, the script will try to reconnect with the same parameters.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // time passes... if (mysql_ping()) {   print "Still connected"; } else {   print "Connection lost"; }

mysql_query

query_result mysql_query(string query[, resource mysql_connection  [, int result_mode]])

Returns a query result (if the query was one that produces a result, such as SELECT or DESCRIBE), returns true if the query did not produce a result but succeeded (such as DELETE or UPDATE) and returns false if the query failed. The query is sent to a specified database using the specified connection (or the most recently opened open connection if none is specified).

The optional result_mode parameter can be one of MYSQL_USE_RESULT, which causes the result to be unbuffered, just like with mysql_unbuffered_query() or MYSQL_STORE_RESULT (the default).

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_query($sql, $connect); // return the data in associative array format and loop through // the result, printing the row values while ($row = mysql_fetch_assoc($result)) {   print "Field1: ".$row["field1"]."<br>\n";   print "Field2: ".$row["field2"]."<br>\n"; } 

mysql_real_escape_string

string mysql_real_escape_string (string stringname [, resource mysql_connection])

Returns a string with all characters that could break the query escaped (a backslash placed before them. The characters that are escaped include null (\x00), new line (\n), carriage return (\r), backslash (\), single quote ('), double quote ("), and Ctrl+Z (\x1A). It does not escape percentage (%) and underscore (_) characters.

This makes a query safe to use. Differs to mysql_escape_string() in that this function takes into account the current character set.

For example:

// orginal unsafe string $field_value = "Isn't it true that the case may be"; // escape the special characters $field_value = mysql_real_escape_string($field_value); // now it's safe and displays: Isn\'t it true that the case may be print "$field_value";

mysql_result

mixed mysql_result(resource query_result, int row [, mixed field_specifier])

Returns the contents of a single field from a query result. The field_specifier can be either an offset (starting at 0) or the field's name, with or without a table specifier (in other words, tablename.fieldname or just fieldname) if this was supplied in the query. If the field specifier is not supplied, the first field will be returned.

This function is markedly slower than the functions that return the entire row, such as mysql_fetch_row() and mysql_fetch_array(), so use one of those instead. Also, don't mix this function with functions that return the entire row.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // return the average of field1 $sql = "SELECT AVG(field2) FROM table1"; $result = mysql_query($sql, $connect); // display the average value of this field print "Field2 average: ".mysql_result($result, 0); 

mysql_select_db

boolean mysql_select_db(string database [, resource mysql_connection])

Changes the current database to the specified database. Uses the specified connection (or the most recently opened open connection if none is specified). If no connection is open, it will attempt to call mysql_connect() with no parameters to connect. Returns true if successful, and returns false if not.

The deprecated mysql_selectdb() function is identical.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect);

mysql_stat

string mysql_stat ([resource mysql_connection])

Returns a string containing the server status. This contains uptime, threads, questions, slow queries, opens, flush tables, open tables, and queries per second average. Uses the specified connection (or the most recently opened open connection if none is specified).

For example:

// displays (for example): // Uptime: 109  Threads: 2  Questions: 199  Slow queries: 1  Opens: 4 // Flush tables: 1  Open tables: 2  Queries per second avg: 1.826 print "Server Status: ".mysql_stat();

mysql_tablename

string mysql_tablename(resource query_result, int row)

Returns the table name from a query result returned by the mysql_list_tables() function based upon the row (starting at 0), or returns false if there's an error. You can return the number of rows in the query result with mysql_num_rows(). This function is actually an alias to mysql_ result(), but it's not good programming practice to use it in the same way because its name is specific to tables, and using it in another way is confusing.

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // returns the list of tables $result = mysql_list_tables("database1"); // loops through the rows of tables, and displays the names for($i=0; $i < mysql_num_rows($result); $i++) {   print "Table name: ".mysql_tablename($result, $i)."<br>\n"; } 

mysql_thread_id

int mysql_thread_id ([resource mysql_connection])

Returns an integer containing the current thread ID.

For example:

 // displays - Thread id: 2394 (for example) print "Thread id: ".mysql_thread_id();

mysql_unbuffered_query

query_result mysql_unbuffered_query(string query  [, resource mysql_connection [, int result_mode]])

Returns an unbuffered query result (if the query was one that produces a result, such as SELECT or DESCRIBE), returns true if the query did not produce a result but succeeded (such as DELETE or UPDATE), and returns false if the query failed. The query is sent using the specified connection (or the most recently opened open connection if none is specified).

The difference between this and mysql_query() is that, because the result is unbuffered, it uses less memory and you can start working with the results as soon as the first row has been retrieved. The downside is you cannot use mysql_num_rows(). It's mainly used on large, slow queries.

The optional result_mode parameter can be one of MYSQL_USE_RESULT (the default) or MYSQL_STORE_RESULT, which causes the result to be buffered, just like with mysql_query().

For example:

// open a persistent connection to the database $connect = mysql_pconnect($hostname, $username, $password);  // select the database mysql_select_db("database1", $connect); // set and run the query $sql = "SELECT field1,field2 FROM table1"; $result = mysql_unbuffered_query($sql, $connect); // return the data in both associative and numeric arrays (default) // loop through the rows, printing the data while ($row = mysql_fetch_array($result)) {   print "Field1: ".$row["field1"]."<br>\n";   print "Field2: ".$row["field2"]."<br>\n"; }



Mastering MySQL 4
Mastering MySQL 4
ISBN: 0782141625
EAN: 2147483647
Year: 2003
Pages: 230
Authors: Ian Gilfillan

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net