使用函数pdo从数据库中检索数据 [英] retrieve data from db using function pdo
问题描述
这是执行函数之前的代码，
try {
$ conn = new PDO（'mysql ：host = localhost; dbname = dbname'，'usr'，'pass'）;
$ conn-> setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
$ stmt = $ conn-> prepare（'SELECT * FROM posts WHERE status =：published ORDER BY id DESC LIMIT 5'）;
$ stmt-> execute（array（'：published'=>'published'））;
while（$ result = $ stmt-> fetch（PDO :: FETCH_ASSOC））{
$ contents = $ result ['content'];
$ title = $ result ['title'];
....
这工作正常。然后我移动db连接命令来分开php文件（functions.php）。 （$ sqlcom，$ exe）{
$ conn = new PDO（''$'$'$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' mysql：host = localhost; dbname = dbname'，'usr'，'pass'）;
$ conn-> setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
$ stmt = $ conn-> prepare（$ sqlcom）;
$ stmt-> execute（$ exe）;
$ data_db = $ stmt-> fetch（PDO :: FETCH_ASSOC）;
返回$ data_db;
}
然后我改变了这样的第一个提到的代码，
try {
$ data_db = run_db（'SELECT * FROM posts WHERE status =：published ORDER BY id DESC LIMIT 5'，array（'：发布'=>'发布'））;
while（$ result = $ data_db）{
$ contents = $ result ['content'];
$ title = $ result ['title'];
然后我得到的是一个无限重复的帖子。谁能告诉我如何解决这个问题？
$ b 函数run_db（$ sqlcom，$ exe）{
$ conn = new PDO（'mysql：host = localhost; dbname = dbname'，'usr'， '通过'）;
$ conn-> setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
$ stmt = $ conn-> prepare（$ sqlcom）;
$ stmt-> execute（$ exe）;
返回$ stmt;
}
以及对该函数的调用：
try {
$ stmt = run_db（'SELECT * FROM posts WHERE status =：published ORDER BY id DESC LIMIT 5'，array（'：published '=>'published'））;
while（$ result = $ stmt-> fetch（PDO :: FETCH_ASSOC））{
$ contents = $ result ['content'];
$ title = $ result ['title'];
编辑：更好解决方案是 jeroen 建议 - 一次返回所有提取的对象：
function run_db（$ sqlcom，$ exe）{
$ conn = new PDO（'mysql：host = localhost; dbname = dbname'，'usr'，'pass'）;
$ conn-> setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
$ stmt = $ conn-> prepare（$ sqlcom）;
$ stmt-> execute（$ exe）;
return $ stmt-> fetchAll（PDO :: FETCH_ASSOC）;
}
然后以这种方式调用：
try {
$ data = run_db（'SELECT * FROM posts WHERE status =：published ORDER BY id DESC LIMIT 5'，array（'：published'=> ;'published'））;
foreach（$ data as $ result）{
$ contents = $ result ['content'];
$ title = $ result ['title'];
编辑2：无论如何 - 将这种逻辑封装到一个函数中并不是一个好主意。现在只能执行 SELECT 查询，而结果数组始终只包含记录的关联数组。如果您希望（出于任何原因）检索对象的数组，甚至只检索一个值，那该怎么办？如果你想执行 INSERT ， UPDATE ， DELETE queries ???
如果您确实想要这样做，那么我会假设用这样的函数创建一个类：
class MyPDO {
private $ connection;
static $ instance;
函数__construct（）{
$ this-> connection = new PDO（'mysql：host = localhost; dbname = dbname'，'usr'，'pass'）;
$ this-> connection-> setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
}
静态函数getInstance（）{
返回self :: $实例？ ：self :: $ instance = new MyPDO;
}
//检索关联数组数组
函数getAssoc（$ sqlcom，$ exe）{
$ stmt = $ this-> connection->准备（$ sqlcom）;
$ stmt-> execute（$ exe）;
return $ stmt-> fetchAll（PDO :: FETCH_ASSOC）;
//检索对象数组
函数getObj（$ sqlcom，$ exe）{
$ stmt = $ conn-> prepare（$ sqlcom） ;
$ stmt-> execute（$ exe）;
return $ stmt-> fetchAll（PDO :: FETCH_OBJ）;
}
//退出单个值，如SELECT 1 FROM表WHERE column = true
函数getOne（$ sqlcom，$ exe）{
$ stmt = $ conn-> prepare（$ sqlcom）;
$ stmt-> execute（$ exe）;
return $ stmt-> fetchColumn（）;
}
//只是执行查询，用于INSERT，UPDATE，DELETE，CREATE ...
函数exec（$ sqlcom，$ exe）{
$ stmt = $ conn-> prepare（$ sqlcom）;
返回$ stmt->执行（$ exe）;
然后你可以这样调用它：
try {
$ pdo = MyPDO :: getInstance（）;
foreach（$ pdo-> getAssoc（'MySQL QUERY'），array（$ param，$ param））作为$ result）{
print_r（$ result）;
catch（\Exception $ e）{
// ...
}
here is the code before implementing the function,
try {
$conn = new PDO('mysql:host=localhost;dbname=dbname', 'usr', 'pass');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare('SELECT * FROM posts WHERE status= :published ORDER BY id DESC LIMIT 5');
$stmt->execute(array(':published' => 'published'));
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
$contents = $result['content'];
$title = $result['title'];
....
this works fine. Then i moved db connecting commands to separate php file(functions.php). and created this function,
function run_db($sqlcom,$exe){
$conn = new PDO('mysql:host=localhost;dbname=dbname', 'usr', 'pass');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare($sqlcom);
$stmt->execute($exe);
$data_db = $stmt->fetch(PDO::FETCH_ASSOC) ;
return $data_db;
}
Then i changed first mentioned code like this,
try {
$data_db=run_db('SELECT * FROM posts WHERE status= :published ORDER BY id DESC LIMIT 5',array(':published' => 'published'));
while ($result = $data_db) {
$contents = $result['content'];
$title = $result['title'];
Then all i got was one post repeating infinitely. Can anyone tell me how to correct this?
Change the function to this:
function run_db($sqlcom,$exe){
$conn = new PDO('mysql:host=localhost;dbname=dbname', 'usr', 'pass');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare($sqlcom);
$stmt->execute($exe);
return $stmt;
}
and the call to that function to:
try {
$stmt = run_db('SELECT * FROM posts WHERE status= :published ORDER BY id DESC LIMIT 5',array(':published' => 'published'));
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
$contents = $result['content'];
$title = $result['title'];
EDIT: Better solution is the one that jeroen advices - to return all the fetched objects at once:
function run_db($sqlcom,$exe){
$conn = new PDO('mysql:host=localhost;dbname=dbname', 'usr', 'pass');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare($sqlcom);
$stmt->execute($exe);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
Then calling this way:
try {
$data = run_db('SELECT * FROM posts WHERE status= :published ORDER BY id DESC LIMIT 5',array(':published' => 'published'));
foreach($data as $result) {
$contents = $result['content'];
$title = $result['title'];
EDIT 2: Anyway - wrapping such a logic into one function is not very good idea. now You are limited with executing of only SELECT queries and the resulting array containing always only record's associative array. What if You would like to (for any reason) retrieve the array of objects, or even only one single value? What if You would like to execute INSERT, UPDATE, DELETE queries???
If You for sure want to go this way, then I'd suppose creating a class with functions like this:
class MyPDO {
private $connection;
static $instance;
function __construct() {
$this->connection = new PDO('mysql:host=localhost;dbname=dbname', 'usr', 'pass');
$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
static function getInstance() {
return self::$instance ? : self::$instance = new MyPDO;
}
// retrieves array of associative arrays
function getAssoc($sqlcom, $exe) {
$stmt = $this->connection->prepare($sqlcom);
$stmt->execute($exe);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// retrieves array of objects
function getObj($sqlcom, $exe) {
$stmt = $conn->prepare($sqlcom);
$stmt->execute($exe);
return $stmt->fetchAll(PDO::FETCH_OBJ);
}
// retireves one single value, like for SELECT 1 FROM table WHERE column = true
function getOne($sqlcom, $exe) {
$stmt = $conn->prepare($sqlcom);
$stmt->execute($exe);
return $stmt->fetchColumn();
}
// just executes the query, for INSERT, UPDATE, DELETE, CREATE ...
function exec($sqlcom, $exe){
$stmt = $conn->prepare($sqlcom);
return $stmt->execute($exe);
}
}
Then You can call it this way:
try {
$pdo = MyPDO::getInstance();
foreach($pdo->getAssoc('MySQL QUERY'), array($param, $param)) as $result) {
print_r($result);
}
} catch(\Exception $e) {
// ...
}
这篇关于使用函数pdo从数据库中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
