CODEWITHSUNDEEP
phppdo
JoshMc
JoshMc

Reputation: 739

PDO query class

I'm tinkering with a class that 'should' allow me to easily execute a fetchall query and display the results within a foreach statement. I assume all is working correctly as I have no errors. As for the foreach - this must be the problem? How would I foreach the results gained from the $connect->query()? I'm new to using any database OOP framework in my functions so I could be along the wrong lines completely.

 <?

error_reporting(1);

class dbconnect {

private $host;
private $database;
private $username;
private $password;

private $pdo;
private $error;

    public function __construct() {

        $this->host     = "localhost";              // Host
        $this->database = "images";                 // Database Name
        $this->username = "*";              // Username
        $this->password = "*";  // Password
        $options  = array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
        );

        try {
            $this->pdo = new PDO("mysql:host={$this->host};dbname={$this->dbname};charset=utf8", $this->username, $this->password, $options);
        }
        catch(PDOException $e) {
            $this->error = $e->getMessage();
        }

    }

    public function query($query) {

        try {
            $stmt = $this->pdo->prepare($query);
            $stmt->execute(); 
        } catch(PDOException $ex) { 
            die("Failed to run query: " . $ex->getMessage()); 
        } 

        $rows = $stmt->fetchAll();
        return $rows;

    }

}

$connect = new dbconnect;
$rows = $connect->query("select * from photos");
foreach($rows as $row):
    print $row['id'];
endforeach;



?>

Upvotes: 0

Views: 2351

Answers (4)

pedram shabani
pedram shabani

Reputation: 1680

$connect = new dbconnect;
$sql="select * from photos";
$stmt=$connect->pdo->prepare($sql); 
$stmt->execute();
$result=$stmt->fetch(PDO::FETCH_ASSOC);
foreach($result as $key => $value) {
   echo $key . "-" . $value . "<br/>";
}

Upvotes: 0

Mattias Buelens
Mattias Buelens

Reputation: 20159

The $rows variable you're declaring inside query is not accessible to the outside, it is local to that function. Most likely, you simply want to return those results to the caller:

$rows = $stmt->fetchAll();
return $rows; // return value from function...

and have the caller capture that return value in its own variable:

$rows = $connect->query("select * from images"); // ... is received by caller
foreach($rows as $row):

Also check out dougjore's answer, you're mixing $this->stmt and $stmt inside your query method.

Upvotes: 2

Sujit Poudel
Sujit Poudel

Reputation: 541

You could do

//PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

$this->stmt = $this->pdo->prepare($query);
$this->stmt->execute();

while ($result = $this->stmt->fetch(PDO::FETCH_ASSOC))
{

  //do something with the result
}

Have a look here for more options to fetch PDO query results: http://php.net/manual/en/pdostatement.fetch.php

Upvotes: 1

wiscWeb
wiscWeb

Reputation: 213

Pretty sure you aren't ever actually executing the query:

        $this->stmt = $this->pdo->prepare($query);
        $stmt->execute();

I believe (I could be wrong, I'm rather new to PDO myself and I haven't built a class for it), that you need to say $this->stmt->execute();

Upvotes: 2

Related Questions