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