Database operation method functions commonly used in PHP development

Today I summarized and analyzed the database tutorial operation method functions commonly used in PHP tutorial development. I hope that interested friends can refer to it.
1. Database operation
1. Connect MYSQL data
mysql tutorial_connect()

Tips and Notes
Note: As soon as the script ends, the connection to the server is closed unless it has been explicitly closed previously by calling mysql_close().
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 database
mysql_select_db()
After connecting to the database, the database selected by PHP by default may not be the database we need in subsequent operations. To ensure that the database is selected correctly, a database selection statement is generally added after the database connection statement.

The mysql_select_db() function sets the active MySQL database.
If successful, the function returns true. If failed, returns false.
Grammar
mysql_select_db(database,connection)
Parameter Description
database required. Specifies the database to be selected.
connection is optional. Specifies the MySQL connection. If not specified, the previous connection is used.

e.g.
mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));
3. Execute SQL statement
mysql_query()
This function sends a SQL statement to the currently active database and executes the statement, returning the result.

Definition and usage
The mysql_query() function executes a MySQL query.
Grammar
mysql_query(query,connection)
Parameter Description
query 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 there is no open connection, this function will try to call the mysql_connect() function without parameters to establish a connection and use it.
Return value
mysql_query() returns a resource identifier only for SELECT, SHOW, EXPLAIN, or DESCRIBE statements, or FALSE if the query was executed incorrectly.
For other types of SQL statements, mysql_query() returns TRUE on success and FALSE on error.
A non-FALSE return value means the query is valid and can be executed by the server. This does not say anything about the number of rows affected or returned. It's possible that a query executed successfully but did not affect or return any rows.

e.g.

$query = “SELECT * FROM $table”
$result = mysql_query($query, $db) or die(mysql_error($db));

4. Close the database
mysql_close()
This function is used to close databases that no longer need to be active, but this method is not necessary. Generally, PHP will automatically close databases that are no longer active.
e.g.
mysql_close($db);
5. Release SQL results
mysql_free_result()
This function is used to release the memory occupied by the execution result of mysql_query(). This function is rarely called unless the result is very large and takes up too much memory; usually the occupied memory is automatically released after the PHP script is executed.
e.g
The ysql_free_result() function frees result memory.
Returns true if successful, false if failed.
Grammar
mysql_free_result(data)
Parameter Description
data required. The result identifier to be freed. The result identifier is the result returned from mysql_query().
Tips and Notes
Note: mysql_free_result() only needs to be called when considering how much memory will be used when returning a very large result set. All associated memory will be automatically released after the script ends.
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 memory

mysql_free_result($result);

$sql = "SELECT * from Customers";
$result = mysql_query($sql,$con);
print_r(mysql_fetch_row($result));

mysql_close($con);
?>

2. SQL execution result operation
1. Return a row in the execution result
mysql_fetch_row()
Returns the numerical array of the current row of the execution result. After executing this function, 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 line
e.g.
while($row = mysql_fetch_row($result))
{……}
2. Alternatives to mysql_fetch_row()
mysql_fetch_array()
mysql_fetch_assoc()
mysql_fetch_array() returns an array of key-value pairs, where the key is the column name of the queried table;
When mysql_fetch_assoc() returns the results, you can sort them first (if you assign a value to the optional parameter), 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 obtains column information from the result set and returns it as an object.
mysql_fetch_field() can be used to obtain field information from query results. If no field offset is specified, the next field that has not been fetched by mysql_fetch_field() is fetched.
This function returns an object containing field information.
The properties of the returned object are:
name - column name
table - the name of the table where the column is located
max_length - the maximum length of the column
not_null - 1 if the column cannot be NULL
primary_key - 1 if the column is 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 if the column is a BLOB
type - the type of the column
unsigned - 1 if the column is an unsigned number
zerofill - 1 if the column is zero-filled
Grammar
mysql_fetch_field(data,field_offset)
Parameter Description
data required. The data pointer to use. The data pointer is the result returned from mysql_query().
field_offset required. Specifies which field to start from. 0 indicates the first field. If not set, the next field is retrieved.
Tips and Notes
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
If we want to display all tables, we will use the mysql_list_tables usage syntax to operate,
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 tables:";
while ($row = mysql_fetch_row($result))
{
echo $row[0];
}

5. Query the column name (field name) of the database

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

mysql_list_fields() gets information about the given table name. The parameters are the database name and table name. Returns a result pointer that can be used with 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);

3. Other functions
1. mysql_num_rows()

The mysql_num_rows() function returns the number of rows in the result set.
Grammar
mysql_num_rows(data)
Parameter Description
data required. Result set. This result set is obtained from a call to mysql_query().
Description
mysql_num_rows() returns the number of rows in the result set. This command is only valid for SELECT statements. To get the number of rows affected by an INSERT, UPDATE, or DELETE query, use mysql_affected_rows().
Returns the number of rows of execution results.
e.g.

$num = mysql_num_rows($result);

2. mysql_num_fields()
Returns the number of columns (number of fields) of the execution result.
e.g. $num = mysql_num_fields($result);
3.mysql_set_charset()
Set the encoding of execution results to prevent garbled characters when displaying Chinese characters on web pages.
e.g.