使用PDO的类和方法，以及检索数据

Question

I'm new to OOP programming, and I'm really lost with this what the title says. When I try to put the query in a class and in another file, I get errors in a file called Main.php and don't even know what to do to fix them:

Notice: Undefined variable: sth in Select.php on line 10

Fatal error: Cannot access empty property in Select.php on line 10

If I put the select in Connection.php, it returns the rows just fine, but with classes, I get those.

Here's my code:

Connection.php:

<?php
$hostname = 'localhost';
$username = 'user';
$password = 'pass';

function connectDB ($hostname, $username, $password){
    $dbh = new PDO("mysql:host=$hostname;dbname=database", $username, $password);
    return $dbh;
}

$dbh = connectDB ($hostname, $username, $password);
echo 'Connected to database <br/>';

Select.php:

<?php require_once 'Connection.php';
class Select {

    public function select() {
        $sql= "select * from table limit 10; <br/>";
        echo $sql;
        $select = $dbh->query($sql)->fetchall(PDO::FETCH_ASSOC);

        foreach($this->$sth as $row){
            echo $row['column']."<br/>";
        }
    }
}

The question is, how can I print the result from the query (for example from main.php, which has an autoloader), and why do I get those errors, when on a single file, they work just fine?

Edit:

<?php
    $test = new Select($dbh);
    echo $test->select();
?>

Besides the fixes in the replies, I included Connection.php into the main.php, changed the echo in Select.php to return and it works perfectly now. Adding this in case someone ever gets as lost as me.

Answer 1

You do not want to iterate over the query, but the result of that query. So this probably is what you are looking for:

<?php
class Select {
    public function select() {
        $sql= 'select * from table limit 10';
        $select = $dbh->query($sql)->fetchall(PDO::FETCH_ASSOC);

        foreach($select as $row){
            echo $row['column']."<br/>";
        }
    }
}

And you also need to take care that the $dbh object is actually present inside that method. Either inject it into the object or specify it as method argument. So your full class will probably look something like that:

<?php
class Select {
    private $dbh;
    public function __construct($dbh) {
        $this->dbh = $dbh;
    }
    public function select() {
        $sql= 'select * from table limit 10';
        $select = $this->dbh->query($sql)->fetchall(PDO::FETCH_ASSOC);

        foreach($select as $row){
            echo $row['column']."<br/>";
        }
    }
}

And you instantiate the object like that:

$selectObj = new Select($dbh);

---

Some general warning, though: Using PDO's fetchall() method is convenient, but carries a huge risk: it means that the full result set has to be copied into an array inside the php script. For bigger results that may lead to issues with memory usage (scripts getting terminated for security reasons). Often it is the better approach to use a while loop over a single row fetched from the result set in each iteration.