Database operation methods commonly used in php development function _ PHP Tutorial

Database operation methods and functions commonly used in php development. Today, I will summarize and analyze the database tutorial operation methods and functions commonly used in php Tutorial development. if you are interested, please refer to them. I. database operations 1. connect to MYSQ. today, I will summarize and analyze the database tutorial operation methods and functions commonly used in php Tutorial development. if you are interested, please refer to them.
I. database operations
1. connect to MYSQL data
Mysql tutorial _ connect ()

Tips and comments
Note: When the script ends, the connection to the server is closed, unless mysql_close () has been explicitly called before.
Tip: to create a persistent connection, use the mysql_pconnect () function.
Example

$ Con = mysql_connect ("localhost", "mysql_user", "mysql_pwd ");
If (! $ Con)
{
Die ('could not connect: '. mysql_error ());
}

// Some code...

Mysql_close ($ con );
?>

E.g.

$ Db = mysql_connect (MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or die ('www.zhutiai.com Unable to connect, please check connection paremeters ');

2. select a database
Mysql_select_db ()
After connecting to the database, the default database selected by PHP may not be the database we need in subsequent operations. to ensure that the database is correctly selected, the database selection statement is usually added after the database connection statement.

The mysql_select_db () function sets the active MySQL database.
If yes, the function returns true. If it fails, false is returned.
Syntax
Mysql_select_db (database, connection)
Parameter description
Database is required. Specifies the database to be selected.
Connection is optional. MySQL connection is required. If not specified, the previous connection is used.

E.g.
Mysql_select_db (MYSQL_DB, $ db) or die (mysql_error ($ db ));
3. execute SQL statements
Mysql_query ()
This function sends the SQL statement to the active database and executes the statement to return the result.

Definition and usage
The mysql_query () function executes a MySQL Query.
Syntax
Mysql_query (query, connection)
Parameter description
Query is required. Specifies the SQL query to be sent. Note: the query string should not end with a semicolon.
Connection is optional. Specifies the SQL connection identifier. If not specified, the last opened connection is used.
Description
If no connection is enabled, this function will try to call the mysql_connect () function without parameters to establish a connection and use it.
Return value
Mysql_query () only returns a resource identifier for SELECT, SHOW, EXPLAIN or DESCRIBE statements. if the query execution is incorrect, FALSE is returned.
For other types of SQL statements, if mysql_query () is executed successfully, TRUE is returned. If an error occurs, FALSE is returned.
If the return value is not FALSE, the query is legal and can be executed by the server. This does not indicate any affected or returned number of rows. It is very likely that a query is successfully executed but does not affect or no rows are returned.

E.g.

$ Query = "SELECT * FROM $ table"
$ Result = mysql_query ($ query, $ db) or die (mysql_error ($ db ));

4. shut down the database
Mysql_close ()
This function is used to shut down the database that does not need to continue to be active, but this method is not required. generally, PHP automatically closes the database that does not continue to be active.
E.g.
Mysql_close ($ db );
5. release SQL results
Mysql_free_result ()
This function is used to release the memory occupied by mysql_query () execution results. This function is rarely called unless the result is large and occupies too much memory; generally, the occupied memory is automatically released after the PHP script is executed.
E. g
The ysql_free_result () function releases the result memory.
If the operation succeeds, true is returned. if the operation fails, false is returned.
Syntax
Mysql_free_result (data)
Parameter description
Data is required. The identifier of the result to be released. The result identifier is the result returned from mysql_query.
Tips and comments
Note: mysql_free_result () is called only when considering how much memory will be occupied when a large result set is returned. After the script is completed, all associated memory will be automatically released.
Example

$ Con = mysql_connect ("localhost", "peter", "abc123 ");
If (! $ Con)
{
Die ('could not connect: '. mysql_error ());
}

$ Db_selected = mysql_select_db ("test_db", $ con );

$ SQL = "SELECT * from Person ";
$ Result = mysql_query ($ SQL, $ con );
Print_r (mysql_fetch_row ($ result ));

// Release the memory

Mysql_free_result ($ result );

$ SQL = "SELECT * from MERS ";
$ Result = mysql_query ($ SQL, $ con );
Print_r (mysql_fetch_row ($ result ));

Mysql_close ($ con );
?>

II. SQL execution result operations
1. return a row in the execution result
Mysql_fetch_row ()
Returns the number array of the current row of the execution result. after this function is executed, the result points to the next row.
E.g.
$ Row = mysql_fetch_row ($ result );
Processing execution results are generally placed in a while loop, traversing each row
E.g.
While ($ row = mysql_fetch_row ($ result ))
{......}
2. alternative to mysql_fetch_row ()
Mysql_fetch_array ()
Mysql_fetch_assoc ()
Mysql_fetch_array () returns the key-value pair array. The key is the column name of the queried table;
Mysql_fetch_assoc () can be sorted first when the returned results are returned (if an optional parameter value is assigned), which is equivalent to mysql_fetch_array () + MYSQL_ASSOC
3. Field (column) attributes of execution results
Mysql_fetch_field ()
Eg.
The mysql_fetch_field () function retrieves column information from the result set and returns it as an object.
Mysql_fetch_field () can be used to obtain field information from the query results. If no field offset is specified, the next field that has not been obtained by mysql_fetch_field () is extracted.
This function returns an object containing field information.
The property of the returned object is:
Name-column name
Table-name of the table where the column is located
Max_length-maximum length of the column
Not_null-1, if this column cannot be NULL
Primary_key-1, if this column is primary key
Unique_key-1. if this column is a unique key
Multiple_key-1, if this column is non-unique key
Numeric-1, if this column is numeric
Blob-1, if this column is BLOB
Type-type of the column
Unsigned-1. if this column is an unsigned number
Zerofill-1, if this column is zero-filled
Syntax
Mysql_fetch_field (data, field_offset)
Parameter description
Data is required. The data pointer to use. The data pointer is the result returned from mysql_query.
Field_offset is required. Specifies which field to start. 0 indicates the first field. If not set, the next field is retrieved.
Tips and comments
Note: The field names returned by this function are case sensitive.
Example

$ Con= mysql_connect ("localhost", "hello", "321 ");
If (! $ Con)
{
Die ('could not connect: '. mysql_error ());
}

$ Db_selected = mysql_select_db ("test_db", $ con );

$ SQL = "SELECT * from Person ";
$ Result = mysql_query ($ SQL, $ con );

While ($ property = mysql_fetch_field ($ result ))
{
Echo "Field name:". $ property-> name ."
";
Echo "Table name:". $ property-> table ."
";
Echo "Default value:". $ property-> def ."
";
Echo "Max length:". $ property-> max_length ."
";
Echo "Not NULL:". $ property-> not_null ."
";
Echo "Primary Key:". $ property-> primary_key ."
";
Echo "Unique Key:". $ property-> unique_key ."
";
Echo "Mutliple Key:". $ property-> multiple_key ."
";
Echo "Numeric Field:". $ property-> numeric ."
";
Echo "BLOB:". $ property-> blob ."
";
Echo "Field Type:". $ property-> type ."
";
Echo "Unsigned:". $ property-> unsigned ."
";
Echo "Zero-filled:". $ property-> zerofill ."

";
}

Mysql_close ($ con );
?>

4. query the table name in the database
The mysql_list_tables syntax is used to display all tables,
Mysql_list_tables syntax

Mysql_list_tables ()
E.g.
$ Db_name = MYSQL_DB;
$ Result = mysql_list_tables ($ db_name );
Echo "the database contains the following table :";
While ($ row = mysql_fetch_row ($ result ))
{
Echo $ row [0];
}

5. query the database column name (field name)

Esource mysql_list_fields (string database_name, string table_name [, resource link_identifier])

Mysql_list_fields () obtains the information of the given table name. The parameter is the database name and table name. Returns a result pointer for mysql_field_flags (), mysql_field_len (), mysql_field_name (), and mysql_field_ty.

Mysql_list_fields ()
E.g.
$ Fields = mysql_list_fields ($ db_name, $ table );
$ Columns = mysql_num_fields ($ fields );
For ($ I = 0; $ I <$ columns; $ I ++)
Echo mysql_field_name ($ fields, $ I );

III. Other functions
1. mysql_num_rows ()

The number of rows in the returned result set by the mysql_num_rows () function.
Syntax
Mysql_num_rows (data)
Parameter description
Data is required. Result set. This result set is obtained from the call of mysql_query.
Description
Mysql_num_rows () returns the number of rows in the result set. This command is only valid for SELECT statements. To obtain the number of rows affected by INSERT, UPDATE, or DELETE queries, use mysql_affected_rows ().
Number of rows returned for execution results.
E.g.

$ Num = mysql_num_rows ($ result );

2. mysql_num_fields ()
Number of columns (number of fields) returned for execution results ).
E.g. $ num = mysql_num_fields ($ result );
3. mysql_set_charset ()
Sets the encoding of execution results to prevent garbled characters when Chinese characters are displayed on the webpage.
E.g.

$ Query = "select * from $ table_name ";
Mysql_query ('set names utf8 ′);
$ Result = mysql_query ($ query, $ db) or die (mysql_error ($ db ));

Note:
1. the capital code in the text is pre-defined content, such as define (MYSQL_HOST, 'localhost ');

Bytes. I. database operations 1. connect to MYSQ...