Common PHP database operations (MYSQL version)

  • 2020-05-07 19:22:29
  • OfStack

1. Database operation
1. Connect MYSQL data
mysql_connect()
e.g.

 
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or die( ' Unable to connect, please check connection paremeters'); 

2. Select a database
mysql_select_db()
After connecting to the database, the database selected by PHP by default may not be the database we need in the following operation. To ensure that the database is selected correctly, 1.
e.g.
 
mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db)); 

3. Execute the SQL statement
mysql_query()
This function sends the SQL statement to the currently active database and executes the statement, returning the result.
e.g.
 
$query =  " SELECT * FROM $table "  
$result = mysql_query($query, $db) or die(mysql_error($db)); 

Close the database
mysql_close()
This function is used to close a database that does not need to be active, but this method is not required; 1 general PHP automatically closes a database that is not active.
e.g.
mysql_close($db);
5. Release SQL results
mysql_free_result()
This function is used to free up the memory occupied by the execution result of mysql_query(). This function is rarely called unless result is too large and takes up too much memory. 1 normally frees up memory after the PHP script has finished executing.
2. SQL performs the resulting operation
1. Returns 1 row in the execution result
mysql_fetch_row()
Returns an array of values for the current row of the execution result, which, when executed, points to the next row.
e.g.
$row = mysql_fetch_row($result);
The processing execution result 1 is normally placed in the 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 an array of key-value pairs, the key being the column name of table of the query;
mysql_fetch_assoc() returns a result that can be sorted first (if assigned to an optional parameter), equivalent to mysql_fetch_array()+MYSQL_ASSOC
3. Field (column) properties of the execution result
mysql_fetch_field()
4. Query the table name in the database
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 column name of the database (field name)
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()
Returns the number of rows that execute the result.
e.g.
$num = mysql_num_rows($result);
2. mysql_num_fields()
Returns the number of columns (number of fields) for the execution result.
e.g. $num = mysql_num_fields($result);
3.mysql_set_charset()
Set the encoding of the execution result to prevent garbled code when displaying Chinese in the web page.
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 uppercase code in the text is predefined content, such as define(MYSQL_HOST, 'localhost');
2. This article only summarizes the main functions of PHP operation database. For the complete contents, please refer to the relevant contents of PHP manual.


Related articles: