php中對(duì)Mysql數(shù)據(jù)庫(kù)的訪問操作
Jun 06, 2016 pm 07:59 PM
一： PHP-MySQL 是 PHP 操作 MySQL 資料庫(kù)最原始的 Extension ，PHP-MySQLi 的 i 代表 Improvement ，提更了相對(duì)進(jìn)階的功能，就 Extension 而言，本身也增加了安全性。而 PDO (PHP Data Object) 則是提供了一個(gè) Abstraction Layer 來(lái)操作資料庫(kù)，用講的其實(shí)
一：?
PHP-MySQL 是 PHP 操作 MySQL 資料庫(kù)最原始的 Extension ，PHP-MySQLi 的 i 代表 Improvement ，提更了相對(duì)進(jìn)階的功能，就 Extension 而言，本身也增加了安全性。而 PDO (PHP Data Object) 則是提供了一個(gè) Abstraction Layer 來(lái)操作資料庫(kù)，用講的其實(shí)看不出來(lái)有有什麼差別，所以就直接看程序吧…?
首先，先來(lái)看一段用 PHP-MySQL 寫成的程式碼，這類的范例常用在世界各地：
?
mysql_connect($db_host, $db_user, $db_password);?
mysql_select_db($dn_name);?
$result = mysql_query("SELECT `name` FROM `users` WHERE `location` = '$location'");?
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))?
{?
echo $row['name'];?
}?
mysql_free_result($result);?
?>?
乍看之下沒什麼問題，但其實(shí)背后有些學(xué)問…?
這種方式不能 Bind Column ，以前例的 SQL 敘述來(lái)說(shuō)，$location 的地方容易被 SQL Injection。后來(lái)于是發(fā)展出了 mysql_escape_string() (備注：5.3.0之后棄用) 以及 mysql_real_escape_string() 來(lái)解決這個(gè)問題，不過這麼一搞，整個(gè)敘述會(huì)變得複雜且丑陋，而且如果欄位多了，可以想見會(huì)是怎樣的情形…?
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",?
mysql_real_escape_string($user),?
mysql_real_escape_string($password));?
mysql_query($query);?
?>?
在 PHP-MySQLi 中有了不少進(jìn)步，除了透過 Bind Column 來(lái)解決上述問題，而且也多援 Transaction, Multi Query ，并且同時(shí)提供了 Object oriented style (下面這段 PHP-MySQLi 范例的寫法) 和 Procedural style (上面 PHP-MySQL 范例的寫法)兩種寫法…等等。
$mysqli = new mysqli($db_host, $db_user, $db_password, $db_name);?
$sql = "INSERT INTO `users` (id, name, gender, location) VALUES (?, ?, ?, ?)";?
$stmt = $mysqli->prepare($sql);?
$stmt->bind_param('dsss', $source_id, $source_name, $source_gender, $source_location);?
$stmt->execute();?
$stmt->bind_result($id, $name, $gender, $location);?
while ($stmt->fetch())?
{?
echo $id . $name . $gender . $location;?
}?
$stmt->close();?
$mysqli->close();?
?>?
但看到這邊又發(fā)現(xiàn)了一些缺點(diǎn)，例如得 Bind Result，這個(gè)就有點(diǎn)多馀，不過這其實(shí)無(wú)關(guān)緊要，因?yàn)樽畲蟮膯栴}還是在于這不是一個(gè)抽象(Abstraction)的方法，所以當(dāng)后端更換資料庫(kù)的時(shí)候，就是痛苦的開始…?
于是 PDO 就出現(xiàn)了(備注：目前 Ubuntu 和 Debian 來(lái)說(shuō)，PDO 并沒有直接的套件可以安裝，而是必須透過 PECL 安裝)。?
roga@carlisten-lx:~$ pecl search pdo?
=======================================?
Package Stable/(Latest) Local?
PDO 1.0.3 (stable) PHP Data Objects Interface.?
PDO_4D 0.3 (beta) PDO driver for 4D-SQL database?
PDO_DBLIB 1.0 (stable) FreeTDS/Sybase/MSSQL driver for PDO?
PDO_FIREBIRD 0.2 (beta) Firebird/InterBase 6 driver for PDO?
PDO_IBM 1.3.2 (stable) PDO driver for IBM databases?
PDO_INFORMIX 1.2.6 (stable) PDO driver for IBM Informix INFORMIX databases?
PDO_MYSQL 1.0.2 (stable) MySQL driver for PDO?
PDO_OCI 1.0 (stable) Oracle Call Interface driver for PDO?
PDO_ODBC 1.0.1 (stable) ODBC v3 Interface driver for PDO?
PDO_PGSQL 1.0.2 (stable) PostgreSQL driver for PDO?
PDO_SQLITE 1.0.1 (stable) SQLite v3 Interface driver for PDO?
pdo_user 0.3.0 (beta) Userspace driver for PDO?
當(dāng)透過 PECL 安裝裝好后，就可以透過以下方式來(lái)操作資料庫(kù)：?
$dsn = "mysql:host=$db_host;dbname=$db_name";?
$dbh = new PDO($dsn, $db_user, $db_password);?
$sql = "SELECT `name`, `location` FROM `users` WHERE `location` = ? , `name` = ?";?
$sth = $dbh->prepare($sql);?
$sth->execute(array($location, $name));?
$result = $sth->fetch(PDO::FETCH_OBJ);?
echo $result->name . $result->location;?
$dbh = NULL;?
?>?
乍看之下，PDO 的程式碼好像也沒有比較短，那到底好處是什麼呢？?
1. PDO 連接資料庫(kù)時(shí)透過 Connection String 來(lái)決定連接何種資料庫(kù)。?
2. PDO 可以透過 PDO::setAttribute 來(lái)決定連線時(shí)的設(shè)定，像是 Persistent Connection, 回傳錯(cuò)誤的方式(Exception, E_WARNING, NULL)。甚至是回傳欄位名稱的大小寫…等等。?
2. PDO 支援 Bind Column 的功能，除了基本的 Prepare, Execute 以外，也可以 Bind 單一欄位，并且指定欄位型態(tài)。?
4. PDO 是 Abstraction Layer 所以就算更換儲(chǔ)存媒介，需要花的功夫比起來(lái)是最少的。?
可惜的是，儘管這些東西都已經(jīng)出現(xiàn)很久了，但還是不夠大眾化。我想或許是肇因于大家習(xí)慣看坊間的書籍學(xué)習(xí)，但那些書本往往只會(huì)介紹最簡(jiǎn)單最傳統(tǒng)的方式。導(dǎo)致很多人還是在用 MySQL 這種方直接連資料庫(kù)。?
不過，目前來(lái)說(shuō)我個(gè)人還是最喜愛透過 DBI 來(lái)連接資料庫(kù)，像是 ActiveRecord 以及 Propel ORM(Object-Relational Mapping)。?
例如說(shuō)以 ActiveRecord 為例，如果要實(shí)現(xiàn)這樣的 SQL 敘述…?
INSERT INTO `users` (id, name, gender, location) VALUES(1, 'roga', 'male', 'tpe')?
以 PDO 來(lái)寫是：?
$sql = "INSERT INTO `users` (id, name, gender, location) VALUES(?, ?, ?, ?)";?
$sth = $dbh->prepare($sql);?
$sth->execute(array(1, 'roga', 'male', 'tpe'));?
?>?
但以 ActiveRecord 來(lái)說(shuō)的話，則是：?
$user = new User();?
$user->id = 1;?
$user->name = 'roga';?
$user->gender = 'male';?
$user->location = 'tpe';?
$user->save();?
?>?
后者在語(yǔ)法上是不是簡(jiǎn)潔很多呢，而且也大幅降低對(duì) SQL 語(yǔ)言的依賴性！(不同資料庫(kù)對(duì) SQL 實(shí)作的問題可參考 Comparison of different SQL implementations)?
mysql是非持繼連接函數(shù)而mysqli是永遠(yuǎn)連接函數(shù)。也就是說(shuō)?
mysql每次鏈接都會(huì)打開一個(gè)連接的進(jìn)程而mysqli多次運(yùn)行mysqli將使用同一連接進(jìn)程,從而減少了服務(wù)器的開銷?
有些朋友在編程的時(shí)候，使用new mysqli('localhost', usenamer', 'password', 'databasename');總是報(bào)?
錯(cuò)，F(xiàn)atal error: Class 'mysqli' not found in d:\...?
mysqli類不是php自帶的嗎？?
不是默認(rèn)開啟的，win下要改php.ini,去掉php_mysqli.dll前的;,linux下要把mysqli編譯進(jìn)去。?
一:Mysqli.dll是一個(gè)允許以對(duì)象的方式或者過程操作數(shù)據(jù)庫(kù)的，它的使用方式也很容易。這里就幾個(gè)常見的操作和 mysql.dll做一個(gè)對(duì)比。?
?。? ?mysql.dll(可以理解為函數(shù)式的方式)：?
復(fù)制代碼代碼如下:
$conn = mysql_connect('localhost', 'user', 'password'); //連接mysql數(shù)據(jù)庫(kù)?
mysql_select_db('data_base'); //選擇數(shù)據(jù)庫(kù)?
?
$result = mysql_query('select * from data_base');//這里有第二個(gè)可選參數(shù)，指定打開的連接?
$row = mysql_fetch_row( $result ) ) //為了簡(jiǎn)單，這里只取一行數(shù)據(jù)?
echo $row[0]; //輸出第一個(gè)字段的值?
mysqli也有過程式的方式，只不過開始貫以mysqli的前綴，其他都差不多。如果mysqli以過程式的方式操作的話， 有些函數(shù)必須指定資源，比如說(shuō) mysqli_query(資源標(biāo)識(shí),SQL語(yǔ)句),并且資源標(biāo)識(shí)的參數(shù)是放在前面的，而 mysql_query(SQL語(yǔ)句,'可選')的資源標(biāo)識(shí)是放在后面的，并且可以不指定，它默認(rèn)是上一個(gè)打開的連接或資源。?
?。??mysqli.dll(對(duì)象方式)：?
復(fù)制代碼代碼如下:
$conn = new mysqli('localhost', 'user', 'password','data_base');?
//這里的連接是new出來(lái)的，最后一個(gè)參數(shù)是直接指定數(shù)據(jù)庫(kù)，不用 ? ? ? ? ? ? ? ? ? mysql_select_db()了?
//也可以構(gòu)造時(shí)候不指定，然后 $conn -> select_db('data_base')?
$result = $conn -> query( 'select * from data_base' );?
$row = $result -> fetch_row(); //取一行數(shù)據(jù)?
echo row[0]; //輸出第一個(gè)字段的值?
二：mysql_fetch_row(),mysql_fetch_array()?
這兩個(gè)函數(shù)，返回的都是一個(gè)數(shù)組，區(qū)別就是第一個(gè)函數(shù)返回的數(shù)組是只包含值，我們只能$row[0],?
? ? ? $row[1],這樣以數(shù)組下標(biāo)來(lái)讀取數(shù)據(jù)，而mysql_fetch_array()返回的數(shù)組既包含第一種，也包含鍵值?
? ? ? 對(duì)的形式，我們可以這樣讀取數(shù)據(jù)，（假如數(shù)據(jù)庫(kù)的字段是 username,passwd）:?
$row['username'], $row['passwd']?
而且，如果用($row as $kay => $value)來(lái)操作的話，還以直接取得數(shù)據(jù)庫(kù)的字段名稱。?
更主要的是mysqli是php5提供的新函數(shù)庫(kù)，(i)表示改進(jìn)，其執(zhí)行速度更快.
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to access a character in a string by index in PHP
Jul 12, 2025 am 03:15 AM
In PHP, you can use square brackets or curly braces to obtain string specific index characters, but square brackets are recommended; the index starts from 0, and the access outside the range returns a null value and cannot be assigned a value; mb_substr is required to handle multi-byte characters. For example: $str="hello";echo$str[0]; output h; and Chinese characters such as mb_substr($str,1,1) need to obtain the correct result; in actual applications, the length of the string should be checked before looping, dynamic strings need to be verified for validity, and multilingual projects recommend using multi-byte security functions uniformly.
How to set and get session variables in PHP?
Jul 12, 2025 am 03:10 AM
To set and get session variables in PHP, you must first always call session_start() at the top of the script to start the session. 1. When setting session variables, use $_SESSION hyperglobal array to assign values ??to specific keys, such as $_SESSION['username']='john_doe'; it can store strings, numbers, arrays and even objects, but avoid storing too much data to avoid affecting performance. 2. When obtaining session variables, you need to call session_start() first, and then access the $_SESSION array through the key, such as echo$_SESSION['username']; it is recommended to use isset() to check whether the variable exists to avoid errors
How to prevent SQL injection in PHP
Jul 12, 2025 am 03:02 AM
Key methods to prevent SQL injection in PHP include: 1. Use preprocessing statements (such as PDO or MySQLi) to separate SQL code and data; 2. Turn off simulated preprocessing mode to ensure true preprocessing; 3. Filter and verify user input, such as using is_numeric() and filter_var(); 4. Avoid directly splicing SQL strings and use parameter binding instead; 5. Turn off error display in the production environment and record error logs. These measures comprehensively prevent the risk of SQL injection from mechanisms and details.
How to get the current session ID in PHP?
Jul 13, 2025 am 03:02 AM
The method to get the current session ID in PHP is to use the session_id() function, but you must call session_start() to successfully obtain it. 1. Call session_start() to start the session; 2. Use session_id() to read the session ID and output a string similar to abc123def456ghi789; 3. If the return is empty, check whether session_start() is missing, whether the user accesses for the first time, or whether the session is destroyed; 4. The session ID can be used for logging, security verification and cross-request communication, but security needs to be paid attention to. Make sure that the session is correctly enabled and the ID can be obtained successfully.
PHP get substring from a string
Jul 13, 2025 am 02:59 AM
To extract substrings from PHP strings, you can use the substr() function, which is syntax substr(string$string,int$start,?int$length=null), and if the length is not specified, it will be intercepted to the end; when processing multi-byte characters such as Chinese, you should use the mb_substr() function to avoid garbled code; if you need to intercept the string according to a specific separator, you can use exploit() or combine strpos() and substr() to implement it, such as extracting file name extensions or domain names.
How do you perform unit testing for php code?
Jul 13, 2025 am 02:54 AM
UnittestinginPHPinvolvesverifyingindividualcodeunitslikefunctionsormethodstocatchbugsearlyandensurereliablerefactoring.1)SetupPHPUnitviaComposer,createatestdirectory,andconfigureautoloadandphpunit.xml.2)Writetestcasesfollowingthearrange-act-assertpat
PHP prepared statement SELECT
Jul 12, 2025 am 03:13 AM
Execution of SELECT queries using PHP's preprocessing statements can effectively prevent SQL injection and improve security. 1. Preprocessing statements separate SQL structure from data, send templates first and then pass parameters to avoid malicious input tampering with SQL logic; 2. PDO and MySQLi extensions commonly used in PHP realize preprocessing, among which PDO supports multiple databases and unified syntax, suitable for newbies or projects that require portability; 3. MySQLi is specially designed for MySQL, with better performance but less flexibility; 4. When using it, you should select appropriate placeholders (such as? or named placeholders) and bind parameters through execute() to avoid manually splicing SQL; 5. Pay attention to processing errors and empty results to ensure the robustness of the code; 6. Close it in time after the query is completed.
How to split a string into an array in PHP
Jul 13, 2025 am 02:59 AM
In PHP, the most common method is to split the string into an array using the exploit() function. This function divides the string into multiple parts through the specified delimiter and returns an array. The syntax is exploit(separator, string, limit), where separator is the separator, string is the original string, and limit is an optional parameter to control the maximum number of segments. For example $str="apple,banana,orange";$arr=explode(",",$str); The result is ["apple","bana
