MYSQL common functions in PHP. php users must learn database operations. Below are some common functions. In combination, you can perform database operations.
MYSQL common functions in PHP. php users must learn database operations. Below are some common functions. In combination, you can perform database operations.
1. mysql_connect ()-create 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 ("cannot connect to Mysql Server ");
Note: Close the connection that must be displayed when this connection is used
2. mysql_pconnect ()-create 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 ("cannot connect to Mysql Server ");
Note: To use this connection function, you do not need to display the closed connection. It is equivalent to using the connection pool.
3. mysql_close ()-close the database connection
Example:
$ Conn = @ mysql_connect ("localhost", "username", "password") or die ("cannot connect to Mysql Server ");
@ Mysql_select_db ("MyDatabase") or die ("this database cannot be selected, or the database does not exist ");
Echo "you have connected to MyDatabase ";
Mysql_close ();
4. 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 ("cannot connect to Mysql Server ");
@ Mysql_select_db ("MyDatabase") or die ("this database cannot be selected, or the database does not exist ");
5. mysql_query ()-query MySQL
Format:
Resource mysql_query (string query, [resource link_id])
Example:
$ LinkId = @ mysql_connect ("localhost", "username", "password") or die ("cannot connect to Mysql Server ");
@ Mysql_select_db ("MyDatabase") or die ("this database cannot be selected, or the database does not exist ");
$ Query = "select * from MyTable ";
$ Result = mysql_query ($ query );
Mysql_close ();
Note: If the SQL query is executed successfully, the resource identifier is returned. If the query fails, FALSE is returned. If the update is successful, TRUE is returned; otherwise, FALSE is returned.
6. 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 ("cannot connect to MysqlServer ");
$ Query = "select * from MyTable ";
$ Result = mysql_db_query ("MyDatabase", $ query );
Mysql_close ();
Note: This function is not recommended for code clarity.
7. mysql_result ()-Get 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;
}
Notes: The simplest and most efficient Data Acquisition Function
8. mysql_fetch_row ()-Get 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 retrieves the entire data row from result_set and places the value in an index array. Usually, the list () function is used.
9. mysql_fetch_array ()-Get 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:
Result_type has the following values:
MYSQL_ASSOC: field name indicates the key, which is a U.S. server. The field content is a value.
MYSQL_NUM: value index array, Hong Kong server. The operation is the same as the mysql_fetch_ros () function.
MYSQL_BOTH: it is returned as an associated array and a numerical index array. The default value of result_type.
10. mysql_fetch_assoc ()-obtain and display data
Format:
Array mysql_fetch_assoc (resource result_set)
Call mysql_fetch_array (resource, MYSQL_ASSOC );
11. mysql_fetch_object ()-obtain 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)
";
}
Description: an object is returned, which is the same as mysql_fetch_array () in operation.
12. mysql_num_rows ()-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 "has". mysql_num_rows ($ result). "The Record ID is greater than 65 ";
Note: It is only useful when determining the number of records obtained by the select query.
13. mysql_affected_rows ()-number of records affected by Insert, update, and delete
Format:
Int mysql_affected_rows ([resource link_id])
Example:
$ Query = "update MyTable set where id> = 5 ";
$ Result = mysql_query ($ query );
Echo "number of records whose ID is greater than or equal to 5:". mysql_affected_rows ();
Description: This function obtains the number of rows affected by the INSERT, UPDATE, or delete update statement.
14. mysql_list_dbs ()-obtain database list information
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
";
}
Description: displays the names of all databases.
15. mysql_db_name ()-obtain the Database Name
Format:
String mysql_db_name (resource result_set, integer index)
Description: This function is used to obtain the database name in the specified index in result_set returned by mysql_list_dbs ().
16. mysql_list_tables ()-obtain the database table list
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
";
}
Description: This function obtains the names of all tables in the database.
17. mysql_tablename ()-obtain the name of a database table
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 )."
";
}
Description: This function obtains the name of the table in the specified index in result_set returned by mysql_list_tables ().
18. mysql_fetch_field ()-obtain 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 ";
$ Result = mysql_query ($ query );
$ Counts = mysql_num_fields ($ result );
For ($ count = 0; $ count <$ counts; $ count ++ ){
$ Field = mysql_fetch_field ($ result, $ count );
Echo"
$ Field-> name $ field-> type ($ field-> max_length)
";
}
Note:
The returned object has 12 object attributes:
Name: field name
Table: The table where the field is located.
Max_length: Maximum length of a field
Not_null: if the field cannot be null, it is 1; otherwise, 0
Primary_key: if the field is a primary key, it is 1; otherwise, 0
Unique_key: if the field is a unique key, it is 1, otherwise 0
Multiple_key: if the field is not unique, it is 1; otherwise, 0
Numeric: if the field is a numerical value, it is 1; otherwise, it is 0.
Blob: if the field is BLOB, it is 1; otherwise, it is 0.
Type: Data type of the field
Unsigned: if the field is unsigned, it is 1, virtual host, otherwise it is 0
Zerofill: if the field is "zero fill", it is 1; otherwise, it is 0.
19. mysql_num_fields ()-obtain the number of queried Fields
Format:
Integer mysql_num_fields (resource result_set)
Example:
$ Query = "select id, name from MyTable order by name ";
$ Result = mysql_query ($ query );
Echo "the number of fields in this query is:". mysql_num_fields ($ result )."
";
20. mysql_list_fields ()-obtains the field names of all fields in the specified table.
Format:
Resource mysql_list_fields (string database_name, string table_name [, resource link_id])
Example:
$ Fields = mysql_list_fields ("MyDatabase", "MyTable ");
Echo "Number of MyTable fields in the database MyDatabase:". mysql_num_fields ($ fields )."
";
21. mysql_field_flags ()-obtain the specified field options
Format:
String mysql_field_flags (resource result_set, integer field_offset)
Example:
$ Query = "select id, name from MyTable order by name ";
$ Result = mysql_query ($ query );
$ Row = mysql_fetch_wor ($ row );
22. mysql_field_len ()-obtain the maximum length of the specified field
Format:
Integer mysql_field_len (resource result_set, integer field_offset)
Example:
$ Query = "select name from MyTable ";
$ Result = mysql_query ($ query );
$ Row = mysql_fetch_row ($ result );
Echo mysql_field_len ($ result, 0 )."
";
Note:
If mysql_field_len ($ reseult, 0) = 16777215
Then numer_format (mysql_field_len ($ result) is 16,777,215
23. mysql_field_name ()-obtain the field name
Format:
String mysql_field_name (resource result_set, int field_offset)
Example:
$ Query = "select id as PKID, name from MyTable order by name ";
$ Result = mysql_query ($ query );
$ Row = mysql_fetch_row ($ result );
Echo mysql_field_name ($ result, 0); // Result: PKID
24. mysql_field_type ()-obtain the field type
Format:
String mysql_field_type (resource result_set, int field_offset)
Example:
$ Query = "select id, name from MyTable order by name ";
$ Result = mysql_query ($ query );
$ Row = mysql_fetch_row ($ result );
Echo mysql_field_type ($ result, 0); // Result: int
25. mysql_field_table ()-obtain the name of the table where the field is located
Format:
String mysql_field_table (resource result_set, int field_offset)
Example:
$ Query = "select id as PKID, name from MyTable order by name ";
$ Result = mysql_query ($ query );
$ Row = mysql_fetch_row ($ result );
Echo mysql_field_table ($ result, 0); // Result: MyTable
