国产av日韩一区二区三区精品,成人性爱视频在线观看,国产,欧美,日韩,一区,www.成色av久久成人,2222eeee成人天堂

Home Backend Development PHP Tutorial Detailed explanation of PDO examples in PHP

Detailed explanation of PDO examples in PHP

Mar 01, 2018 am 10:54 AM
php Example Detailed explanation

1. 何為PDO?

PDO(PHP數(shù)據(jù)對(duì)象) 是一個(gè)輕量級(jí)的、具有兼容接口的PHP數(shù)據(jù)連接拓展,是一個(gè)PHP官方的PECL庫(kù),隨PHP 5.1發(fā)布,需要PHP 5的面向?qū)ο笾С?,因而在更早的版本上無(wú)法使用。它所提供的數(shù)據(jù)接入抽象層,具有與具體數(shù)據(jù)庫(kù)類(lèi)型無(wú)關(guān)的優(yōu)勢(shì),為它所支持的數(shù)據(jù)庫(kù)提供統(tǒng)一的操作接口。目前支持的數(shù)據(jù)庫(kù)有Cubrid、FreeTDS / Microsoft SQL Server / Sybase、Firebird/Interbase 6、IBM DB2、IBM Informix Dynamic Server、MySQL 3.x/4.x/5.x、Oracle Call Interface、ODBC v3 (IBM DB2, unixODBC and win32 ODBC)、PostgreSQL、SQLite 3 and SQLite 2、Microsoft SQL Server / SQL Azure等。由于PDO是在底層實(shí)現(xiàn)的統(tǒng)一的數(shù)據(jù)庫(kù)操作接口,因而利用它能夠?qū)崿F(xiàn)更高級(jí)的數(shù)據(jù)庫(kù)操作,比如存儲(chǔ)過(guò)程的調(diào)度等。

2. PDO實(shí)例

下面將實(shí)現(xiàn)一個(gè)用PDO連接SQLite數(shù)據(jù)庫(kù)的實(shí)現(xiàn)分頁(yè)顯示的例子,查詢(xún)的結(jié)果輸出為JSON數(shù)據(jù)。

<?php
$cat = isset ($_GET[&#39;cat&#39;]) ? $_GET[&#39;cat&#39;] : "1";
$pg = isset ($_GET[&#39;pg&#39;]) ? $_GET[&#39;pg&#39;] : "1";
 
$limit = 10;
$dbname = &#39;shelf.sqlite&#39;;
try {
$db = new PDO("sqlite:" . $dbname);
$sth = $db->prepare(&#39;select * from book where cat_id=:id limit :offset, :limit&#39;, array (
PDO :: ATTR_CURSOR => PDO :: CURSOR_FWDONLY
));
 
$result = $sth->execute(array (
&#39;:id&#39; => $cat,
&#39;:offset&#39; => ($pg -1) * $limit,
&#39;:limit&#39; => $limit
));
$list = array ();
$query = $db->query(&#39;select count(*) from book where cat_id=&#39; . $cat)->fetch(); //Only 1 row
$list["count"] = $query[0];
if ($result) {
while ($row = $sth->fetch(PDO :: FETCH_ASSOC)) {
$list["books"][] = $row;
}
} else {
print_r($db->errorInfo());
}
 
$db = NULL;
 
echo str_replace(&#39;\\/&#39;, &#39;/&#39;, json_encode($list));
 
} catch (PDOException $ex) {
print_r($ex);
}
?>

3. PDO中的常量

PDO庫(kù)中定義了一些靜態(tài)常量,這些常量用PDO :: 的方式進(jìn)行調(diào)用。比如在prepare()語(yǔ)句中經(jīng)常這樣使用:

$query=$db->prepare(&#39;select * from book where cat_id=:id limit :offset, :limit&#39;, array (
PDO :: ATTR_CURSOR => PDO :: CURSOR_FWDONLY
));

這里的PDO :: ATTR_CURSOR和PDO :: CURSOR_FWDONLY都是PDO常量,這里將數(shù)據(jù)庫(kù)的cursor類(lèi)型設(shè)為forward only。

4. PDO中的連接和連接管理

PDO中的連接是通過(guò)創(chuàng)建PDO類(lèi)的實(shí)例而建立的。創(chuàng)造時(shí)需要提供數(shù)據(jù)源名稱(chēng)(DSN)及可選的用戶(hù)名和密碼等參數(shù)。在這個(gè)過(guò)程中值得注意的是,如果發(fā)生異常,PHP的Zend引擎默認(rèn)操作是將具體的錯(cuò)誤信息顯示出來(lái),這就帶來(lái)一個(gè)問(wèn)題:連接信息(數(shù)據(jù)位置、用戶(hù)名、密碼等)可能遭到泄露。因此,為嚴(yán)防此類(lèi)不幸的事情發(fā)生,一定要顯式捕獲異常,無(wú)論是用try...catch語(yǔ)句還是用set_exception_handler()函數(shù),隱藏一些敏感數(shù)據(jù)。所不同的是,調(diào)用set_exception_handler()后代碼的執(zhí)行將終止,而采用try...catch的形式,異常之后的代碼將繼續(xù)執(zhí)行,正如try...catch語(yǔ)句的原意一般(更多請(qǐng)移步:PHP學(xué)習(xí)筆記之異常捕獲與處理)。

<?php
$db = new PDO(&#39;mysql:host=localhost;dbname=test&#39;, $user, $pass);
//使用新建立的數(shù)據(jù)庫(kù)連接。
//... ...
//連接在PDO實(shí)例的生命周期里是活動(dòng)的。使用完畢后應(yīng)當(dāng)關(guān)閉此連接,若不這樣做PHP在代碼結(jié)束時(shí)才關(guān)閉此連接,將占用一部分內(nèi)存。
$db = null;
?>

當(dāng)然,事情并不都是這樣,有時(shí)我們可能會(huì)需要一個(gè)永久的連接。具體做法是在PDO的構(gòu)造函數(shù)里再加一個(gè)參數(shù):

<?php
$db = new PDO(&#39;mysql:host=localhost;dbname=test&#39;, $user, $pass, array(
    PDO::ATTR_PERSISTENT => true
));
?>

永久的連接能夠跨越代碼,在一個(gè)代碼執(zhí)行完畢時(shí)并未被關(guān)閉,而是被緩存起來(lái),以供另一段擁有同樣權(quán)限的代碼重復(fù)使用。這樣便不必每次都新建一個(gè)連接,省了不少事不說(shuō),還能夠加快網(wǎng)站速度。

5. PDO中的查詢(xún)操作:exec/query/prepared statement

在PDO中有三種方法執(zhí)行查詢(xún)操作,分別是用exec、query和使用prepared statement。三種方法各有利弊,先說(shuō)exec。

(1)PDO::exec()一般用于執(zhí)行一次的SQL語(yǔ)句,返回受查詢(xún)影響的行數(shù)。它不適用于SELECT語(yǔ)句,如果需要用一次是SELECT語(yǔ)句,可以用PDO::query();也不適用于多次使用的語(yǔ)句,如果有多次使用的需求,考慮用PDO::prepare()。

(2)PDO::query()用于執(zhí)行一次SELECT語(yǔ)句,執(zhí)行后應(yīng)當(dāng)隨即使用PDOStatement::fetch()語(yǔ)句將結(jié)果取出,否則立即進(jìn)行下一次的PDO::query()將會(huì)報(bào)錯(cuò)。在2.PDO實(shí)例部分,為了得到查詢(xún)數(shù)據(jù)的總量,就用了PDO::query()語(yǔ)句。

(3)PDOStatement表示一個(gè)prepared statement語(yǔ)句,而在執(zhí)行之后,又將返回一組關(guān)聯(lián)數(shù)組的結(jié)果。如果一類(lèi)查詢(xún)(查詢(xún)結(jié)構(gòu)相似而具體的參數(shù)不一)需要一次解析而執(zhí)行使用很多次,可以先用prepared statement,這樣可以為具體的查詢(xún)的執(zhí)行做好準(zhǔn)備,避免了分析、編譯、優(yōu)化的循環(huán),將減少資源占用率,從而提高運(yùn)行效率。通過(guò)對(duì)數(shù)據(jù)庫(kù)進(jìn)行prepare操作,便會(huì)返回PDOStatement數(shù)據(jù)類(lèi)型,從而在其基礎(chǔ)上展開(kāi)execute、fetch等進(jìn)一步的操作。

 
$sth = $db->prepare(&#39;select * from book where cat_id=:id limit :offset, :limit&#39;, array (
PDO :: ATTR_CURSOR => PDO :: CURSOR_FWDONLY
));
//用$limit1得到一個(gè)結(jié)果
$result1 = $sth->execute(array (
        &#39;:id&#39; => $cat,
        &#39;:offset&#39; => ($pg -1) * $limit1,
        &#39;:limit&#39; => $limit1
    ));
//用$limit2得到另一個(gè)結(jié)果
$result2 = $sth->execute(array (
        &#39;:id&#39; => $cat,
        &#39;:offset&#39; => ($pg -1) * $limit2,
        &#39;:limit&#39; => $limit2
    ));

使用prepared statement還有一個(gè)好處就是,語(yǔ)句里不再使用引號(hào),PDO driver已自動(dòng)完成這一操作,可以防止SQL注入攻擊的危險(xiǎn)。查詢(xún)語(yǔ)句里可以使用包含名字的(:name)和問(wèn)號(hào)(?)的參數(shù)占位符,分別將用associated array 和indexed array傳入數(shù)值。

//用位置參入?yún)?shù)
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);
//用名稱(chēng)傳入?yún)?shù)
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(&#39;:name&#39;, $name);
$stmt->bindParam(&#39;:value&#39;, $value);
$name = &#39;one&#39;;
$value = 1;
$stmt->execute();
/////////////////////////////////////////////
//也可以這樣實(shí)現(xiàn)
//用位置參入?yún)?shù),indexed array
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$name = &#39;one&#39;;
$value = 1;
$stmt->execute(array($name,$value));
//用名稱(chēng)傳入?yún)?shù), associated array
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$name = &#39;one&#39;;
$value = 1;
$stmt->execute(array(&#39;:name&#39;=>$name,&#39;:value&#39;=>$value));

特別注意:查詢(xún)語(yǔ)句中的占位符應(yīng)當(dāng)是占據(jù)整個(gè)值的位置,如果有模糊查詢(xún)的符號(hào),應(yīng)當(dāng)這樣做:

// placeholder must be used in the place of the whole value
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(array("%$_GET[name]%"));
//下面這樣就有問(wèn)題了
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE &#39;%?%&#39;");
$stmt->execute(array($_GET[&#39;name&#39;]));

相關(guān)推薦:

PHP中PDO實(shí)現(xiàn)的SQLite操作類(lèi)

PDO如何操作MySQL

PHP之詳解PDO

The above is the detailed content of Detailed explanation of PDO examples in PHP. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undress AI Tool

Undress AI Tool

Undress images for free

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

PHP Tutorial
1488
72
How to use PHP to build social sharing functions PHP sharing interface integration practice How to use PHP to build social sharing functions PHP sharing interface integration practice Jul 25, 2025 pm 08:51 PM

The core method of building social sharing functions in PHP is to dynamically generate sharing links that meet the requirements of each platform. 1. First get the current page or specified URL and article information; 2. Use urlencode to encode the parameters; 3. Splice and generate sharing links according to the protocols of each platform; 4. Display links on the front end for users to click and share; 5. Dynamically generate OG tags on the page to optimize sharing content display; 6. Be sure to escape user input to prevent XSS attacks. This method does not require complex authentication, has low maintenance costs, and is suitable for most content sharing needs.

PHP calls AI intelligent voice assistant PHP voice interaction system construction PHP calls AI intelligent voice assistant PHP voice interaction system construction Jul 25, 2025 pm 08:45 PM

User voice input is captured and sent to the PHP backend through the MediaRecorder API of the front-end JavaScript; 2. PHP saves the audio as a temporary file and calls STTAPI (such as Google or Baidu voice recognition) to convert it into text; 3. PHP sends the text to an AI service (such as OpenAIGPT) to obtain intelligent reply; 4. PHP then calls TTSAPI (such as Baidu or Google voice synthesis) to convert the reply to a voice file; 5. PHP streams the voice file back to the front-end to play, completing interaction. The entire process is dominated by PHP to ensure seamless connection between all links.

How to use PHP combined with AI to achieve text error correction PHP syntax detection and optimization How to use PHP combined with AI to achieve text error correction PHP syntax detection and optimization Jul 25, 2025 pm 08:57 PM

To realize text error correction and syntax optimization with AI, you need to follow the following steps: 1. Select a suitable AI model or API, such as Baidu, Tencent API or open source NLP library; 2. Call the API through PHP's curl or Guzzle and process the return results; 3. Display error correction information in the application and allow users to choose whether to adopt it; 4. Use php-l and PHP_CodeSniffer for syntax detection and code optimization; 5. Continuously collect feedback and update the model or rules to improve the effect. When choosing AIAPI, focus on evaluating accuracy, response speed, price and support for PHP. Code optimization should follow PSR specifications, use cache reasonably, avoid circular queries, review code regularly, and use X

PHP creates a blog comment system to monetize PHP comment review and anti-brush strategy PHP creates a blog comment system to monetize PHP comment review and anti-brush strategy Jul 25, 2025 pm 08:27 PM

1. Maximizing the commercial value of the comment system requires combining native advertising precise delivery, user paid value-added services (such as uploading pictures, top-up comments), influence incentive mechanism based on comment quality, and compliance anonymous data insight monetization; 2. The audit strategy should adopt a combination of pre-audit dynamic keyword filtering and user reporting mechanisms, supplemented by comment quality rating to achieve content hierarchical exposure; 3. Anti-brushing requires the construction of multi-layer defense: reCAPTCHAv3 sensorless verification, Honeypot honeypot field recognition robot, IP and timestamp frequency limit prevents watering, and content pattern recognition marks suspicious comments, and continuously iterate to deal with attacks.

PHP realizes commodity inventory management and monetization PHP inventory synchronization and alarm mechanism PHP realizes commodity inventory management and monetization PHP inventory synchronization and alarm mechanism Jul 25, 2025 pm 08:30 PM

PHP ensures inventory deduction atomicity through database transactions and FORUPDATE row locks to prevent high concurrent overselling; 2. Multi-platform inventory consistency depends on centralized management and event-driven synchronization, combining API/Webhook notifications and message queues to ensure reliable data transmission; 3. The alarm mechanism should set low inventory, zero/negative inventory, unsalable sales, replenishment cycles and abnormal fluctuations strategies in different scenarios, and select DingTalk, SMS or Email Responsible Persons according to the urgency, and the alarm information must be complete and clear to achieve business adaptation and rapid response.

How to use PHP to combine AI to generate image. PHP automatically generates art works How to use PHP to combine AI to generate image. PHP automatically generates art works Jul 25, 2025 pm 07:21 PM

PHP does not directly perform AI image processing, but integrates through APIs, because it is good at web development rather than computing-intensive tasks. API integration can achieve professional division of labor, reduce costs, and improve efficiency; 2. Integrating key technologies include using Guzzle or cURL to send HTTP requests, JSON data encoding and decoding, API key security authentication, asynchronous queue processing time-consuming tasks, robust error handling and retry mechanism, image storage and display; 3. Common challenges include API cost out of control, uncontrollable generation results, poor user experience, security risks and difficult data management. The response strategies are setting user quotas and caches, providing propt guidance and multi-picture selection, asynchronous notifications and progress prompts, key environment variable storage and content audit, and cloud storage.

Beyond the LAMP Stack: PHP's Role in Modern Enterprise Architecture Beyond the LAMP Stack: PHP's Role in Modern Enterprise Architecture Jul 27, 2025 am 04:31 AM

PHPisstillrelevantinmodernenterpriseenvironments.1.ModernPHP(7.xand8.x)offersperformancegains,stricttyping,JITcompilation,andmodernsyntax,makingitsuitableforlarge-scaleapplications.2.PHPintegrateseffectivelyinhybridarchitectures,servingasanAPIgateway

PHP integrated AI speech recognition and translator PHP meeting record automatic generation solution PHP integrated AI speech recognition and translator PHP meeting record automatic generation solution Jul 25, 2025 pm 07:06 PM

Select the appropriate AI voice recognition service and integrate PHPSDK; 2. Use PHP to call ffmpeg to convert recordings into API-required formats (such as wav); 3. Upload files to cloud storage and call API asynchronous recognition; 4. Analyze JSON results and organize text using NLP technology; 5. Generate Word or Markdown documents to complete the automation of meeting records. The entire process needs to ensure data encryption, access control and compliance to ensure privacy and security.

See all articles