I When talking about PHP, you must not mention MySQL. If you want to talk about MySQL, then PHP will inevitably be mentioned. The rapid rise of PHP is inseparable from MySQL, and the widespread application of MySQL is also closely related to PHP.
The following is a detailed analysis of functions related to MySQL in PHP4 (32 in total, all of which are mysql_ starting with):
<1>. 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, and it is generally only possible to use its default port number.
If no parameters are filled in, the default hostname is localhost, username is root, and password is empty.
The function is executed successfully and returns a connection number of type int (link_identifier). The execution fails and returns a false value.
example:
$connect = mysql_connect("localhost","user","password");
if($connect) echo "Connect Successed!"; //The connection is successful, and the Connect Successed is displayed!
else echo "Connect Failed!"; //Connect failed, display Connect Failed!
?>
In the above example, if mysql_connect() fails to execute, the system error message will be displayed, and then the execution will continue. So, how do you block error messages from these systems and end the program after failure?
In MySQL, it is allowed to add @ symbols before the database function to block the system's error prompts, and use the die() function to give more understandable error prompts, and then the die() function will automatically exit the program.
The above example can be changed to:
$connect = @mysql_connect("localhost","user","password") or die ("Unable to connect database server!");
?>
If the execution of mysql_connect() fails, the program will be exited after the Unable to connect database server! is displayed.
(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 completed, the connection established by mysql_connect() in (1) will be automatically closed, while the connection established by mysql_pconnect() in (2) will continue to exist, which is a stable and persistent connection.
---------- In mysql_pconnect() in (2), before each connection, it will check whether there is a connection using the same hostname, use, and password. If so, use this connection number directly.
----------------- Connection established by mysql_connect() in (1) can be closed with mysql_close(), while (2) mysql_pconnect() cannot be closed with mysql_close().
<2>.Close the database connection function (1):
mysql_close()
Format: int mysql_close(int link_identifier);
Close the connection established by the mysql_connect() function. After successful execution, the ture value is returned, and if the failure is returned, the false value is returned.
Examples are as follows:
$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 function (1):
mysql_select_db()
Format: int mysql_select_db(string database name, int link_identifier);
Select the specified database name, succeed, return 1 true value (True), and fail, return 1 False value
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 a USE statement in MySQL: such as 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 False value 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 in conjunction with the mysql_select_db() function, and it will be meaningless to use it alone!
2、mysql_db_query()
Format: int mysql_db_query(string database, string sqlquery, int link_identifier);
In this function, the database name database and the SQL statement sqlquery must be specified, and if it fails, it will return False.
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() is that the former does not need to use mysql_select_db() to select the database database, but while executing SQL statements, select the database. <5>. Database record operation functions (5):
1、mysql_fetch_array()
Format: array mysql_fetch_array(int query);
If the execution is successful, 1 array is returned. The array holds the value of the next record. If the execution fails, the False value will be returned.
The returned array can be represented by subscripts or field names.
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 of mysql_fetch_array() is basically the same as that of 1. The difference is that mysql_fetch_row() can only be represented by array subscripts.
1 array is returned successfully, and False value is returned if it 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 array subscripts and starts from 0.
In addition: mysql_fetch_row() executes faster than mysql_fetch_array(), and they all read the next row of data.
3、mysql_result()
Format: int mysql_result(int query, int row, string filedname);
In mysql_result(), the parameter row must start from 0, and the parameter filedname must be the real field name and cannot be represented by a subscript.
If the execution is successful, the value of the field retrieved from the database is returned, and if the failure is, the value of False will be 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 loopfully. If the execution is successful, the value will be returned in the form of an object object, and if it fails, the value will be returned in the False value.
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, it returns 1 object object!
The operation 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)
Execution is successful, returns the true value, fails, returns the False value.
This function can be used in conjunction with mysql_fetch_array() or mysql_fetch_row(), that is, after using the mysql_data_seek() function, you can use the 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);
Create a database database through a program. Of course, you can also use the mysql_query() or mysql_db_query() function to create or delete database
But we can use this function to create a database more conveniently.
If 1 truth value is successfully returned, 1 false will be 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);
Delete 1 database database through the program.
But we can use this function to delete 1 database more conveniently.
If 1 truth value is successfully returned, 1 false will be 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 1 object, that is, a hash table, with the subscript:
table: table name
name: field name
max_length: The maximum length of this field
not_null: If the field is not null, return 1, otherwise return 0
primary_key: If the field is primary key, it will return 1, otherwise it will return 0
unique_key: If the field is unique key, it will return 1, otherwise it will return 0
multiple_key: If the field is not unique key, it will return 1, otherwise it will return 0.
numeric: If the field is numeric, return 1, otherwise return 0
blob: If the field is blob, it will return 1, otherwise it will return 0
type: field type
unsigned: If the field is unsigned, it will return 1, otherwise it will return 0.
zerofill: If the field is zero filled, it will return 1, otherwise it will return 0
The reference format is: object name->subscript name
Use this function to get table name, field name, 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 we want to directly obtain the information of the third item of the hash table, that is, the third field, it can be used in the following format:
$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 in the above example.
8) Take the database name and table name (2):
1、mysql_list_dbs()
Format: int mysql_list_dbs(int link_identifier);
Get all available database names (database names).
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++;
}
?>
All database names in MySQL can be displayed in sequence.
Note: 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 under this database Table name.
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++;
}
?>
You can display the names of all tables under mysql in sequence.
Note: It is equivalent to the show tables command in MySQL (first use the use mysql command to select 1 database)
(Source: Fengshuang)