php - retrieve data from db using function pdo

Question

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?

Answer 1

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) {
    // ...
}