PHP OOP: Handling multiple row outputs from a database, best way to do it?

Question

I'm trying to learn OOPHP and I think I'm getting there. I just don't know what the best way of outputting multiple database rows is.

The first method I've tried is doing the outputting normally within the function, but I think this kind of defeats the purpose of OOP.

I've also tried setting a public $list within the class that the database query pushes all the data to and then looping through the data outside the code, but ideally I'd like to create multiple instances of the object from the query and use their public variables (id, name, and eyecolour in my test case) to get the data so that the data always comes from the same place. But how do I create multiple objects from a query within the class and then how do I loop through them to display them, and how do I differentiate between them or just target a specific object with a specific value?

I have trawled through multiple topics but they all seem to focus on the problem in a specific situation. I'd like to learn it in this simple sense so I can then apply it to bigger projects.

Here is the code that I have been playing around with:

class Ignition {

public function dataConnection() {
    return new PDO('mysql:host=localhost;port=8889;dbname=oop_testing','root','root',array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
    }

}


class People {

private $db;

// Each person has a
public $id;
public $name;
public $eyecolour;
public $list = array();

public function __construct() {
    $this->db = new Ignition();
    $this->db = $this->db->dataConnection();
}

public function getList() {
    echo "People:<br /><br />";
    $sql = $this->db->prepare("SELECT * FROM people");
    $sql->execute();
    //$this->list = $sql->fetch(PDO::FETCH_ASSOC);
    while($person = $sql->fetch(PDO::FETCH_ASSOC)) {
        array_push($this->list, $person);
    }


    /*while($person = $sql->fetch(PDO::FETCH_ASSOC)) {
        echo $person['name'] . "<br />";
    }*/
}

public function getIndividual($search, $by = "id") {
    $sql = $this->db->prepare("SELECT * FROM people WHERE $by = :search");
    //$sql->bindParam(":by", $by);
    $sql->bindParam(":search", $search);
    $sql->execute();
    $individual = $sql->fetch(PDO::FETCH_ASSOC);
    $this->id = $individual['id'];
    $this->name = $individual['name'];
    $this->eyecolour = $individual['eyecolour'];
}

}


$people = new People();
$people->getList();

/*echo "<pre>";
var_dump($people->list);
echo "</pre>";*/

foreach ($people->list as $person) {
    echo $person['name'];
}

$people->getIndividual(1);
echo "<br />Name: " . $people->name;
echo "<br />Eye Colour: " . $people->eyecolour;

Pitchinnate · Accepted Answer · 2013-10-25 18:34:46Z

3

The best option would actually to get a list of People objects how you do that is up to you. I would utilize PDO::FETCH_CLASS and use static functions something like this:

class People {
    public $id;
    public $name;
    public $eyecolour;

    public function display() {
        echo "Id: {$this->id} name: {$this->name} eyecolour: {$this->eyecolour} <br>";
    }

    public static function get() {
        $result = self::db()->prepare("select * from people limit 1");
        $result->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE,'People');
        $result->execute();
        return $result->fetch();
    }

    public static function getall() {
        $result = self::db()->prepare("select * from people");
        $result->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE,'People');
        $result->execute();
        return $result->fetchAll();
    }

    public static function db() {
        return new PDO("mysql:host=127.0.0.1;dbname=mydb", 'root', '', array(PDO::ATTR_PERSISTENT => true));
    }
}


$person = People::get();
$person->display();
$all_people = People::getall();

foreach($all_people as $single_person) {
    //$single_person is an instance of People
    $single_person->display();
}

Obviously you can add search criteria to the get() and getall() methods like you have in your functions. I was just doing this as an example.

edited Oct 25, 2013 at 18:34

answered Oct 25, 2013 at 18:13

Pitchinnate

7,5561 gold badge22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Over a year ago

This seems to work for what I want it to do. So does FETCH_CLASS populate the properties based on the database column names?

Pitchinnate Over a year ago

Yes, if you want instead of this method you could get the results as a simple array and the create a function that sets the values of the object. Just all depends on the situation and what you are doing.

Atle · Accepted Answer · 2013-10-25 18:17:48Z

1

To practice OO, you may begin with setting all variables in the class to private (even when tempted to use public) and only use accessor functions. With this rule, because we are lazy, it is more likely that we put code in the correct place (inside the class).

Also, a good rule is that one object in real life should be one class.

In this specific case, I would create a class named Person with the different properties of a person (eyecolor, height etc.). The People class can create Person objects from what it retrieves from the database and store them in an array (maybe indexed by ID or name).

The Person class can take the whole row from the database as argument to it's constructor, and it then has to do the dirty work of setting/checking all variables itself. This is good for laziness and OO (no setter functions).

You may have an accessor function in People which returns a Person (searches for name, ID, etc.).

edited Oct 25, 2013 at 18:17

answered Oct 25, 2013 at 17:44

Atle

1,87712 silver badges10 bronze badges

4 Comments

Adam Over a year ago

That makes sense, I will give that a go. How do I go about creating objects from inside another class? And how do I then access them in whatever script it is?

Atle Over a year ago

You define both classes Person and People objects in the same scope (file scope). Inside People, if you read from the DB row by row, you can do $person = new Person($row); (make the __construct() of Person able to take the whole row as argument and set its internal properties from that). You can then do array_push($this->list, $person) to save the person for later.

Adam Over a year ago

That was what I was trying to do originally, it just seemed a bit messy!

Atle Over a year ago

I guess it depends on wheter your goal is to practice OO or to just make the thing work. This other solution is best for the latter :)

Collectives™ on Stack Overflow

PHP OOP: Handling multiple row outputs from a database, best way to do it?

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related