×

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;連接失?。?amp;#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;連接失?。?amp;#39;.mysql_error());
}mysql_select_db(&#39;bookstore&#39;,$link) or die(&#39;不能選定數(shù)據(jù)庫bookstore:&#39; .mysql_error());
mysql_query(&#39;set names utf8&#39;);//設(shè)置字符集(一般不常用)?>

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()函數(shù)用來取得上一步 INSERT 操作產(chǎn)生的 ID

int?mysql_insert_id?([?resource?$link_identifier?]?)

<? = "" = (( && () >?0?"數(shù)據(jù)記錄插入成功，最后一條插入的數(shù)據(jù)記錄id為:".()."
"?"數(shù)據(jù)記錄插入失敗，錯誤號：".()."，錯誤原因：".()."
"?>

　　實際上，最后一個id應(yīng)該為6，但是由于4、5、6三條語句是同時插入的，這時顯示的是第一個id為4

　　下面，將id為4的記錄的作者修改為小白

?=?("UPDATE?books?SET?author='小白'?WHERE?id='4'"(?&&?()?>?0?"數(shù)據(jù)記錄修改成功
"?"數(shù)據(jù)記錄修改失敗，錯誤號：".()."，錯誤原因：".()."
"

　　下面，刪除作者為李四的記錄

?=?("DELETE?FROM?books?WHERE?author='李四'"(?&&?()?>?0?"數(shù)據(jù)記錄刪除成功
"?"數(shù)據(jù)記錄刪除失敗，錯誤號：".()."，錯誤原因：".()."
"

?

步驟四：處理結(jié)果集

　　在PHP腳本中執(zhí)行SELECT查詢命令，也是調(diào)用mysql_query()函數(shù)，但和執(zhí)行DML不同的是，執(zhí)行SELECT命令之后，mysql_query()函數(shù)的返回值是一個PHP資源的引用指針(結(jié)果集)。這個返回值可以在各種結(jié)果集處理函數(shù)中，對結(jié)果數(shù)據(jù)表的各個字段進(jìn)行處理

mysql_num_fields()

　　mysql_num_fields()函數(shù)取得結(jié)果集中字段的數(shù)目

int?mysql_num_fields?(?resource?$result?)

mysql_num_rows()

　　mysql_num_rows()函數(shù)取得結(jié)果集中行的數(shù)目

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

　　從結(jié)果中可以看出，該結(jié)果集總共有4行8列

?

　　如果需要訪問結(jié)果集中的數(shù)據(jù)，可以選用mysql_fetch_row()、mysql_fetch_assoc()、mysql_fetch_array()、mysql_fetch_object()這4個函數(shù)中的任意一個

mysql_fetch_row()

　　mysql_fetch_row()函數(shù)從結(jié)果集中取得一行作為枚舉數(shù)組

array?mysql_fetch_row?(?resource?$result?)

　　如果需要訪問結(jié)果集中的數(shù)據(jù)，可以選用mysql_fetch_row()、mysql_fetch_assoc()、mysql_fetch_array()、mysql_fetch_object()這4個函數(shù)中的任意一個

mysql_fetch_row()

　　mysql_fetch_row()函數(shù)從結(jié)果集中取得一行作為枚舉數(shù)組

array?mysql_fetch_row?(?resource?$result?)

$result?=?mysql_query("SELECT?*?FROM?books");
$row?=?mysql_fetch_row($result);
//Array?(?[0]?=>?1?[1]?=>?PHP?[2]?
=>?電子工業(yè)出版社?[3]?=>?張三?[4]?=>?80.00?[5]?=>?0?[6]?=>?[7]?=>?PHP相關(guān)?)
print_r($row);$row?=?mysql_fetch_row($result);
//Array?(?[0]?=>?3?[1]?=>?JSP?[2]?
=>?電子工業(yè)出版社?[3]?=>?王五?[4]?=>?70.00?[5]?=>?0?[6]?=>?[7]?=>?JSP相關(guān)?)
print_r($row);

mysql_fetch_assoc()

　　mysql_fetch_assoc()函數(shù)從結(jié)果集中取得一行作為關(guān)聯(lián)數(shù)組

array?mysql_fetch_assoc?(?resource?$result?)

$result?=?mysql_query("SELECT?*?FROM?books");
$assoc?=?mysql_fetch_assoc($result);
//Array?(?[id]?=>?1?[bookname]?=>?PHP?[publisher]?=>?電子工業(yè)出版社?[author]?=>?
張三?[price]?=>?80.00?[ptime]?=>?0?[pic]?=>?[detail]?=>?PHP相關(guān)?)
print_r($assoc);
$assoc?=?mysql_fetch_assoc($result);
//Array?(?[id]?=>?3?[bookname]?=>?JSP?[publisher]?=>?電子工業(yè)出版社?[author]?=>?王五?[price]?=>?
70.00?[ptime]?=>?0?[pic]?=>?[detail]?=>?JSP相關(guān)?)
print_r($assoc);

mysql_fetch_array()

　　mysql_fetch_array()函數(shù)從結(jié)果集中取得一行作為關(guān)聯(lián)數(shù)組，或數(shù)字?jǐn)?shù)組，或二者兼有。mysql_fetch_array() 中可選的第二個參數(shù) result_type 是一個常量，可以接受以下值：MYSQL_ASSOC，MYSQL_NUM 和 MYSQL_BOTH，默認(rèn)值是 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]?=>?電子工業(yè)出版社?[publisher]?=>?
電子工業(yè)出版社?[3]?=>?張三?[author]?=>?張三?[4]?=>?80.00?[price]?=>?80.00?[5]?=>?0?
[ptime]?=>?0?[6]?=>?[pic]?=>?[7]?=>?PHP相關(guān)?[detail]?=>?PHP相關(guān)?)print_r($array);
$array?=?mysql_fetch_array($result);
//?Array?(?[0]?=>?3?[id]?=>?3?[1]?=>?JSP?[bookname]?=>?JSP?[2]?=>?電子工業(yè)出版社?[publisher]?=>
?電子工業(yè)出版社?[3]?=>?王五?[author]?=>?王五?[4]?=>?70.00?[price]?=>?70.00?[5]?=>?0?
?[ptime]?=>?0?[6]?=>?[pic]?=>?[7]?=>?JSP相關(guān)?[detail]?=>?JSP相關(guān)?)print_r($array);

mysql_fetch_object()

　　mysql_fetch_object()函數(shù)從結(jié)果集中取得一行作為對象

object?mysql_fetch_object?(?resource?$result?)

$result?=?mysql_query("SELECT?*?FROM?books");$object?=?mysql_fetch_object($result);
//stdClass?Object?(?[id]?=>?1?[bookname]?=>?PHP?[publisher]?=>?電子工業(yè)出版社?[author]?
=>?張三?[price]?=>?80.00?[ptime]?=>?0?[pic]?=>?[detail]?=>?PHP相關(guān)?)print_r($object);
$object?=?mysql_fetch_object($result);
//stdClass?Object?(?[id]?=>?3?[bookname]?=>?JSP?[publisher]?=>?電子工業(yè)出版社?[author]?=>?
王五?[price]?=>?70.00?[ptime]?=>?0?[pic]?=>?[detail]?=>?JSP相關(guān)?)
print_r($object);

　　對于上面的四個函數(shù)來說，默認(rèn)指針都指向第一行記錄。在獲取一行記錄后，指針會自動下移。如果是最后一委，則函數(shù)返回false。一般地，mysql_fetch_assoc()這種返回關(guān)聯(lián)數(shù)組形式的函數(shù)較常用

mysql_data_seek()

　　mysql_data_seek()函數(shù)可以移動內(nèi)部結(jié)果的指針

　　[注意]$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]?=>?電子工業(yè)出版社?[author]?=>?小白[price]
?=>80.00?[ptime]?=>?0?[pic]?=>?[detail]?=>?PHP相關(guān)?)print_r($assoc);mysql_data_seek($result?,?0);
$assoc?=?mysql_fetch_assoc($result);//Array?(?[id]?=>?1?[bookname]?=>?PHP?[publisher]?
=>?電子工業(yè)出版社?[author]?=>?張三?[price]?=>?80.00?[ptime]?=>?0?[pic]?=>?[detail]?=>?PHP相關(guān)?)
print_r($assoc);

　　下面使用while循環(huán)和mysql_fetch_assoc()函數(shù)將結(jié)果集以表格的形式顯示出來

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()函數(shù)用于釋放結(jié)果內(nèi)存

bool?mysql_free_result?(?resource?$result?)

　　mysql_free_result() 僅需要在考慮到返回很大的結(jié)果集時會占用多少內(nèi)存時調(diào)用。在腳本結(jié)束后所有關(guān)聯(lián)的內(nèi)存都會被自動釋放

?

步驟五：關(guān)閉數(shù)據(jù)庫連接

mysql_close()

　　mysql_close()函數(shù)用于關(guān)閉 MySQL 連接

bool?mysql_close?([?resource?$link_identifier?=?NULL?]?)

　　mysql_close() 關(guān)閉指定的連接標(biāo)識所關(guān)聯(lián)的到 MySQL 服務(wù)器的非持久連接。如果沒有指定 link_identifier，則關(guān)閉上一個打開的連接

　　所以，一個比較完整的php操作數(shù)據(jù)庫擴(kuò)展函數(shù)的程序如下所示

<?php//連接數(shù)據(jù)庫$link = mysql_connect(&#39;localhost&#39;,&#39;root&#39;,&#39;******&#39;);if(!$link){die(&#39;連接失?。?amp;#39;.mysql_error());
}//選擇數(shù)據(jù)庫mysql_select_db(&#39;bookstore&#39;,$link) or die(&#39;不能選定數(shù)據(jù)庫bookstore:&#39; .mysql_error());
//執(zhí)行SQL命令$insert = "insert into books(bookname, publisher, author, price, detail) values
(&#39;PHP&#39;,&#39;電子工業(yè)出版社&#39;,&#39;張三&#39;,&#39;80.00&#39;,&#39;PHP相關(guān)&#39;),
(&#39;ASP&#39;,&#39;電子工業(yè)出版社&#39;,&#39;李四&#39;,&#39;90.00&#39;,&#39;ASP相關(guān)&#39;),
(&#39;JSP&#39;,&#39;電子工業(yè)出版社&#39;,&#39;王五&#39;,&#39;70.00&#39;,&#39;JSP相關(guān)&#39;)";
$result = mysql_query($insert);
//操作結(jié)果集$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;;//釋放結(jié)果集mysql_free_result($result);//關(guān)閉數(shù)據(jù)庫連接mysql_close($link);?>

以上就是前端學(xué)PHP之mysql擴(kuò)展函數(shù)的內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注PHP中文網(wǎng)（www.miracleart.cn）！