Front-end learning PHP mysql extension function

×

Directory

[1]Connect to the database[2]Use the database[3]Execute SQL query[4]Operation result set[5]Close the connection

The previous words

　mysql due to its size Small, fast, low total cost of ownership, especially open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership. The database system solution that uses the mysql database management system and the php scripting language is being adopted by more and more websites. Among them, the LAMP (linux+apche+mysql+php) mode is the most popular

PHP has a standard Functions are used to operate the database. Mysqli is newly added in PHP5 and is an improvement on the mysql extension. However, due to historical issues, many old projects were developed using mysql extension in PHP4. If secondary development is carried out on the original project, the use of mysql extension functions is required. If it is a newly designed project, it is recommended to use mysqli expansion or PDO technology. This article mainly introduces the mysql extension function in PHP

Overview

Several steps to operate the MySQL database in the PHP script are as follows:

　1. Connect to the MySQL database server and determine whether the connection is correct

　2. Select the database, and Set the character set (optional)

　3. Execute SQL commands

　4. Process the result set

　5. Close the database connection

Step 1: Connect to the MySQL database server and determine whether the connection is correct

mysql_connect()

　The mysql_connect() function is used to open a connection to the MySQL server. Returns a resource if successful, or FALSE on failure

resource mysql_connect ([ string $server [, string $username [, string 
$password [, bool $new_link [, int $client_flags ]]]]] )

mysql_errno()

　　mysql_errno() function is used to return the numeric encoding of the error message in the previous MySQL operation

The mysql_error() function is used to return the text error message generated by the previous MySQL operation. If the connection resource number is not specified, the last successfully opened connection is used to extract error information from the MySQL server

int mysql_errno ([ resource $link_identifier ] )

string mysql_error ([ resource $link_identifier ] )

Step 2: Select the database and set the character set (optional)

Usually, the database creation work is first established by the database administrator (DBA), and then used by the PHP programmer in the script. For example, create a database named bookstore

After using a PHP script to establish a connection with the mysql server, in order to avoid specifying the target database every time the mysql extension function of PHP is called, it is best to use the mysql_select_db() function for subsequent The operation selects a default database. This function is similar to the SQL command "USE bookstore". Mysql_select_db() mysql_select_db() is used to select a MySQL database.

Steps Three: Execute SQL command

First, create a books data table in the bookstore database

<?php$link = mysql_connect(&#39;localhost&#39;,&#39;root&#39;,&#39;123456&#39;);
var_dump($link);//resource(3, mysql link)if(!$link){die(&#39;连接失败：&#39;.mysql_error());
}?>

In PHP, just pass the SQL command as a string to the mysql_query() function, It will be sent to the MYSQL server and executed. The mysql_query() function is used to send a MySQL query. mysql_query() only returns a resource for SELECT, SHOW, DESCRIBE, EXPLAIN and other statements. If there is an error in the query, it returns FALSE; for other types of SQL statements, such as INSERT, UPDATE, DELETE, DROP, etc., mysql_query() is executing Returns TRUE on success, FALSE on error

bool mysql_select_db ( string $database_name [, resource $ link_identifier ] )

Declares the three INSERT statements to be inserted as a string

<?php$link = mysql_connect(&#39;localhost&#39;,&#39;root&#39;,&#39;zhiaihebe0123&#39;);
var_dump($link);//resource(3, mysql link)if(!$link){die(&#39;连接失败：&#39;.mysql_error());
}mysql_select_db(&#39;bookstore&#39;,$link) or die(&#39;不能选定数据库bookstore:&#39; .mysql_error());
mysql_query(&#39;set names utf8&#39;);//设置字符集(一般不常用)?>

Use the mysql_query() function to send the INSERT statement, if successful Returns true, returns false on failure. The mysql_affected_rows() function is used to obtain the number of record rows affected by the previous MySQL operation. If the execution is successful, the number of affected rows is returned. If the latest query fails, the function returns -1

CREATE TABLE books(
    id INT NOT NULL AUTO_INCREMENT,bookname VARCHAR(80) NOT NULL DEFAULT '',publisher 
    VARCHAR(60) NOT NULL DEFAULT '',author VARCHAR(20) NOT NULL DEFAULT '',price
     DOUBLE(5,2) NOT NULL DEFAULT 0.00,ptime INT NOT NULL DEFAULT 0,
     pic CHAR(24) NOT NULL DEFAULT '',detail TEXT,PRIMARY KEY(id));
));

resource mysql_query ( string $query [, resource $link_identifier = NULL ] )

Usually by judging whether the value of the mysql_affected_rows() function is greater than 0 To determine whether the data operation is successful

mysql_insert_id()

　　mysql_insert_id()函数用来取得上一步 INSERT 操作产生的 ID

int mysql_insert_id ([ resource $link_identifier ] )

<? = "" = (( && () > 0 "数据记录插入成功，最后一条插入的数据记录id为:".()."
" "数据记录插入失败，错误号：".()."，错误原因：".()."
"?>

　　实际上，最后一个id应该为6，但是由于4、5、6三条语句是同时插入的，这时显示的是第一个id为4

　　下面，将id为4的记录的作者修改为小白

 = ("UPDATE books SET author='小白' WHERE id='4'"( && () > 0 "数据记录修改成功
" "数据记录修改失败，错误号：".()."，错误原因：".()."
"

　　下面，删除作者为李四的记录

 = ("DELETE FROM books WHERE author='李四'"( && () > 0 "数据记录删除成功
" "数据记录删除失败，错误号：".()."，错误原因：".()."
"

 

步骤四：处理结果集

　　在PHP脚本中执行SELECT查询命令，也是调用mysql_query()函数，但和执行DML不同的是，执行SELECT命令之后，mysql_query()函数的返回值是一个PHP资源的引用指针(结果集)。这个返回值可以在各种结果集处理函数中，对结果数据表的各个字段进行处理

mysql_num_fields()

　　mysql_num_fields()函数取得结果集中字段的数目

int mysql_num_fields ( resource $result )

mysql_num_rows()

　　mysql_num_rows()函数取得结果集中行的数目

int mysql_num_rows ( resource $result )

$result = mysql_query("SELECT * FROM books");
$rows = mysql_num_rows($result);$cols = mysql_num_fields($result);
var_dump($rows,$cols);//int 4 int 8

　　从结果中可以看出，该结果集总共有4行8列

 

　　如果需要访问结果集中的数据，可以选用mysql_fetch_row()、mysql_fetch_assoc()、mysql_fetch_array()、mysql_fetch_object()这4个函数中的任意一个

mysql_fetch_row()

　　mysql_fetch_row()函数从结果集中取得一行作为枚举数组

array mysql_fetch_row ( resource $result )

　　如果需要访问结果集中的数据，可以选用mysql_fetch_row()、mysql_fetch_assoc()、mysql_fetch_array()、mysql_fetch_object()这4个函数中的任意一个

mysql_fetch_row()

　　mysql_fetch_row()函数从结果集中取得一行作为枚举数组

array mysql_fetch_row ( resource $result )

$result = mysql_query("SELECT * FROM books");
$row = mysql_fetch_row($result);
//Array ( [0] => 1 [1] => PHP [2] 
=> 电子工业出版社 [3] => 张三 [4] => 80.00 [5] => 0 [6] => [7] => PHP相关 )
print_r($row);$row = mysql_fetch_row($result);
//Array ( [0] => 3 [1] => JSP [2] 
=> 电子工业出版社 [3] => 王五 [4] => 70.00 [5] => 0 [6] => [7] => JSP相关 )
print_r($row);

mysql_fetch_assoc()

　　mysql_fetch_assoc()函数从结果集中取得一行作为关联数组

array mysql_fetch_assoc ( resource $result )

$result = mysql_query("SELECT * FROM books");
$assoc = mysql_fetch_assoc($result);
//Array ( [id] => 1 [bookname] => PHP [publisher] => 电子工业出版社 [author] => 
张三 [price] => 80.00 [ptime] => 0 [pic] => [detail] => PHP相关 )
print_r($assoc);
$assoc = mysql_fetch_assoc($result);
//Array ( [id] => 3 [bookname] => JSP [publisher] => 电子工业出版社 [author] => 王五 [price] => 
70.00 [ptime] => 0 [pic] => [detail] => JSP相关 )
print_r($assoc);

mysql_fetch_array()

　　mysql_fetch_array()函数从结果集中取得一行作为关联数组，或数字数组，或二者兼有。mysql_fetch_array() 中可选的第二个参数 result_type 是一个常量，可以接受以下值：MYSQL_ASSOC，MYSQL_NUM 和 MYSQL_BOTH，默认值是 MYSQL_BOTH

array mysql_fetch_array ( resource $result [, int $ result_type ] )

$result = mysql_query("SELECT * FROM books");$array = mysql_fetch_array($result);
//Array ( [0] => 1 [id] => 1 [1] => PHP [bookname] => PHP [2] => 电子工业出版社 [publisher] => 
电子工业出版社 [3] => 张三 [author] => 张三 [4] => 80.00 [price] => 80.00 [5] => 0 
[ptime] => 0 [6] => [pic] => [7] => PHP相关 [detail] => PHP相关 )print_r($array);
$array = mysql_fetch_array($result);
// Array ( [0] => 3 [id] => 3 [1] => JSP [bookname] => JSP [2] => 电子工业出版社 [publisher] =>
 电子工业出版社 [3] => 王五 [author] => 王五 [4] => 70.00 [price] => 70.00 [5] => 0 
 [ptime] => 0 [6] => [pic] => [7] => JSP相关 [detail] => JSP相关 )print_r($array);

mysql_fetch_object()

　　mysql_fetch_object()函数从结果集中取得一行作为对象

object mysql_fetch_object ( resource $result )

$result = mysql_query("SELECT * FROM books");$object = mysql_fetch_object($result);
//stdClass Object ( [id] => 1 [bookname] => PHP [publisher] => 电子工业出版社 [author] 
=> 张三 [price] => 80.00 [ptime] => 0 [pic] => [detail] => PHP相关 )print_r($object);
$object = mysql_fetch_object($result);
//stdClass Object ( [id] => 3 [bookname] => JSP [publisher] => 电子工业出版社 [author] => 
王五 [price] => 70.00 [ptime] => 0 [pic] => [detail] => JSP相关 )
print_r($object);

　　对于上面的四个函数来说，默认指针都指向第一行记录。在获取一行记录后，指针会自动下移。如果是最后一委，则函数返回false。一般地，mysql_fetch_assoc()这种返回关联数组形式的函数较常用

mysql_data_seek()

　　mysql_data_seek()函数可以移动内部结果的指针

　　[注意]$row_number从0开始

bool mysql_data_seek ( resource $result , int $row_number )

$result = mysql_query("SELECT * FROM books");
$assoc = mysql_fetch_assoc($result);
mysql_data_seek($result , 2);$assoc = mysql_fetch_assoc($result);
Array ( [id] => 4 [bookname] => PHP [publisher] => 电子工业出版社 [author] => 小白[price]
 =>80.00 [ptime] => 0 [pic] => [detail] => PHP相关 )print_r($assoc);mysql_data_seek($result , 0);
$assoc = mysql_fetch_assoc($result);//Array ( [id] => 1 [bookname] => PHP [publisher] 
=> 电子工业出版社 [author] => 张三 [price] => 80.00 [ptime] => 0 [pic] => [detail] => PHP相关 )
print_r($assoc);

　　下面使用while循环和mysql_fetch_assoc()函数将结果集以表格的形式显示出来

table{
    border:1px solid black;
    border-collapse:collapse;
    table-layout:fixed;
}" _ue_custom_node_="true">$result = mysql_query("SELECT id,bookname,publisher,author,price FROM books");
echo '';echo '';echo '编号';echo '书名';echo '出版社';echo '作者';echo '价格';echo '';
while($assoc = mysql_fetch_assoc($result)) {echo '';
echo "{$assoc['id']}";echo "{$assoc['bookname']}";
echo "{$assoc['publisher']}";echo "{$assoc['author']}";
echo "{$assoc['price']}";echo '';
}echo '';

mysql_free_result()

　　mysql_free_result()函数用于释放结果内存

bool mysql_free_result ( resource $result )

　　mysql_free_result() 仅需要在考虑到返回很大的结果集时会占用多少内存时调用。在脚本结束后所有关联的内存都会被自动释放

 

步骤五：关闭数据库连接

mysql_close()

　　mysql_close()函数用于关闭 MySQL 连接

bool mysql_close ([ resource $link_identifier = NULL ] )

　　mysql_close() 关闭指定的连接标识所关联的到 MySQL 服务器的非持久连接。如果没有指定 link_identifier，则关闭上一个打开的连接

　　所以，一个比较完整的php操作数据库扩展函数的程序如下所示

<?php//连接数据库$link = mysql_connect(&#39;localhost&#39;,&#39;root&#39;,&#39;******&#39;);if(!$link){die(&#39;连接失败：&#39;.mysql_error());
}//选择数据库mysql_select_db(&#39;bookstore&#39;,$link) or die(&#39;不能选定数据库bookstore:&#39; .mysql_error());
//执行SQL命令$insert = "insert into books(bookname, publisher, author, price, detail) values
(&#39;PHP&#39;,&#39;电子工业出版社&#39;,&#39;张三&#39;,&#39;80.00&#39;,&#39;PHP相关&#39;),
(&#39;ASP&#39;,&#39;电子工业出版社&#39;,&#39;李四&#39;,&#39;90.00&#39;,&#39;ASP相关&#39;),
(&#39;JSP&#39;,&#39;电子工业出版社&#39;,&#39;王五&#39;,&#39;70.00&#39;,&#39;JSP相关&#39;)";
$result = mysql_query($insert);
//操作结果集$result = mysql_query("SELECT id,bookname,publisher,author,price FROM books");
echo &#39;&#39;;
echo &#39;&#39;;
echo &#39;编号&#39;;
echo &#39;书名&#39;;
echo &#39;出版社&#39;;
echo &#39;作者&#39;;
echo &#39;价格&#39;;
echo &#39;&#39;;
while($assoc = mysql_fetch_assoc($result)) {
echo &#39;&#39;;echo "{$assoc[&#39;id&#39;]}";echo "{$assoc[&#39;bookname&#39;]}";echo "{$assoc[&#39;publisher&#39;]}";
echo "{$assoc[&#39;author&#39;]}";echo "{$assoc[&#39;price&#39;]}";echo &#39;&#39;;
}echo &#39;&#39;;//释放结果集mysql_free_result($result);//关闭数据库连接mysql_close($link);?>

以上就是前端学PHP之mysql扩展函数的内容，更多相关内容请关注PHP中文网（www.php.cn）！