×

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ù)庫(kù)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ù)用來(lái)取得上一步 INSERT 操作產(chǎn)生的 ID

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

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

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

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

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

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

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

?

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

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

?

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

mysql_fetch_row()

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

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

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

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() 中可選的第二個(gè)參數(shù) result_type 是一個(gè)常量，可以接受以下值：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é)果集中取得一行作為對(duì)象

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);

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

mysql_data_seek()

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

　　[注意]$row_number從0開(kāi)始

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é)果集以表格的形式顯示出來(lái)

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?'編號(hào)';echo?'書(shū)名';echo?'出版社';echo?'作者';echo?'價(jià)格';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é)果集時(shí)會(huì)占用多少內(nèi)存時(shí)調(diào)用。在腳本結(jié)束后所有關(guān)聯(lián)的內(nèi)存都會(huì)被自動(dòng)釋放

?

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

mysql_close()

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

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

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

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

<?php//連接數(shù)據(jù)庫(kù)$link = mysql_connect(&#39;localhost&#39;,&#39;root&#39;,&#39;******&#39;);if(!$link){die(&#39;連接失?。?amp;#39;.mysql_error());
}//選擇數(shù)據(jù)庫(kù)mysql_select_db(&#39;bookstore&#39;,$link) or die(&#39;不能選定數(shù)據(jù)庫(kù)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;編號(hào)&#39;;
echo &#39;書(shū)名&#39;;
echo &#39;出版社&#39;;
echo &#39;作者&#39;;
echo &#39;價(jià)格&#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ù)庫(kù)連接mysql_close($link);?>

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