PHP4 and MySQL database operation functions

I can't help but mention MySQL in PHP, but MySQL, so PHP must also be mentioned. The rapid rise of PHP is inseparable from MySQL, and the wide application of MySQL is also related to PHP.

The following is a detailed analysis of MySQL-related functions in PHP4 (a total of 32 functions starting with mysql _):

<1>. database server connection functions (2 ):

(1). mysql_connect ()
Format: int mysql_connect (string [hostname] [ort], string [username], string [password]);

The port parameter in the parameter indicates the port number of the database server. You can use its default port number.
If no parameter is specified, the default hostname is localhost, username is root, and password is empty.

If the function is successfully executed, a connection number (link_identifier) of the int type is returned. If the function fails to be executed, a value of false is returned.

Example:


$ Connect = mysql_connect ("localhost", "user", "password ");
If ($ connect) echo "Connect Successed! "; // The connection is successful. Connect Successed is displayed!
Else echo "Connect Failed! "; // Connection Failed, show Connect Failed!



?>

In the preceding example, if mysql_connect () fails to be executed, a system error message is displayed and the execution continues. Then, how can we block the system error prompts and end the program after the failure?
In MySQL, the @ symbol can be added before the database function to block system error prompts. At the same time, the die () function is used to provide more understandable error prompts, and then die () the function automatically exits the program.

The preceding example can be changed:


$ Connect = @ mysql_connect ("localhost", "user", "password") or die ("Unable to connect database server! ");

?>

If mysql_connect () fails to be executed, Unable to connect database server will be displayed! And then exit the program.

(2). mysql_pconnect ()
Format: int mysql_pconnect (string [hostname] [ort], string [username], string [password]);
This function is basically the same as mysql_connect () in (1). The difference is:

--------- After the database operation is complete, the connection established by (1) mysql_connect () will be closed automatically, and the connection established by (2) mysql_pconnect () will continue to exist, is a stable and persistent connection.
--------- In mysql_pconnect () of (2), before each connection, the system checks whether the same hostname, use, and password connections are used. If yes, the connection number is used directly.
The Connection established by mysql_connect () of --------- (1) can be closed with mysql_close (), while mysql_pconnect () of (2) cannot be closed with mysql_close.


<2>. Disable the database connection function (1 ):

Mysql_close ()
Format: int mysql_close (int link_identifier );
Close the connection established by the mysql_connect () function. If the connection runs successfully, the true value is returned. If the connection fails, the false value is returned.

Example:

$ Connect = @ mysql_connect ("hostname", "user", "password") or die ("Unable to connect database server! ");

$ Close = @ mysql_close ($ connect) or die ("Unable to close database server connect! ");

?>

Note: mysql_close () cannot close the connection established by the mysql_pconnect () function.

<3>. Select database functions (1 ):

Mysql_select_db ()
Format: int mysql_select_db (string database name, int link_identifier );
Select the specified database name. If the database name is successful, one True value is returned. If the database name fails, one False value is returned.
Example 1:
$ Select = mysql_select_db (\ 'Forum \ ', $ connect );
If ($ select)
{Echo "connect db forum successed! ";}
Else
{Echo "connect db forum failed! ";}
?>

Example 2:
$ Select = mysql_select_db ("forum", $ connect) or die ("Can not connect this DB! ");
?>

Note: This function is equivalent to the USE statement in MySQL, for example, USE forum.

<4>. SQL query functions (2 ):

1. mysql_query ()
Format: int mysql_query (string sqlquery, int link_identifier );
Send a standard SQL statement request to the server. If it fails, a value of False is returned.

Example:
$ Connect = mysql_connect ($ hostname, $ user, $ pwd );
$ Select = mysql_select_db ($ dbname, $ connect );
$ Query = mysql_query ($ SQL, $ connect );
If ($ query) echo "Successed! ";
Else echo "Failed! ";
?>
This function must be used with the mysql_select_db () function. It makes no sense to use it independently!

2. mysql_db_query ()
Format: int mysql_db_query (string database, string sqlquery, int link_identifier );

In this function, you must specify the database Name and SQL statement sqlquery. If the query fails, False is returned.

Example:

$ Connect = mysql_connect ($ hostname, $ user, $ pwd );
$ Query = mysql_db_query ($ dbname, $ SQL, $ connect );
If ($ query) echo "Successed! ";
Else echo "Failed! ";
?>

The difference between mysql_db_query () and mysql_query () lies in that the former does not need to use mysql_select_db () to select the database, but selects the database while executing the SQL statement. <5>. Database record operation functions (5 ):

1. mysql_fetch_array ()
Format: array mysql_fetch_array (int query );

If the execution is successful, an array is returned. The array contains the value of the next record. If the execution fails, False is returned.
The returned array can be expressed by subscript or field name.

Example:
$ Query = mysql_query ($ SQL, $ connect );
While ($ arrary = mysql_fetch_array ($ query ))
{

Echo $ array [column1]. "|". $ array [column2];
// Echo $ array [0]. "|". $ array [1];

}
?>
Note: The subscript of the array starts from 0!

2. mysql_fetch_row ()
Format: array = mysql_fetch_row (int query );

The function is basically the same as the mysql_fetch_array () function of 1. The difference is that mysql_fetch_row () can only be expressed as an array subscript.
1 array is returned successfully, and False is returned if the operation fails.

Example:
$ Query = mysql_query ($ SQL, $ connect );
While ($ row = mysql_fetch_row ($ query ))
{
Echo $ row [0]. "|". $ row [1]."
";
}
?>
Note: The mysql_fetch_row () function can only be represented by an array subscript and start from 0.
In addition, mysql_fetch_row () is faster than mysql_fetch_array () and reads data from the next row.

3. mysql_result ()
Format: int mysql_result (int query, int row, string filedname );

In mysql_result (), the row parameter must start from 0, and the filedname parameter must be a real field name, which cannot be expressed by subscript.
If the execution is successful, the value of the Field Retrieved from the database is returned. If the execution fails, the value of False is returned.

Example:
$ Query = mysql_query ($ SQL, $ connect );
Echo mysql_result ($ query, 0, "column1 ")."
";
Echo mysql_result ($ query, 1, "column1 ")."
";
Echo mysql_result ($ query, 2, "column1 ")."
";
?>

Note: This function has few functions but is easy to use.

4. mysql_fetch_object ()
Format: object mysql_fetch_object (int query)

The specified field can be read cyclically. If the execution is successful, a value is returned in the form of an object. If the operation fails, a value of False is returned.

Example:
$ Query = mysql_query ($ SQL, $ connect );
While ($ object = mysql_fetch_object ($ query ))
{
Echo $ object-> column1 ."
";
Echo $ object-> column2 ."
";
Echo $ object-> column3 ."
";
}
?>


Note: After the mysql_fetch_object () function is successfully executed, one object is returned!
The procedure is as follows:
$ Object-> Field name

5. mysql_data_seek ()
Format: int mysql_data_seek (int row, int query );
Move the cursor to the specified row (row_number)
If the execution is successful, true value is returned. If the execution fails, False is returned.
This function can be used with mysql_fetch_array () or mysql_fetch_row (). After using the mysql_data_seek () function, you can use mysql_fetch_array () or mysql_fetch_row () function to display the specified row.

Example:
$ Query = mysql_query ($ SQL, $ connect );
$ Seek = mysql_data_seek ($ query, 2 );
$ Arrary = mysql_fetch_array ($ query );
Echo $ array [column1]."
";
Echo $ array [column2]."
";
?>

<6> database-level database operation functions (2 ):

1. mysql_create_db ()
Format: int mysql_create_db (string database name, int link_identifier );

You can use the mysql_query () or mysql_db_query () function to create or delete a database.

However, we can use this function to create a database more conveniently.
If one true value is returned successfully, if one fails, one false is returned.

Example:


$ Connect = mysql_connect ("$ hostname", "$ user", "$ pwd ");
$ Create = mysql_create_db ("dbtest", $ connect );
If ($ create) echo "create database dbtest successed! ";
Else echo "create database dbtest failed! ";

?>


2. mysql_drop_db ()
Format: int mysql_drop_db (string database name, int link_identifier );

You can use a program to delete one database.

However, we can use this function to delete one database more conveniently.
If one true value is returned successfully, if one fails, one false is returned.

Example:


$ Connect = mysql_connect ("$ hostname", "$ user", "$ pwd ");
$ Create = mysql_drop_db ("dbtest", $ connect );
If ($ create) echo "drop database dbtest successed! ";
Else echo "drop database dbtest failed! ";

?>

Note: If mysql_query () or mysql_db_query () is used, the SQL statement should be:
(1) create database dbtest
(2) drop database dbtest 7) database information functions (2 ):

1. mysql_fetch_field ()
Format: object mysql_fetch_field (int query, int [field_offset]);

Returns an object, that is, a hash table marked:
Table: table Name
Name: field name
Max_length: Maximum length of the field
Not_null: if the field is not null, 1 is returned; otherwise, 0 is returned.
Primary_key: if the field is primary key, 1 is returned; otherwise, 0 is returned.
Unique_key: if the field is unique key, 1 is returned; otherwise, 0 is returned.
Multiple_key: if the field is not a unique key, 1 is returned; otherwise, 0 is returned.
Numeric: if the field is numeric, 1 is returned; otherwise, 0 is returned.
Blob: if the field is blob, 1 is returned; otherwise, 0 is returned.
Type: Field type
Unsigned: if the field is unsigned, 1 is returned; otherwise, 0 is returned.
Zerofill: if the field is zero filled, 1 is returned; otherwise, 0 is returned.

Reference format: Object Name-> subscript name

You can use this function to obtain the table name, field name, and type .......

Example:

$ Query = mysql_query ($ SQL, $ connect );
While ($ object = mysql_fetch_field ($ query ))
{
Echo "table name:". $ object-> table ."
";
Echo "field name:". $ object-> name ."
";
Echo "primary key:". $ object-> primary_key ."
";
Echo "not null:". $ object-> not_null ."
";
Echo "field type:". $ object-> type ."
";
Echo "field max length:". $ object-> max_length ."
";
}
?>

Note: The hash table starts with 0 coordinates, that is, the first field is 0 items in the hash table.
If you want to directly obtain the information of the Third Field in the hash table, the format is as follows:
$ Query = mysql_query ($ SQL, $ connect );
$ Object = mysql_fetch_field ($ query, 2 );
Echo "table name:". $ object-> table ."
";
Echo "field name:". $ object-> name ."
";
Echo "primary key:". $ object-> primary_key ."
";
Echo "not null:". $ object-> not_null ."
";
Echo "field type:". $ object-> type ."
";
Echo "field max length:". $ object-> max_length ."
";
?>

In fact, this can also be achieved through the following function.

2. mysql_field_seek ()
Format: int mysql_field_seek (int $ query, int field_offset );

Move the cursor to the specified field.
Example:

$ Query = mysql_query ($ SQL, $ connect );
$ Seek = mysql_field_seek ($ query, 2 );
$ Object = mysql_fetch_field ($ query );
Echo "table name:". $ object-> table ."
";
Echo "field name:". $ object-> name ."
";
Echo "primary key:". $ object-> primary_key ."
";
Echo "not null:". $ object-> not_null ."
";
Echo "field type:". $ object-> type ."
";
Echo "field max length:". $ object-> max_length ."
";
?>

This also meets the same requirements as the above example.

8) Database Name and table name (2 ):

1. mysql_list_dbs ()
Format: int mysql_list_dbs (int link_identifier );
Obtain the names of all available databases ).

Example:

$ Connect = mysql_connect ($ host, $ usr, $ pwd );
$ Dbs = mysql_list_dbs ($ connect );
$ Rows = mysql_num_rows ($ dbs );
Echo "database total:". $ rows;
$ I = 0;
While ($ I <$ rows)
{
$ Db_name [$ I] = mysql_tablename ($ dbs, $ I );
Echo $ db_name [$ I];
$ I ++;
}
?>
The names of all databases in MySQL are displayed in sequence ).
Note: It is equivalent to the show databases command in MySQL.

2. mysql_list_tables ()
Format: int mysql_list_tables (string database name );
Displays the names of all tables in the database.

Example:

$ Connect = mysql_connect ($ host, $ usr, $ pwd );
$ Tables = mysql_list_tables ("mysql ");
$ Rows = mysql_num_rows ($ tables );
Echo "Table total:". $ rows;
$ I = 0;
While ($ I <$ rows)
{
$ Table_name [$ I] = mysql_tablename ($ tables, $ I );
Echo $ table_name [$ I];
$ I ++;
}

?>

The names of all tables under mysql are displayed in sequence.
Note: It is equivalent to the show tables command in MySQL (first use the use mysql command to select a database)
(Source: Flash)