The PHP API for MySQL

 

Introduction.  Well, how's this title for a string of acronyms?  You're likely to be aware of PHP and MySQL, but API stands for Application Programming Interface.  Basically, it relates to what is built in to PHP in order to better interface with MySQL.

Two of the best places I know of to find other documentation are

I am putting all of the functions I currently know about in a table.  Hopefully, this list will grow.  I have certainly not been as successful as I would like in finding these sorts of built in functions.

 

Function Description Subtleties
mysql_affected_rows([link_identifier]) This returns the number of rows affected in the most recent MySQL operation that actually modified data.
  • returns -1 on errors
  • returns 0 if all rows deleted
  • when used within transactions it must be invoked before the COMMIT
mysql_close([link_identifier]) Close the connection of a particular link.  Closes the current active connection if the link is unspecified.
  • returns TRUE when successful
  • returns FALSE when fails
  •  
mysql_connect([server[, username[,password[,new_link]]]]) Opens a connection to a MySQL Server.  Many of these parameters default to desired values when working on a server you control.
 
  • it returns a link_identifier if successful
  • returns FALSE if it fails
mysql_create_db(database_name[,link_identifier]]) Creates a database with the specified name via the link.  Most servers won't give such permissions automatically.
  • returns TRUE if creates
  • returns FALSE if fails
mysql_data_seek(result,row_number)

 

 This moves the internal pointer result within a result_set to the row determined by row_number.
  • usually used after invoking a query and just before fetching a row
mysql_db_name(result, offset[,field]) This returns the database name of a field in a result.
  • after calling mysql_list_dbs( ) this function will return the name of the table indexed by offset
mysql_errno([link_identifier]) Returns the error message number from the previous MySQL operation or 0 if no error occurred.
 		  
mysql_error([link_identifier]) Returns the error message text from the previous MySQL operation or an empty string if no error occurred.
 		  
mysql_escape_string(raw_string) This escapes special characters in a string given by raw_string to be used in an SQL statement.
 
  • doesn't account for current character set of the connection
mysql_fetch_array(result)

 

 This fetches one row of data from the resultset associated with the identifier result.  This will return a numeric array, associative array or both.
  • each call fetches the next row of data
  • when there are no more rows it re\turns FALSE
mysql_fetch_assoc(result) This fetches a result row as an associate array.
  • each call fetches the next row of data
  • when there are no more rows it re\turns FALSE
mysql_fetch_field(result,[offset])

 

 This gets column information from a result and returns it as an object.

This object has the following properties

  • name - column name
  • table - the name of the table to which the column belongs
  • not_null - returns 1 if the column is NOT NULL
  • primary_key - returns 1 if the column is the primary key
  • unique_key - returns 1 if the column is a unique key
  • multiple_key - returns 1 if the column is a non-unique key
  • numeric - set to 1 if the column is of a numeric type
  • blob - set to 1 if the column is of type BLOB
  • max_length - the column's maximum length
  • type - the type of column
  • unsigned - set to 1 if the column is unsigned
  • zerofill - set to 1 if the column has the ZEROFILL attribute
  • the offset is an integer
  • if the offset isn't specified it returns the next field that has not already been reported by this method
mysql_fetch_lengths(result) This returns an array representing the lengths of the data in the fields retrieved by the last _fetch_( ) function call.
 		  
mysql_fetch_object(result)

 

 This fetches one row of data from the result as an object.
  • each call fetches the next row of data
  • when there are no more rows it re\turns FALSE
  • columns are accessible by names but not numbers
mysql_fetch_row(result)

 

 This fetches one row of data from the result as a numerically indexed array.
  • each call fetches the next row of data
  • when there are no more rows it re\turns FALSE
  • columns are accessible by  numbers
mysql_field_flags(result, offset) Returns the flag associated with a field in a result.
  • auto_increment
  • not_null
  • primary_key
  • unique_key
  • multiple_key
  • blob
  • unsigned
  • zerofill
  • binary
  • enum
  • timestamp
  • varies based on version
  
mysql_field_len(result, offset) Returns the length of a specified field in a result.
 		  
mysql_field_name(result, offset)
 		 Returns the name of a specified field in a result.  
mysql_field_seek(result, offset)
 		 Sets the result pointer to a specified field offset.  
mysql_field_table(result, offset)
 		 Returns the name of the table the specified field is in.  
mysql_field_type(result, offset)
 		 Returns the type of a specified field in a result.  
mysql_free_result(result)
 		 Frees all memory in use by the pointer result.
  • TRUE when successful
  • FALSE when not
mysql_get_client_info( )
 		 Returns the version of the MySQL client library.  
mysql_get_host_info([link_identifier])
 		 Returns a string that specifies the hostname and the type of connection that's in use.
  • if link_identifier is unspecified the last open connection is used
mysql_get_proto_info([link_identifier])
 		 Returns an integer with the protocol version of connection that's in use.
  • if link_identifier is unspecified the last open connection is used
mysql_get_server_info([link_identifier])
 		 Returns a string with the version of MySQL  that's in use.
 		  
mysql_insert_id([link_identifier])
 		 Returns the ID generated in an AUTO_INCREMENT column from the previous INSERT operation.  
mysql_list_dbs([link_identifier])
 		 List the databases available on a MySQL server.  
mysql_list_fields(database_name, table_name[,link_identifier])
 		 Lists the names of the columns in a given table in a given database.  
mysql_list_tables(database_name[,link_identifier])
 		 Lists the tables in a MySQL database.  
mysql_number_fields(result)
 		 Returns the number of fields in a given result.  
mysql_number_rows(result)
 		 Returns the number of rows in a given result.  
mysql_pconnect([server[, username[,password]]]) Opens a persistent connection to a MySQL server.

These connections persist even if a mysql_close( ) is encountered or a script finishes execution.  The next time the script attempts to form the connection this persistent link will be used again.
  • returns a resource identifier if the connection is successful
  • returns FALSE if the connection fails
mysql_query(query[, link_identifier][,result_mode])
 		 Sends a query to the currently selected database.  Some sort of query_string needs to be developed.
 
  • returns a resource identifier when appropriate
  • returns TRUE for other types of queries if it executes properly
  • returns FALSE if there is an error
mysql_real_escape_string(raw_string[, link_identifier]) This escapes special characters in a string given by raw_string to be used in an SQL statement.
  • does account for current character set of the connection
 
mysql_result(result, row_offset[,field])
 		 Returns the contents of one cell of a result based on row offset and possibly field.
 		  
mysql_select_db(database_name[,link_identifier])
 		 Selects a MySQl database
  • returns TRUE if successful
  • returns FALSE otherwise
mysql_tablename(result,offset)
 		 Returns the table name of a field in a result.  
mysql_unbuffered_query(query[, link_identifier][,result_mode]) Sends a query to the currently selected database.  It doesn't fetch and buffer the resultset rows automatically.  Some sort of query_string needs to be developed.