Database results as objects or arrays?

Question

This question is similar to Mysql results in PHP - arrays or objects? However, my question expands on what has been discussed there.

I'm trying to decide which format is better for working with database results: objects or arrays. I'm not concerned about performance (from what I understand it makes little difference). My focus is also more on displaying the results—not creating, updating or deleting them.

To date I've always used objects, via functions like mysqli_fetch_object or PDO's fetchObject. This normally works nice, until I start doing joins. Joins lead to strange objects that are a blend of fields from two or more tables. My code quickly starts getting confusing.

I should note, I'm assigning specific class names, and not sticking with the default stdClass. I do this so that I can access any helper methods I've created in my classes. For example:

foreach ($members as $member)
{
    echo $member->full_name();
    echo $member->age();
}

For the sake of clarity, I'm considering moving to arrays for all my database results. From what I've read others do this as well. However, this leaves me with no easy way to access my helper methods.

Using the above example, I guess I could just output both the first and last name instead of using the full_name() method, not a big deal. As for the age() method, I guess I could create a generic utility class and put it in there.

My questions:

1. If you use (model) objects, how do you deal with joins?
2. If you use arrays, how do you deal with helper methods?

Answer 1

I think its better to represent all of your datas and its type in form of Model. For both joined and singular objects. Doing so will always omit your problem.

class Member_Details {
    public $id;    
    public $first_name;
    public $last_name;

    public function FullName() {
         return $this -> first_name." ".$this -> last_name;
    }
}

class Member_Address {
    public $id;
    public $address;
    public $city;
}

class MemberJoins {
     public $objects = array();
}

After creating such classes you can configures a JOIN in the following way.

$obj_details = new Member_Details();
$obj_address = new Member_Address();
//Add data to the objects and then

//Then create the join object
$obj_address_details = new MemberJoins();
$obj_address_details -> objects = array($obj_details, $obj_address);

These both have a common property id from which its data can be linked.

Answer 2

It’s preference at the end of the day. Personally, I prefer objects. Although CakePHP uses arrays for results using the “object” name as the array key. However, things start to get funny when you fetch related records in CakePHP.

With your problem, you could simply have objects within objects. For example:

stdClass Object
(
    [id] => 1
    [title] => Article Title
    [published] => 2013-03-04 16:30:00
    [category] => stdClass Object
        (
            [id] => 1
            [name] => Category Name
        )

)

You can then display associated data in your views as follows:

<?php echo $article->category->name; ?>

Or if you use getters and setters:

<?php echo $article->getCategory()->getName(); ?>

There’s no right or wrong answer. As I say, it’s all personal preference.

Answer 3

I've always used objects - but I don't put the data in directly from the query. Using 'set' functions I create the layout and so avoid issues with joins and naming collisions. In the case of your 'full_name' example I would probably use 'as' to get the name parts, set each in the object and offer 'get_full_name' as a member fn.

If you were feeling ambitious you could add all sorts of things to 'get_age'. Set the birth date once and go wild from there.

EDIT: There are several ways to make objects out of your data. You can predefine the class and create objects or you can create them 'on the fly'.

--> Some v simplified examples -- if this isn't sufficient I can add more.

on the fly:

$conn = DBConnection::_getSubjectsDB();  
$query = "select * from studies where Status = 1";  
$st = $conn->prepare( $query );  

$st->execute();  
$rows = $st->fetchAll();  
foreach ( $rows as $row )  
{  
    $study = (object)array();  
    $study->StudyId = $row[ 'StudyId' ];  
    $study->Name = $row[ 'StudyName' ];  
    $study->Investigator = $row[ 'Investigator' ];  
    $study->StartDate = $row[ 'StartDate' ];  
    $study->EndDate = $row[ 'EndDate' ];  
    $study->IRB = $row[ 'IRB' ];  

    array_push( $ret, $study );  
} 

predefined:

/** Single location info
*/
class Location  
{  
    /** Name  
    * @var string  
    */  
    public $Name;  

    /** Address  
    * @var string  
    */  
    public $Address;  

    /** City  
    * @var string  
    */  
    public $City;

    /** State
    * @var string
    */
    public $State;

    /** Zip
    * @var string
    */
    public $Zip;

    /** getMailing
    * Get a 'mailing label' style output
    */
    function getMailing()
    {  
         return $Name . "\n" . $Address . "\n" . $City . "," . $State . "  " . $Zip;
    }
}

usage:

$conn = DBConnection::_getLocationsDB();  
$query = "select * from Locations where Status = 1";  
$st = $conn->prepare( $query );  

$st->execute();  
$rows = $st->fetchAll();  
foreach ( $rows as $row )  
{  
    $location = new Location();  
    $location->Name= $row[ 'Name' ];  
    $location->Address = $row[ 'Address ' ];  
    $location->City = $row[ 'City' ];  
    $location->State = $row[ 'State ' ];  
    $location->Zip = $row[ 'Zip ' ];  

    array_push( $ret, $location );  
} 

Then later you can loop over $ret and output mailing labels:

foreach( $ret as $location )
{ 
    echo $location->getMailing();
}

Answer 4

I think you are talking about weird objects coming up using SELECT on two or more tables.

I solve this by using AS in my sql to give it a more simple name.

SELECT IFNULL(SUM(table2.big_name),0) AS sumBig

...


$result=$PDO->fetchObject();
$sum=$result->sumBig;