18.2 Functions

only for RuBoard - do not distribute or recompile

18.2 Functions

mysql_affected_rows

 int mysql_affected_rows([cresource   connection   ]) 

Returns the number of rows affected by the most recent DELETE , INSERT , or UPDATE statement. If the most recent query failed, -1 is returned. There is an exception to this rule: if all rows are deleted from a table, the function returns zero. The function takes an optional connection resource handle as a parameter. If no parameter is passed, the most recently opened connection that is still open is assumed. The function cannot be used with SELECT statements, where mysql_num_rows( ) should be used instead.

The function may report that zero rows were affected. For example, the query:

 DELETE FROM customer WHERE cust_id = 3 

always executes, but mysql_affected_rows( ) returns zero if there is no matching row. Similarly, if an UPDATE doesn't change the database, the function returns zero.

Example

 <?php   $query = "INSERT INTO people              VALUES(\"selina\", \"" . crypt("sarah", "se") . "\")";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      echo "Inserted " . mysql_affected_rows($connection) . " row(s)";    ?> 
mysql_change_user

 int mysql_change_user( string   username   , string   password   [, string   database   [, cresource   connection   ]]) 

Changes the logged-in MySQL user to another username using that user's password . Optional database and connection resource handles may be specified. If the database and connection resource handles are omitted, the current database and most recently opened connection that is still open are assumed.

The function returns true on success and false on failure. If false is returned, the user that was authenticated prior to the function call remains current.

The function mysql_change_user( ) is available only through MySQL DBMS Version 3.23.3 and later. In addition, the function is missing from PHP Version 4 (up to and including the current PHP 4.1.2), with calls to the function reporting it as an undefined function. This is likely to be fixed in future PHP versions.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      // The database will still be "wedding" on the current connection   if (mysql_change_user("richo", "twelve") == true)      echo "Changed user to richo";   else      echo "Change to user richo failed!"; ?> 
mysql_close

 boolean mysql_close([cresource   connection   ]) 

Closes the most recently opened MySQL DBMS connection. The function takes an optional connection resource handle as a parameter. If no parameter is passed, the most-recently opened connection that is still open is assumed.

The function returns true on success and false on failure.

This function is rarely used, as nonpersistent connections opened with mysql_connect( ) are closed when a script ends. Connections opened with mysql_pconnect( ) cannot be closed. Therefore, the only practical use of this function is to close a nonpersistent connection in a script where resource use must be minimized.

Example

 <?php   $query = "SELECT * FROM presents";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      while ($row = mysql_fetch_array($result))      echo $row["present"] . "\n";      mysql_close($connection); ?> 
mysql_connect

 cresource mysql_connect([string   hostname   [, string   username   [, string   password   [, boolean   new_connection   ]]]]) 

Used to establish a connection to the MySQL DBMS. The function returns a connection resource handle on success that can be used to access databases through subsequent function calls. The function returns false on failure.

The function has four optional parameters. The first is the hostname of the DBMS that can include an optional port number. The hostname parameter is usually set to localhost when the MySQL DBMS and the web server are running on the same machine. A default port for MySQL of 3306 is assumed if the port is omitted.

The second and third parametersa username and password are MySQL DBMS username and password credentials. These are the same username and password used to access the DBMS though the command-line monitor mysql .

If a second call is made to the function in the same script with the same first three parameters, a new connection is not opened. Instead, the function just returns the connection resource handle of the existing open connection. In the upcoming PHP 4.2 release, you should be able to override this behavior by supplying a fourth new_connection parameter. When it is set to true , a new connection will always be opened.

If all parameters are omitted, the hostname and port default to localhost:3306 , the username defaults to the name of the user that owns the MySQL DBMS server process, and the password defaults to an empty string. Because these parameters are unlikely to be valid credentials for accessing the MySQL DBMS, the first three parameters to mysql_connect( ) should be supplied in practice.

Example

 <?php      // This is a typical function call   // Local machine, user "fred" and password "shhh"   // On a Unix machine, this defaults to Unix socket   $connection1 = mysql_connect("localhost", "fred", "shhh");      // Local machine, user "fred" and password "shhh"   // Adding the port forces a TCP/IP connection   // on a Unix machine   $connection2 = mysql_connect("localhost:3306", "fred", "shhh");      // Remote machine "blah.webdatabasebook.com" on port 4000   $connection3 = mysql_connect("blah.webdatabasebook.com:4000", "fred", "shhh");    ?> 
mysql_create_db

 boolean mysql_create_db (string   database   [, cresource   connection   ]) 

Creates a new database with the name supplied as the database parameter. The function takes an optional connection resource handle as the second parameter. If no second parameter is passed, the most recently opened connection that is still open is assumed. To use the database after creating it, you must call mysql_select_db( ) .

Returns true on success and false on failure.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      if (mysql_create_db("temp", $connection))      echo "Created database 'temp'";   else      echo "Create database 'temp' failed!"; ?> 
mysql_data_seek

 boolean mysql_data_seek (qresource   query   , int   row   ) 

Moves the internal pointer related to a query to a specific row , where zero refers to the first row in a result set. After calling this function, the next row that is retrieved through mysql_fetch_array( ) , mysql_fetch_assoc( ) , mysql_fetch_object( ) , or mysql_fetch_row( ) will be the row specified.

The function returns true on success and false on failure. A common source of failure is that there are no rows in the result set associated with the query resource handle. A prior call to mysql_num_rows( ) can be used to determine if results were returned from the query.

Example

 <?php   $query = "SELECT * FROM presents";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      if (!mysql_data_seek($result, 7))      echo "Could not seek to the eighth row!";       $row = mysql_fetch_array($result);       echo "Eighth row: " . $row["present"];        ?> 
mysql_db_name

 string mysql_db_name (qresource   query   , int   row   [, mixed unused]) 

Returns the name of a database associated with a query resource handle returned from a prior call to mysql_list_dbs( ) . The second argument is a row index into the query result set. The first database in the result set is numbered zero. The number of database names in the result set can be determined using mysql_num_rows( ) .

This function returns false on error. A common source of error is supplying a row number that is greater than the number of databases available at the DBMS.

This function is an alias to mysql_result( ) . It is therefore possible to supply a third argument to this function, but it should not be used in practice with mysql_db_name( ) .

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_list_dbs($connection);      echo "The databases available are:\n";      for ($x=0; $x < mysql_num_rows($result); $x++)      echo mysql_db_name($result, $x) . "\n"; ?> 
mysql_drop_db

 boolean mysql_drop_db(string   database   [, cresource   connection   ]) 

Drops a database . An optional connection can be supplied; otherwise , the most recently opened connection resource handle that is still open is used. This function permanently deletes the database, its tables, and its data.

The function returns true on success and false on failure.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      if (mysql_drop_db("temp", $connection))       echo "Dropped database 'temp'";   else      echo "Drop database 'temp' failed!"; ?> 
mysql_errno

 int mysql_errno([resource   connection   ]) 

Returns the error number of the most recently executed MySQL function or zero if no error occurred. An optional connection can be supplied; otherwise, the most-recently opened connection resource handle that is still open is used. Any successful MySQL-related function call resets the value of this function to zero, with the exceptions of mysql_error( ) and mysql_errno( ) , which do not change the value.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      mysql_select_db("not-a-database", $connection);      // Prints MySQL reported error 1049   if (mysql_errno(  ))      echo "MySQL reported error " . mysql_errno(  ); ?> 
mysql_error

 string mysql_error([cresource   connection   ]) 

Returns the text of the error message associated with the most recently executed MySQL function or '' (the empty string) if no error occurred. An optional connection can be supplied; otherwise, the most recently opened connection resource handle that is still open is used. Any successful MySQL-related function call resets the text to '' (the empty string), with the exceptions of mysql_error( ) and mysql_errno( ) , which do not change this value.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      mysql_select_db("not-a-database", $connection);      // Prints MySQL reported error: Unknown database 'not-a-database'   if (mysql_errno(  ))      echo "MySQL reported error: " . mysql_error(  ); ?> 
mysql_escape_string

 string mysql_escape_string (string   input   ) 

Escapes an input string so it can be used as a parameter to mysql_query( ) or mysql_unbuffered_query( ) . The function returns a copy of the input string that has any special characters escaped so it is safe to use in an SQL query. Specifically, it escapes single quote, double quote, NULL , carriage return, line feed, and SUB (substitute) characters.

Example

 <?php   $person = "Steven O'Grady";   $person = mysql_escape_string($person);      // Prints: Steven O\'Grady   echo $person; ?> 
mysql_fetch_array

 array mysql_fetch_array (qresource   query   [, int   array_type   ]) 

Returns an array that contains the next available row from the result set associated with the parameter query . The internal pointer associated with the result set is then incremented, so that the next call to this function will retrieve the next row. The query resource handle is returned from a prior call to mysql_query( ) or mysql_unbuffered_query( ) . The function returns false when no more rows are available.

Each row is returned as an array, and all elements of the array are of type string . The second parameter, array_type , controls whether associative access, numeric access, or both are possible on the array. When set to MYSQL_ASSOC , the function behaves identically to mysql_fetch_assoc( ) . When set to MYSQL_NUM , the function behaves identically to mysql_fetch_row( ) . The default is MYSQL_BOTH , which permits both associative and numeric array access.

If two or more attributes in the query have the same name, only the last-named attribute in the SELECT clause is available via the associative array. The other attributes with identical names must be accessed via their numeric indexes.

When both a table and attribute name are used in a SELECT statement, only the attribute name should be used to access the data associatively. For example, after executing the statement SELECT p.quantity FROM presents p , the attribute data is accessed associatively in the array $row that is returned from mysql_fetch_array( ) as $row["quantity"] .

Attributes can be aliased and then retrieved using the alias name. For example, consider the following statement:

 SELECT customer.cust_id AS c,         orders.cust_id AS o  FROM orders, customer  WHERE customer.cust_id = orders.cust_id 

The attribute data can be accessed in an associate array $row that is returned from mysql_fetch_array( ) as $row["c"] and $row["o"] .

Aggregate functions are associatively referenced using the aggregate function name. For example, after executing the statement SELECT sum(quantity) FROM presents , the aggregate data is accessed associatively in the array $row that is returned from mysql_fetch_array( ) as $row["sum(quantity)"] .

Prior to PHP 4.0.5, NULL values were not returned into the array, but this has been fixed in recent versions.

This bug doesn't affect associative access, but it causes renumbering for numeric access. If a table has a NULL attribute, the array returned has one fewer element. The missing element can still be referenced associatively, because referencing a nonexistent element correctly returns NULL . However, if you want to avoid having arrays of different lengths returned, ensure that all attributes have a value or upgrade to a new release of PHP.


Example

 <?php   $query = "SELECT * FROM presents";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      while ($row = mysql_fetch_array($result))   {      echo "ID:\t{$row["present_id"]}\n";      echo "Quantity:\t{$row["quantity"]}\n";      echo "Present:\t{$row["present"]}\n";         echo "Shop:\t{$row["shop"]}\n\n";   } ?> 
mysql_fetch_assoc

 array mysql_fetch_assoc (qresource   query   ) 

Returns an associative array that contains the next available row from the result set associated with the parameter query . The internal pointer associated with the result set is then incremented, so that the next call to this function will retrieve the next row. The query resource handle is returned from a prior call to mysql_query( ) or mysql_unbuffered_query( ) . The function returns false when no more rows are available.

The function behaves identically to mysql_fetch_array( ) when its second parameter is set to MYSQL_ASSOC . See the description of mysql_fetch_array( ) for the limitations of associative access to query results.

Example

 <?php   $query = "SELECT people_id FROM people";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      echo "Users:\n";      while ($row = mysql_fetch_assoc($result))      echo $row["people_id"] . "\n"; ?> 
mysql_fetch_field

 object mysql_fetch_field(qresource   query   [, int   attribute   ]) 

Returns an object containing metadata about an attribute associated with a query resource handle. The first argument is a query resource handle returned from a prior call to mysql_list_fields( ) , mysql_query( ) , or mysql_unbuffered_query( ) . The second optional parameter indicates which attribute in the result set is required. If no second argument is provided, metadata about the first attribute that has not yet been retrieved is returned. Thus, successive calls to mysql_fetch_fields( ) can be used to retrieve information about all the attributes in a query result set.

The properties of the object returned by the function are:

name

The attribute name

table

The name of the table to which the attribute belongs

max_length

The maximum length of the attribute

not_null

Set to one if the attribute cannot be NULL

primary_key

Set to one if the attribute forms part of a primary key

unique_key

Set to one if the attribute is a unique key

multiple_key

Set to one if the attribute is a non-unique key

numeric

Set to one if the attribute is a numeric type

blob

Set to one if the attribute is a BLOB type

type

The type of the attribute

unsigned

Set to one if the attribute is an unsigned numeric type

zerofill

Set to one if the numeric column is zero filled

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      $result = mysql_list_fields("wedding", "presents");      echo "Presents table attributes:\n";      while ($row = mysql_fetch_field($result))   {      echo $row->name;                echo " is an attribute of type " . $row->type . ".";      if ($row->not_null == true)         echo " It cannot be null.\n";      else         echo " It can be null.\n";   } ?> 
mysql_fetch_lengths

 array mysql_fetch_lengths(qresource   query   ) 

Returns an array of attribute lengths associated with the most recently retrieved row of data. The argument to the function is a query result handle that has been used to retrieve at least one row. The elements of the returned array correspond to the length of the values in the array returned from the most recent call to mysql_fetch_row( ) , mysql_fetch_array( ) , mysql_fetch_object( ) , or mysql_fetch_assoc( ) .

This function returns the length of a value within the specific result set, not the maximum length of an attribute as defined in the database table. Use the function mysql_field_len( ) to retrieve the maximum length of an attribute as defined in the database table.

The function returns false on error.

Example

 <?php   $query = "SELECT * FROM presents";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);        while ($row = mysql_fetch_row($result))   {      echo "The total length of this row is: ";      $row2 = mysql_fetch_lengths($result);           $length = 0;      foreach ($row2 as $element)         $length += $element;        echo $length . "\n";   } ?> 
mysql_fetch_object

 object mysql_fetch_object(qresource   query   [, int   array_type   ]) 

Returns an object that contains the next available row from the result set associated with the parameter query . The internal pointer associated with the result set is then incremented, so that the next call to this function will retrieve the next row. The query resource handle is returned from a prior call to mysql_query( ) or mysql_unbuffered_query( ) . The function returns false when no more rows are available.

Each row is returned as an object, and all member variables of the object are of type string . The second parameter array_type should be included and set to MYSQL_ASSOC . Numeric indexes cannot be used to access objects.

The same associative access limitations that apply to mysql_fetch_array( ) apply to mysql_fetch_object( ) . There is one additional limitation: aggregate functions must be aliased for associative access, because parentheses and other special characters are invalid in member variable names. Thus, the sum( ) function in the statement SELECT sum(quantity) as total FROM presents can be accessed associatively in the object $row as $row->total .

Example

 <?php   $query = "SELECT * FROM presents";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      while ($row = mysql_fetch_object($result, MYSQL_ASSOC))   {      echo "\n\nQuantity:\t" . $row->quantity;      echo "\nPresent:\t" . $row->present;      echo "\nShop:\t" . $row->shop;   } ?> 
mysql_fetch_row

 array mysql_fetch_row(qresource   query   ) 

Returns a numerically indexed array that contains the next available row from the result set associated with the parameter query . The internal pointer associated with the result set is then incremented, so that the next call to this function will retrieve the next row. The query resource handle is returned from a prior call to mysql_query( ) or mysql_unbuffered_query( ) . Returns false when no more rows are available.

The function behaves identically to mysql_fetch_array( ) when its second parameter is set to MYSQL_NUM . Unlike mysql_fetch_assoc( ) (and mysql_fetch_array( ) with the MYSQL_BOTH or MYSQL_ASSOC second parameter), the array returned by mysql_fetch_row( ) contains all the attributes of the result set, even if some attributes have the same name.

Example

 <?php   $query = "SELECT * FROM presents";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      while ($row = mysql_fetch_row($result))   {     for($x=0;$x<mysql_num_fields($result);$x++)        echo $row[$x] . " ";      echo "\n";   } ?> 
mysql_field_flags

 string mysql_field_flags(qresource   query   , int   attribute   ) 

Returns a string containing any special flags associated with an attribute in a query result set. The first argument is a query resource handle returned from a prior call to mysql_list_fields( ) , mysql_query( ) , or mysql_unbuffered_query( ) . The second argument is the ordinal number of the attribute in the SQL query. The first attribute is numbered zero.

The flags are returned as a string and are delimited with a space character. The following flags are reported:

not_null

The attribute cannot contain a NULL value.

primary_key

The attribute is a primary key.

unique_key

The attribute is a unique key.

multiple_key

The attribute is a non-unique key.

blob

The attribute is a BLOB type.

unsigned

The attribute is an unsigned integer.

zerofill

The attribute is a zero-filled numeric.

binary

The attribute may contain binary data and will use binary-safe comparisons.

enum

The attribute is an enumeration, which can contain one of several predefined values.

auto_increment

The attribute has the auto_increment modifier.

timestamp

The attribute is an automatic timestamp field.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      $result = mysql_list_fields("wedding", "presents");      for($x=0; $x < mysql_num_fields($result); $x++)      echo mysql_field_name($result, $x) .            " has the properties: " .            mysql_field_flags($result, $x) . "\n"; ?> 
mysql_field_name

 string mysql_field_name(qresource   query   , int   attribute   ) 

Returns the name of an attribute in a result set. The first parameter is a query resource handle returned from a prior call to mysql_list_fields( ) , mysql_query( ) , or mysql_unbuffered_query( ) . The second argument is the ordinal number of the attribute in the SQL query. The first attribute is numbered zero.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      $result = mysql_list_fields("wedding", "presents");      echo "The attributes of presents are: ";   for($x=0; $x < mysql_num_fields($result); $x++)      echo mysql_field_name($result, $x) . " "; ?> 
mysql_field_len

 int mysql_field(qresource   query   , int   attribute   ) 

Returns the defined maximum length of an attribute in a result set. The first parameter is a query resource handle returned from a prior call to mysql_list_fields( ) , mysql_query( ) , or mysql_unbuffered_query( ) . The second argument is the ordinal number of the attribute in the SQL query. The first attribute is numbered zero.

This function returns the maximum length of the attribute as defined in the database table, not the length of a value within a specific result set. Use the function mysql_fetch_lengths( ) to retrieve the length of specific values.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      $result = mysql_list_fields("wedding", "presents");      for($x=0; $x < mysql_num_fields($result); $x++)      echo mysql_field_name($result, $x) .            " has a maximum length of " .            mysql_field_len($result, $x) . "\n"; ?> 
mysql_field_seek

 boolean mysql_field_seek(qresource   query   , int   attribute   ) 

Sets the internal attribute pointer within a result set. The first parameter is a query resource handle returned from a prior call to mysql_query( ) or mysql_unbuffered_query( ) . The second argument is the ordinal number of the attribute in the SQL query, where zero refers to the first attribute.

After the attribute pointer has been set using this function, the next call to mysql_fetch_field( ) will return the attribute at this pointer.

The function returns true on success and false on error.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      $result = mysql_list_fields("wedding", "presents");      mysql_field_seek($result, 2);      $field = mysql_fetch_field($result);      echo "The third attribute is " . $field->name; ?> 
mysql_field_table

 string mysql_field_table(qresource   query   , int   attribute   ) 

Returns the name of the table that contains the specified attribute. The first parameter is a query resource handle returned from a prior call to mysql_query( ) , mysql_list_fields( ) , or mysql_unbuffered_query( ) . The second argument is the ordinal number of the attribute in the SQL query, where zero refers to the first attribute.

Example

 <?php   $query = "SELECT * FROM presents, people WHERE presents.people_id = people.people_id";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      echo "The table associated with the first attribute is " . mysql_field_table($result, 1); ?> 
mysql_field_type

 string mysql_field_type(qresource   query   , int   attribute   ) 

Returns the PHP type of an attribute. The first parameter is a query resource handle returned from a prior call to mysql_list_fields( ) , mysql_query( ) , or mysql_unbuffered_query( ) . The second argument is the ordinal number of the attribute in the SQL query, where zero refers to the first attribute.

Example

 <?php   $query = "SELECT * FROM presents, people WHERE presents.people_id = people.people_id";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      for($x=0;$x<mysql_num_fields($result);$x++)      echo "The type of attribute $x is " . mysql_field_type($result, $x) . "\n"; ?> 
mysql_free_result

 boolean mysql_free_result(qresource query) 

Frees all memory used by a query resource handle. This occurs automatically at the end of a script, but this function may be useful in scripts where repeated querying is performed or memory is constrained.

The function returns true on success and false on error.

Example

 <?php   $query = "SELECT * FROM presents";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      while ($row = mysql_fetch_object($result, MYSQL_ASSOC))   {      echo "\n\nQuantity:\t" . $row->quantity;      echo "\nPresent:\t" . $row->present;      echo "\nShop:\t" . $row->shop;   }      mysql_free_result($result); ?> 
mysql_get_client_info

 string mysql_get_client_info(  ) 

Returns a string that describes the MySQL client library used by PHP. This is currently the library version number. This function is available only in PHP 4.0.5 or later versions.

Example

 <?php   // Prints (on our machine): This is the 3.23.44 MySQL client library.   echo "This is the " . mysql_get_client_info(  ) . " MySQL client library."; ?> 
mysql_get_host_info

 string mysql_get_host_info([cresource   connection   ]) 

Returns a string that describes a MySQL server connection. The string contains the type of connection (TCP or Unix socket) and the hostname. An optional connection resource handle may be provided as the parameter; otherwise, the most recently opened connection that is still open is assumed. This function is available only in PHP 4.0.5 or later versions.

Example

 <?php   mysql_connect("localhost", "root", "drum");      // Prints: This is a Localhost via UNIX socket connection to MySQL.      echo "This is a " . mysql_get_host_info(  ) . " connection to MySQL."; ?> 
mysql_get_proto_info

 int mysql_get_proto_info([cresource   connection   ]) 

Returns an integer that is the protocol version used in a MySQL server connection. An optional connection resource handle may be provided as the parameter; otherwise, the most recently opened connection that is still open is assumed. This function is available only in PHP 4.0.5 or later versions.

Example

 <?php echo "You are connected to MySQL using protocol " . mysql_get_proto_info(  );  ?> 
mysql_get_server_info

 string mysql_get_server_info([cresource   connection   ]) 

Returns as a string the version of the MySQL DBMS. An optional connection resource handle may be provided as the parameter, otherwise, the most recently opened connection that is still open is assumed. This function is available only in PHP 4.0.5 or later versions.

Example

 <?php   mysql_connect("localhost", "fred", "shhh");      // Prints (on our machine): This is MySQL version 3.23.44-log         echo "This is MySQL version " . mysql_get_server_info(  );       ?> 
mysql_insert_id

 int mysql_insert_id([cresource   connection   ]) 

Returns the most recently generated AUTO_INCREMENT identifier value associated with a connection. An optional connection resource handle may be provided as the parameter; otherwise, the most recently opened connection that is still open is assumed.

This function works for a connection and not on a per-query basis. Subsequent INSERT statements through the same connection make it impossible to retrieve previous identifier values using this function. The return value is not affected by non- INSERT SQL statements.

The function returns false if there have been no AUTO_INCREMENT identifiers for the connection.

Example

 <?php   $query = "INSERT INTO presents              VALUES(NULL, \"Bike\", \"Fitzroy Cycles\", 1, \"Red\", \"350.00\", NULL)";      $connection = mysql_connect("localhost", "root", "drum");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      echo "Inserted record " . mysql_insert_id($connection);             ?> 
mysql_list_dbs

 qresource mysql_list_dbs([cresource   connection   ]) 

Returns a query resource handle that can be used to retrieve the names of the databases available on a connection; the database names are retrieved with a subsequent call to mysql_db_name( ) . An optional connection resource handle may be provided as the parameter; otherwise, the most recently opened connection that is still open is assumed.

Returns false on error.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_list_dbs($connection);      echo "The databases available are:\n";      for ($x=0; $x < mysql_num_rows($result); $x++)      echo mysql_db_name($result, $x) . "\n"; ?> 
mysql_list_fields

 qresource mysql_list_fields(string   database   , string   table   [, cresource   connection   ]) 

Returns a query resource handle that can be used to retrieve information about the attributes of the specified table within the given database . The attribute information can be retrieved through the functions mysql_field_name( ) , mysql_field_type( ) , mysql_field_len( ) , and mysql_field_flags( ) . An optional connection resource handle may be provided as the third parameter; otherwise, the most recently opened connection that is still open is assumed.

Returns false on failure.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      $result = mysql_list_fields("wedding", "presents");      echo "The attributes of presents are: ";   for($x=0; $x < mysql_num_fields($result); $x++)      echo mysql_field_name($result, $x) . " "; ?> 
mysql_list_tables

 qresource mysql_list_tables(string   database   [, cresource   connection   ]) 

Returns a query resource handle that can be used to retrieve information about the tables within the given database . The table name information can be retrieved with the function mysql_tablename( ) . An optional connection resource handle may be provided as the second parameter; otherwise, the most recently opened connection that is still open is assumed.

Returns false on failure.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      $result = mysql_list_tables("wedding", $connection);      echo "The tables of wedding are: ";              for($x=0; $x < mysql_num_rows($result); $x++)      echo mysql_tablename($result, $x) . " "; ?> 
mysql_num_fields

 int mysql_num_fields(qresource   query   ) 

Returns the number of attributes in a row associated with the query resource handle parameter. The query resource handle is returned from a prior call to mysql_list_fields( ) , mysql_query( ) , or mysql_unbuffered_query( ) .

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      $result = mysql_list_fields("wedding", "presents", $connection);      echo "There are " . mysql_num_fields($result) . " attributes in presents"; ?> 
mysql_num_rows

 int mysql_num_rows(qresource   query   ) 

Returns the number of rows associated with a query resource handle. The query resource handle is returned from a prior call to mysql_query( ) . The function does not work with mysql_unbuffered_query( ) . In addition, this function works only for SELECT queries; the number of rows affected by an SQL INSERT , UPDATE , or DELETE statement should be determined using the function mysql_affected_rows( ) .

Example

 <?php   $query = "SELECT * FROM presents WHERE present LIKE 'M%'";       $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      echo "There are " . mysql_num_rows($result) . " presents that begin with M"; ?> 
mysql_pconnect

 cresource mysql_pconnect([string   hostname   [, string   username   [, string   password   ]]]) 

Used to establish or re-establish a connection to the MySQL DBMS. The function returns a connection resource handle on success that can be used to access databases through subsequent function calls. The function returns false on failure. The three parameters are identical to the first three parameters of mysql_connect( ) .

This function should be called only once with the same parameters in a script: any subsequent calls to mysql_pconnect( ) in the same script with the same parameters return the same connection handle. Indeed, connections created with mysql_pconnect( ) are often reused across several scripts: the p stands for persistent, which means that after the script ends, the connection is kept in a pool. The connection can then be reused by any other script that requires a connection with the same hostname , username , and password .

Connections in the pool that remain unused are closed to save resources. How long a connection can remain unused is a MySQL parameter and is set to a default of five seconds. This can be changed with the -- set-variable connect_timeout parameter to safe_mysqld .

Persistent connections are available only through a PHP module that is integrated into a web server. See Chapter 11 for details.

Example

 <?php   $query = "SELECT * FROM presents";      $connection = mysql_pconnect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      while ($row = mysql_fetch_array($result))      echo $row["present"] . "\n";    ?> 
mysql_query

 qresource mysql_query(string   query   [, cresource   connection   ]) 

Executes an SQL query and (usually) returns a query resource handle that can be used to retrieve the result set. The query statement does not need to be terminated with a semicolon, and any valid SQL statement is permitted. If the connection resource handle parameter is omitted, the last opened connection that is still open is assumed. If no connection is open, an attempt is made to open one, just as when mysql_connect( ) is issued with no parameters.

On success, the function never returns a false value. For SELECT , SHOW , EXPLAIN , or DESCRIBE queries, the function returns a query result resource that can be used to fetch data. For other SQL queries, the function returns true on success. The function returns false on failure.

Example

 <?php   $query = "SELECT * FROM people WHERE people_id LIKE 'h%'";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      while ($row = mysql_fetch_array($result))      echo $row["people_id"] . "\n";    ?> 
mysql_result

 string mysql_result(qresource   query   , int   row   [, mixed   attribute   ]) 

Retrieves as a string attribute value from a query resource handle. The row that contains the attribute is the second parameter; rows are numbered starting at zero. By default, the first attribute of the row is returned. A specific attribute can be provided as the third parameter. This attribute can be specified by using the attribute's ordinal position in the SQL query (where the first attribute is numbered zero), its name as given in the SQL query, or the fully qualified SQL name of the attribute (using the table.attribute notation). The ordinal position alternative executes much faster than the other alternatives and should be used wherever possible.

mysql_result() is different from functions that return entire rows, such as mysql_fetch_row( ) .

You should not mix calls to mysql_result( ) with calls to other functions that read data from a result set. Also, mysql_result( ) is much slower for reading row data than row-specific functions, and should not be used for this task.


Example

 <?php   $query = "SELECT count(*) FROM people";      $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_query($query, $connection);      $count = mysql_result($result, 0);   echo "There are " . $count . " rows in people"; ?> 
mysql_select_db

 boolean mysql_select_db(string   database   [, cresource   connection   ]) 

Use the specified database on a connection. Subsequent calls to query functions (such as mysql_query( ) or mysql_unbuffered_query( ) ) will execute on this database . An optional connection resource handle may be provided; otherwise, the most recently opened connection that is still open is assumed. If no connection is open, an attempt is made to open one with a mysql_connect( ) call that has no parameters.

This function returns true on success and false on failure.

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection); ?> 
mysql_tablename

 string mysql_tablename(qresource   query   , int   table   [, mixed unused]) 

Returns the name of a table from a prior query with mysql_list_tables( ) . The second argument is an ordinal table index into the query result set, where the first table in the result set is numbered zero. The number of table names in the result set can be determined using mysql_num_rows( ) .

This function returns false on error. A common source of error is supplying a table number that is greater than the number of tables available in the database.

This function is an alias to mysql_result( ) . It is therefore possible to supply a third argument to this function, but it should not be used in practice with mysql_tablename( ) .

Example

 <?php   $connection = mysql_connect("localhost", "fred", "shhh");      $result = mysql_list_tables("wedding", $connection);      echo "The tables of wedding are: ";              for($x=0; $x < mysql_num_rows($result); $x++)      echo mysql_tablename($result, $x) . " "; ?> 
mysql_unbuffered_query

 qresource mysql_unbuffered_query(string   query   [, cresource   connection   ]) 

Execute a query without retrieving and buffering the entire result set. This is useful for queries that return large results sets or that are slow to execute. The advantage is that you don't need the memory resources to store the complete result set, and the function will return before the SQL query has finished. In contrast, the function mysql_query( ) does not return until the query is finished and all of the results have been buffered for subsequent retrieval. The parameters and return values are identical to mysql_query( ) .

The disadvantage of mysql_unbuffered_query( ) is that mysql_num_rows( ) cannot be called for the returned query resource handle, because the number of rows returned from the query is not known. The function is otherwise identical in behavior to mysql_query( ) .

This function is available in PHP 4.0.6 or later versions.

Because of the internal MySQL workings of this function, it is important that you finish processing a result set created with mysql_unbuffered_query( ) before creating a new query. Failure to do so may create unpredictable results.

Example

 <?php   $query = "SELECT * FROM presents";      $connection = mysql_pconnect("localhost", "fred", "shhh");   mysql_select_db("wedding", $connection);      $result = mysql_unbuffered_query($query, $connection);      while ($row = mysql_fetch_array($result))      echo $row["present"] . "\n"; ?> 
only for RuBoard - do not distribute or recompile


Managing and Using MySQL
Managing and Using MySQL (2nd Edition)
ISBN: 0596002114
EAN: 2147483647
Year: 2002
Pages: 137

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