How should PHP classes interact with the MySQL database?

Question

The other day, while developing my PHP project and implementing the User class, i started to wonder how this class should interact with the MySQL database the project is using.

Let me start with an example: let's say I have a getName() method, inside the User class, that returns the user's real name. What's the more fitting way to implement that method?

I came up with 2 solutions:

I put the DB query inside the getName() and only get what I need like this:
```
public function getName() {

    // MySQL query code here
}
```

I create a load() method inside the User class that load all the user data inside the class structure and then the getName() is something like this:

private $name;

// Load all user data inside the class' structure
public function load() {

    $this->name = // MySQL query here
}

public function getName() {

    return $this->name;
}

I thought, not sure if mistakenly or not, that the first way is more efficient because i only get the data I need, while the second way is more OOP but less efficient.

So, here's the question: what is the better way? Why? Are there better ways?

Either way, consider storing/caching the results of that so you do not make a query every time you use getName on that object. Also, consider not wrrying about all that by using a ORM/DBAL Solution like propel or doctrine stackoverflow.com/questions/2062473/php-orms-doctrine-vs-propel — Andresch Serj
– Andresch Serj, Commented Apr 3, 2014 at 9:08
Also check out Lazy Loading and the Active Record Pattern en.wikipedia.org/wiki/Active_record_pattern — Andresch Serj
– Andresch Serj, Commented Apr 3, 2014 at 9:11
@AndreschSerj I found your comment very helpful, if you write down the answer I'll mark it :) — siannone
– siannone, Commented Apr 11, 2014 at 12:17

Community · Accepted Answer · 2017-05-23 12:05:37Z

1

Either way, consider storing/caching the results of that so you do not make a query every time you use getName on that object. Also, consider not wrrying about all that by using a ORM/DBAL Solution like propel or doctrine.

Also check out Lazy Loading and the Active Record Pattern

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Apr 14, 2014 at 7:03

Andresch Serj

37.8k16 gold badges63 silver badges106 bronze badges

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

Comments

Victory · Accepted Answer · 2014-04-03 09:18:46Z

1

Run your query just in time and only run it once (unless you know the value might change), try something like the following:

 class User {
   protected $data;

   function getName()
   {
     if (!isset($data['name'])) {
       // if you can load more than just $this->data['name'] in one query
       // you probably should.
       $this->data['name'] = // query here
     }
     return $this->data['name'];
   }

 }

edited Apr 3, 2014 at 9:18

answered Apr 3, 2014 at 9:09

Victory

5,9013 gold badges28 silver badges47 bronze badges

3 Comments

Andresch Serj Over a year ago

Don't you think the overhead of reading probably 6 fields in one query is worth not doing 6 queries and thus, the load should load all the cols from the table involved?

Victory Over a year ago

@AndreschSerj loading all columns would be best, instead of $this->data['name'], maybe just $this->data = getQueryResultsAsArra();

Andresch Serj Over a year ago

What i meant exactly :D

Denat Hoxha · Accepted Answer · 2014-04-03 09:10:15Z

0

Aside from the question being kinda broad (as there are countless patterns), the second way you mentioned is better IMO, and to add to it I would also suggest supplying ID as a parameter which you could then use to build a single query to fetch the user by ID and then manually assign all properties (from the fetched row).

answered Apr 3, 2014 at 9:10

Denat Hoxha

9458 silver badges16 bronze badges

Collectives™ on Stack Overflow

How should PHP classes interact with the MySQL database?

3 Answers 3

Comments

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Linked

Related