11

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?
Improve this question
8
  • 2
    It's a matter of taste, but I prefer to deal with arrays when working with data from the database, then use that data to create objects, if needed. I would rather create objects using the methods I define rather than what ships with the DB extensions. Commented Jan 2, 2013 at 23:40
  • 1
    Why not both? $sth->fetch(PDO::FETCH_LAZY); then just whatever accessor best suits the code you're writing. Commented Jan 2, 2013 at 23:43
  • @DudeSolutions That approach makes sense. So, are you saying that if you want to access one of your helper methods, you would just create the actual object at that point? Seems like extra work, but I could see that creating a nice distinction between database results and real objects. Commented Jan 2, 2013 at 23:47
  • @Jonathan If you need your data to be an object in order to access your helper method, then yes. And really, that's basically the core of one of the many problems frameworks try to solve-- when and where should I access X helper method. There is no right answer,so long as it works and is maintainable. Commented Jan 2, 2013 at 23:51
  • @Sammitch Pretty slick idea! Commented Jan 2, 2013 at 23:54

4 Answers 4

Reset to default
7
+100

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();
}
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Do you mind including some code to better illustrate your approach?
Thanks for providing code! So, if you did a JOIN, would you simply create two objects instead of one?
Also, does this approach not create a lot of extra work for you? Instead of just working with the array/object from your database class, you're manually going through each result set. Or do you only do this when you need to fine-tune it a little?
Voted up: trivial ORM looks like it saves time, but IME it's a nightmare to deal with for complex systems - both in initial implementation and maintenance - using factories saves massive amounts of pain and time.
Nice answer. However, $study = (object)array(); is a rather ugly way of saying $study = new stdClass();.
|
1

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.

Improve this answer

3 Comments

I love this approach Martin, but this cannot happen within one database query, which is really important for me. Extra fields from a join would be added to the parent object, not a parameter. I'm working on a larger scale project right now where I really see the negative impacts of unnecessary extra queries. Unless I'm missing something? Thanks again.
It is possible; CakePHP manages to do it (just with arrays not objects) in one query. You could assign results to a model that populates the correct properties and “sub”-objects.
Interesting, they must be doing this in their ORM, similar to what @ethrbunny is suggesting.
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.

Improve this answer

2 Comments

Starx: Thanks for your answer. Will this work when running a SELECT from the database though? I'm thinking my question wasn't clear enough on that. I'm really looking to run ONE query, which could include joins (for performance reasons). Then, after the query is complete I'm not sure what the best way to work with the data is - as an array, or as an object.
@Jonathan, Then you have to create combined Model Classes and work your way up.
0

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;
Improve this answer

1 Comment

Not really. I use AS often, but I'm still stuck with a object that contains paramaters that don't really belong to it. Update: but thanks for the response either way. ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.