MySQL Functions

MySQL Functions

mysql_affected_rows

Name

mysql_affected_rows — get number of affected rows in last query

Description

int mysql_affected_rows(int [link_identifier] );

Returns: The number of affected rows by the last query.

mysql_affected_rows returns the number of rows affected by the last INSERT, UPDATE or DELETE query on the server associated with the specified link identifier. If the link identifier isn't specified, the last opened link is assumed.

This command is not effective for SELECT statements, only on statements which modify records. To retrieve the number of rows returned from a SELECT, use mysql_num_rows.

mysql_close

Name

mysql_close — close MySQL connection

Description

int mysql_close(int [link_identifier] );

Returns: true on success, false on error

mysql_close closes the link to a MySQL database that's associated with the specified link identifier. If the link identifier isn't specified, the last opened link is assumed.

Note that this isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

mysql_close will not close persistent links generated by mysql_pconnect(). See also: mysql_connect, and mysql_pconnect.

mysql_connect

Name

mysql_connect — open MySQL server connection

Description

int mysql_connect(string [hostname] , string [username] , string [password]

);

Returns: A positive MySQL link identifier on success, or false on error.

mysql_connect establishes a connection to a MySQL server. All of the arguments are optional, and if they're missing, defaults are assumed ('localhost', user name of the user that owns the server process, empty password). The hostname string can also include a port number. eg. "hostname:port"

In case a second call is made to mysql_connect with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close.

See also mysql_pconnect, and mysql_close.

mysql_create_db

Name

mysql_create_db — create MySQL database

Description

int mysql_create_db(string database name, int [link_identifier] );

mysql_create_db attempts to create a new database on the server associated with the specified link identifier.

See also: mysql_drop_db. For downwards compatibility mysql_createdb can also be used.

mysql_data_seek

Name

mysql_data_seek — move internal row pointer

Description

int mysql_data_seek(int result_identifier, int row_number);

Returns: true on success, false on failure

mysql_data_seek moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to mysql_fetch_row would return that row.

mysql_dbname

Name

mysql_dbname — get current MySQL database name

Description

string mysql_dbname(string result, int i);

mysql_dbname returns the database name stored in position i of the result pointer returned from the mysql_list_dbs function. The mysql_num_rows function can be used to determine how many database names are available.

mysql_db_query

Name

mysql_db_query — send MySQL query

Description

int mysql_db_query(string database, string query, int link_identifier);

Returns: A positive MySQL result identifier to the query result, or false on error.

mysql_db_query selects a database and executes a query on it. If the optional link identifier isn't specified, the function will try to find an open link to the MySQL server and if no such link is found it'll try to create one as if mysql_connect was called with no arguments

See also mysql_connect. For downwards compatibility mysql can also be used.

mysql_drop_db

Name

mysql_drop_db — drop (delete) MySQL database

Description

int mysql_drop_db(string database_name, int [link_identifier] );

Returns: true on success, false on failure.

mysql_drop_db attempts to drop (remove) an entire database from the server associated with the specified link identifier.

See also: mysql_create_db. For downward compatibility mysql_dropdb can also be used.

mysql_errno

Name

mysql_errno — returns error number of last mysql call

Description

int mysql_errno();

Errors coming back from the mySQL database backend no longer issue warnings. Instead, use these functions to retrieve the error number.

<?php mysql_connect("marliesle");

echo mysql_errno().": ".mysql_error()." "; mysql_select_db("nonexistentdb");

echo mysql_errno().": ".mysql_error()." ";

$conn = mysql_query("SELECT * FROM nonexistenttable"); echo mysql_errno().": ".mysql_error()." ";

mysql_error

Name

mysql_error — returns error message of last mysql call

Description

string mysql_error();

Errors coming back from the mySQL database backend no longer issue warnings. Instead, use these functions to retrieve the error string.

<?php mysql_connect("marliesle");

echo mysql_errno().": ".mysql_error()." "; mysql_select_db("nonexistentdb");

echo mysql_errno().": ".mysql_error()." ";

$conn = mysql_query("SELECT * FROM nonexistenttable"); echo mysql_errno().": ".mysql_error()." ";

mysql_fetch_array

Name

mysql_fetch_array — fetch row as array

Description

array mysql_fetch_array(int result);

Returns: An array that corresponds to the fetched row, or false if there are no more rows.

mysql_fetch_array is an extended version of mysql_fetch_row. In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys.

An important thing to note is that using mysql_fetch_array is NOT significantly slower than using

mysql_fetch_row, while it provides a significant added value. For further details, also see mysql_fetch_row

Example 1. mysql fetch array

<?php mysql_connect($host,$user,$password);

$result = mysql_db_query("database","select * from table"); while($row = mysql_fetch_array($result)) {

echo $row["user_id"]; echo $row["fullname"];

}

mysql_free_result($result);

mysql_fetch_field

Name

mysql_fetch_field — get field information

Description

object mysql_fetch_field(int result, int [field_offset] );

Returns an object containing field information.

mysql_fetch_field can be used in order to obtain information about fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by mysql_fetch_field is retrieved.

The properties of the object are:

name - column name
table - name of the table the column belongs to
max_length - maximum length of the column
not_null - 1 if the column cannot be null
primary_key - 1 if the column is a primary key
unique_key - 1 if the column is a unique key
multiple_key - 1 if the column is a non-unique key
numeric - 1 if the column is numeric
blob - 1 it the column is a BLOB
type - the type of the column
unsigned - 1 if the column is unsigned
zerofill - 1 if the column is zero-filled See also

mysql_field_seek

mysql_fetch_lengths

Name

mysql_fetch_lengths — get max data size of output columns

Description

int mysql_fetch_lengths(int result);

Returns: An array that corresponds to the lengths of each field in the last row fetched by

mysql_fetch_row, or false on error.

mysql_fetch_lengths stores the lengths of each result column in the last row returned by

mysql_fetch_row in an array, starting at offset 0. See also: mysql_fetch_row.

mysql_fetch_object

Name

mysql_fetch_object — fetch row as object

Description

int mysql_fetch_object(int result);

Returns: An object with properties that correspond to the fetched row, or false if there are no more rows.

mysql_fetch_object is similar to mysql_fetch_array, with one difference - an object is returned, instead of an array. Indirectly, that means that you can only access the data by the field names, and not by their offsets (numbers are illegal property names).

Speed-wise, the function is identical to mysql_fetch_array, and almost as quick as

mysql_fetch_row (the difference is insignificant).

See also: mysql_fetch_array and mysql_fetch_row.

mysql_fetch_row

Name

mysql_fetch_row — get row as enumerated array

Description

array mysql_fetch_row(int result);

Returns: An array that corresponds to the fetched row, or false if there are no more rows.

mysql_fetch_row fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0.

Subsequent call to mysql_fetch_row would return the next row in the result set, or false if there are no more rows.

See also: mysql_fetch_array, mysql_fetch_object, mysql_data_seek, mysql_fetch_lengths, and mysql_result.

mysql_field_name

Name

mysql_field_name — get field name

Description

string mysql_field_name(string result, int i);

mysql_field_name returns the name of the specified field. Arguments to the function is the result identifier and the field index, ie. mysql_field_name($result,2);

Will return the name of the second field in the result associated with the result identifier. For downwards compatibility mysql_fieldname can also be used.

mysql_field_seek

Name

mysql_field_seek — set field offset

Description

int mysql_field_seek(int result, int field_offset);

Seeks to the specified field offset. If the next call to mysql_fetch_field won't include a field offset, this field would be returned.

mysql_field_table

Name

mysql_field_table — get table name for field

Description

string mysql_field_table(int result, int field_offset);

Get the table name for field. For downward compatibility mysql_fieldtable can also be used.

mysql_field_type

Name

mysql_field_type — get field type

Description

string mysql_field_type(string result, int field_offset);

mysql_field_type is similar to the mysql_field_name function. The arguments are identical, but the field type is returned. This will be one of "int", "real", "string", or "blob".

Example 1. mysql field types

<?php mysql_connect("localhost:3306"); mysql_select_db("wisconsin");

$result = mysql_query("SELECT * FROM onek");

$fields = mysql_num_fields($result);

$rows = mysql_num_rows($result);

$i = 0;

$table = mysql_field_table($result, $i);

echo "Your '".$table."' table has ".$fields." fields and ".$rows." records

";

echo "The table has the following fields "; while ($i < $fields) {

$type = mysql_field_type ($result, $i);

$name = mysql_field_name ($result, $i);

$len = mysql_field_len ($result, $i);

$flags = mysql_field_flags ($result, $i);

echo $type." ".$name." ".$len." ".$flags." ";

$i++;

}

mysql_close();

For downward compatibility mysql_fieldtype can also be used.

mysql_field_flags

Name

mysql_field_flags — get field flags

Description

string mysql_field_flags(string result, int field_offset);

mysql_field_flags returns the field flags of the specified field. The flags are reported as a single word per flag separated by a single space, so that you can split the returned value using explode.

The following flags are reported, if your version of MySQL is current enough to support them: "not_null", "primary_key", "unique_key", "multiple_key", "blob", "unsigned", "zerofill", "binary", "enum", "auto_increment", "timestamp".

For downward compatibility mysql_fieldflags can also be used.

mysql_field_len

Name

mysql_field_len — get field length

Description

int mysql_field_len(string result, int field_offset); mysql_field_len returns the length of the specified field. For downward compatibility

mysql_fieldlen can also be used.

mysql_free_result

Name

mysql_free_result — free result memory

Description

int mysql_free_result(int result);

mysql_free_result only needs to be called if you are worried about using too much memory while your script is running. All associated result memory for the specified result identifier will automatically be freed.

For downward compatibility mysql_freeresult can also be used.

mysql_insert_id

Name

mysql_insert_id — get generated id from last INSERT

Description

int mysql_insert_id(void);

mysql_insert_id returns the ID generated for an AUTO_INCREMENTED field. This function takes no arguments. It will return the auto-generated ID returned by the last INSERT query performed.

mysql_list_fields

Name

mysql_list_fields — list result fields

Description

int mysql_list_fields(string database, string tablename); mysql_list_fields retrieves information about the given tablename. Arguments are the database

name and the table name. A result pointer is returned which can be used with mysql_field_flags, mysql_field_len, mysql_field_name, and mysql_field_type.

A result identifier is a positive integer. The function returns -1 if a error occurs. A string describing the error will be placed in $phperrmsg, and unless the function was called as @mysql() then this error string will also be printed out.

For downward compatibility mysql_listfields can also be used.

mysql_list_dbs

Name

mysql_list_dbs — list MySQL databases on server

Description

int mysql_listdbs(void);

mysql_listdbs will return a result pointer containing the databases available from the current mysql daemon. Use the mysql_dbname function to traverse this result pointer.

For downward compatibility mysql_listdbs can also be used.

mysql_list_tables

Name

mysql_list_tables — list tables in a MySQL database

Description

int mysql_list_tables(string database);

mysql_list_tables takes a database name and result pointer much like the mysql_db_query function. The mysql_tablename function should be used to extract the actual table names from the result pointer.

For downward compatibility mysql_listtables can also be used.

mysql_num_fields

Name

mysql_num_fields — get number of fields in result

Description

int mysql_num_fields(int result); mysql_num_fields returns the number of fields in a result set.

See also: mysql_db_query, mysql_query, mysql_fetch_field, mysql_num_rows. For downward compatibility mysql_numfields can also be used.

mysql_num_rows

Name

mysql_num_rows — get number of rows in result

Description

int mysql_num_rows(string result); mysql_num_rows returns the number of rows in a result set.

See also: mysql_db_query, mysql_query and, mysql_fetch_row. For downward compatibility mysql_numrows can also be used.

mysql_pconnect

Name

mysql_pconnect — open persistent MySQL connection

Description

int mysql_pconnect(string [hostname] , string [username] , string [password]

);

Returns: A positive MySQL persistent link identifier on success, or false on error

mysql_pconnect acts very much like mysql_connect with two major differences.

First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close will not close links established by mysql_pconnect()).

This type of links is therefore called 'persistent'.

mysql_query

Name

mysql_query — send MySQL query

Description

int mysql_query(string query, int [link_identifier] );

Returns: A positive MySQL result identifier on success, or false on error.

mysql_query sends a query to the currently active database on the server that's associated with the specified link identifier. If the link identifier isn't specified, the last opened link is assumed. If no link is open, the function tries to establish a link as if mysql_connect was called, and use it.

See also: mysql_db_query, mysql_select_db, and mysql_connect.

mysql_result

Name

mysql_result — get result data

Description

int mysql_result(int result, int row, mixed field);

Returns: The contents of the cell at the row and offset in the specified MySQL result set.

mysql_result returns the contents of one cell from a MySQL result set. The field argument can be the field's offset, or the field's name, or the field's table dot field's name (fieldname.tablename). If the column name has been aliased ('select foo as bar from...'), use the alias instead of the column name.

When working on large result sets, you should consider using one of the functions that fetch an entire row (specified below). As these functions return the contents of multiple cells in one function call, they're MUCH quicker than mysql_result. Also, note that specifying a numeric offset for the field argument is much quicker than specifying a fieldname or tablename.fieldname argument.

Recommended high-performance alternatives: mysql_fetch_row, mysql_fetch_array, and

mysql_fetch_object.

mysql_select_db

Name

mysql_select_db — select MySQL database

Description

int mysql_select_db(string database_name, int [link_identifier] );

Returns: true on success, false on error

mysql_select_db sets the current active database on the server that's associated with the specified link identifier. If no link identifier is specified, the last opened link is assumed. If no link is open, the function will try to establish a link as if mysql_connect was called, and use it.

Every subsequent call to mysql_query will be made on the active database. See also: mysql_connect, mysql_pconnect, and mysql_query

For downward compatibility mysql_selectdb can also be used.

mysql_tablename

Name

mysql_tablename — get table name of field

Description

string mysql_tablename(int result, int i);

mysql_tablename takes a result pointer returned by the mysql_list_tables function as well as an integer index and returns the name of a table. The mysql_num_rows function may be used to determine the number of tables in the result pointer.

Example 1. mysql_tablename() example

<?php

mysql_connect ("localhost:3306");

$result = mysql_listtables ("wisconsin");

$i = 0;

while ($i < mysql_num_rows ($result)) {

$tb_names[$i] = mysql_tablename ($result, $i); echo $tb_names[$i] . " ";

$i++;

}