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 http://stackoverflow.com/questions/2062473/php-orms-doctrine-vs-propel — Andresch Serj, Apr 03 '14 at 09:08
Also check out Lazy Loading and the Active Record Pattern http://en.wikipedia.org/wiki/Active_record_pattern — Andresch Serj, Apr 03 '14 at 09:11
@AndreschSerj I found your comment very helpful, if you write down the answer I'll mark it :) — siannone, Apr 11 '14 at 12:17

Victory · Answer 1 · 2014-04-03T09:18:46.530

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 03 '14 at 09:18

answered Apr 03 '14 at 09:09

Victory

5,811
2
26
45

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? – Andresch Serj Apr 03 '14 at 09:10
1

@AndreschSerj loading all columns would be best, instead of $this->data['name'], maybe just `$this->data = getQueryResultsAsArra();` – Victory Apr 03 '14 at 09:13
What i meant exactly :D – Andresch Serj Apr 03 '14 at 09:34

score 1 · Accepted Answer · edited May 23 '17 at 12:05

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 '17 at 12:05

Community

1
1

answered Apr 14 '14 at 07:03

Andresch Serj

35,217
15
59
101

score 0 · Answer 3 · answered Apr 03 '14 at 09:10

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).

How should PHP classes interact with the MySQL database?

3 Answers3