A summary of the commonly used MYSQL functions in PHP
mysql_connect() - establish a database connection Format: resource mysql_connect([string hostname [:port] [:/path/to/socket] [, string username] [, string password]) Example: $conn = @mysql_connect(“localhost”, “username”, “password”) or die(“不能连接到Mysql Server”); Note: with this connection you must explicitly close the connection
mysql_pconnect() - establish a database connection Format: resource mysql_pconnect([string hostname [:port] [:/path/to/socket] [, string username] [, string password]) Example: $conn = @mysql_pconnect(“localhost”, “username”, “password”) or dir(“不能连接到Mysql Server”); Note: this connection function does not need an explicit close, it is equivalent to using a connection pool
mysql_close() - close a database connection Example: $conn = @mysql_connect(“localhost”, “username”, “password”) or die(“不能连接到Mysql Server”); @mysql_select_db(“MyDatabase”) or die(“不能选择这个数据库，或数据库不存在”); echo “你已经连接到MyDatabase数据库”; mysql_close();
mysql_select_db() - select a database Format: boolean mysql_select_db(string db_name [, resource link_id]) Example: $conn = @mysql_connect(“localhost”, “username”, “password”) or die(“不能连接到Mysql Server”); @mysql_select_db(“MyDatabase”) or die(“不能选择这个数据库，或数据库不存在”);
mysql_query() - query MySQL Format: resource mysql_query (string query, [resource link_id]) Example: $linkId = @mysql_connect(“localhost”, “username”, “password”) or die(“不能连接到Mysql Server”); @mysql_select_db(“MyDatabase”) or die(“不能选择这个数据库，或者数据库不存在”); $query = “select * from MyTable”; $result = mysql_query($query); mysql_close(); Note: if the SQL query executes successfully it returns a resource identifier, and returns FALSE on failure. If an update executes successfully it returns TRUE, otherwise FALSE
mysql_db_query() - query MySQL Format: resource mysql_db_query(string database, string query [, resource link_id]) Example: $linkId = @mysql_connect(“localhost”, “username”, “password”) or die(“不能连接到MysqlServer”); $query = “select * from MyTable”; $result = mysql_db_query(“MyDatabase”, $query); mysql_close(); Note: to keep the code clear, calling this function is not recommended
mysql_result() - fetch and display data Format: mixed mysql_result (resource result_set, int row [, mixed field]) Example: $query = “select id, name from MyTable order by name”; $result = mysql_query($query); for($count=0;$count<=mysql_numrows($result);$count++) { $c_id = mysql_result($result, 0, “id”); $c_name = mysql_result($result, 0, “name”); echo $c_id,$c_name; } Note: the simplest, and also the least efficient, data fetch function
mysql_fetch_row() - fetch and display data Format: array mysql_fetch_row (resource result_set) Example: $query = “select id, name from MyTable order by name”; $result = mysql_query($query); while (list($id, $name) = mysql_fetch_row($result)) { echo(“Name: $name ($id)
“); } Note: the function fetches a whole row of data from result_set and puts the values into an indexed array. It is usually used together with the list() functionmysql_fetch_array() - fetch and display data Format: array mysql_fetch_array (resource result_set [, int result_type]) Example: $query = “select id, name from MyTable order by name”; $result = mysql_query($query); while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $id = $row[“id”]; $name = $row[“name”]; echo “Name: $name ($id)
“; } Another example: $query = “select id, name from MyTable order by name”; $result = mysql_query($query); while($row = mysql_fetch_array($result, MYSQL_NUM)) { $id = $row[0]; $name = $row[1]; echo “Name: $name ($id)
“; } Note: the values of result_type are: MYSQL_ASSOC: field names are the keys and field contents are the values MYSQL_NUM: a numeric index array, works the same as the mysql_fetch_ros() function MYSQL_BOTH: returns both as an associative array and as a numeric index array. The default value of result_type.mysql_fetch_assoc() - fetch and display data Format: array mysql_fetch_assoc (resource result_set) Equivalent to calling mysql_fetch_array(resource, MYSQL_ASSOC);
mysql_fetch_object() - fetch and display data Format: object mysql_fetch_object(resource result_set) Example: $query = “select id, name from MyTable order by name”; while ($row = mysql_fetch_object($result)) { $id = $row->id; $name = $row->name; echo “Name: $name ($id)
“; } Note: returns an object, and works the same way as mysql_fetch_array()mysql_num_rows() - the number of records selected Format: int mysql_num_rows(resource result_set) Example: query = “select id, name from MyTable where id > 65”; $result = mysql_query($query); echo “有”.mysql_num_rows($result).”条记录的ID大于65”; Note: only useful when you need to determine how many records a select query fetched.
mysql_affected_rows() - the number of records affected by Insert, update, delete Format: int mysql_affected_rows([resource link_id]) Example: $query = “update MyTable set name=’CheneyFu’ where id>=5”; $result = mysql_query($query); echo “ID大于等于5的名称被更新了的记录数：”.mysql_affected_rows(); Note: this function gets the number of rows affected by an INSERT, UPDATE or DELETE statement
mysql_list_dbs() - fetch the list of databases Format: resource mysql_list_dbs([resource link_id]) Example: mysql_connect(“localhost”, “username”, “password”); $dbs = mysql_list_dbs(); echo “Databases:
“; while (list($db) = mysql_fetch_rows($dbs)) { echo “$db
“; } Note: displays all database namesmysql_db_name() - fetch a database name Format: string mysql_db_name(resource result_set, integer index) Note: this function gets the database name at the specified index in the result_set returned by mysql_list_dbs()
mysql_list_tables() - fetch the list of tables in a database Format: resource mysql_list_tables(string database [, resource link_id]) Example: mysql_connect(“localhost”, “username”, “password”); $tables = mysql_list_tables(“MyDatabase”); while (list($table) = mysql_fetch_row($tables)) { echo “$table
“; } Note: this function gets the names of all tables in database 17. mysql_tablename() - fetch a database table name Format: string mysql_tablename(resource result_set, integer index) Example: mysql_connect(“localhost”, “username”, “password”); $tables = mysql_list_tables(“MyDatabase”); $count = -1; while (++$count < mysql_numrows($tables)) { echo mysql_tablename($tables, $count).”
“; } Note: this function gets the table name at the specified index in the result_set returned by mysql_list_tables() 18. mysql_fetch_field() - fetch field information Format: object mysql_fetch_field(resource result [, int field_offset]) Example: mysql_connect(“localhost”, “username”, “password”); mysql_select_db(“MyDatabase”); $query = “select * from MyTable”;
