Efficient way of retrieving database info to a class

Question

Let's say you have a class, with certain properties, and that you tried your best so those properties would match the column names in a database in a way that you could fit each row of a db into your class object.

The way I usually do this, is by creating a method $class->get_all() to query the database for all the rows that match a specific query. From the resource set, I then create several sql objects, with mysql_fetch_object($resource) and store them in one array which is then returned. Basically something like this usually happens:

<?php while($row = mysql_fetch_object($result_set)){ $class->id = $row->id; $class->name = $row->name; $class->birthdate = $row->birthdate; $output[] = $class; } return $output; ?>

... which means I have 2 objects ($row and $class), with the same properties, and I am copying one of them into the other.

Is there a way to optimize this? Like, instead of copying the values from one to the other, maybe set them as a pointer to the same sql object property?

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — Madara's Ghost
– Madara's Ghost, Commented Jul 29, 2012 at 18:51
I did a little more digging and thanks to farzad (from SO), something interesting came up: Since PHP often works on "pass by value", instead of "pass by reference", code will obviously run slower. To mitigate that, there seems to be a feature in the PHP interpreter (something like lazy-copy) that sets a variable value as a pointer to another variable. This means the code above (despite the deprecation) is not as inefficient as I thought, because basically $class->id = "whatever value is in $row->id". :) — Franco
– Franco, Commented Jul 29, 2012 at 19:29
No. $row->id contains the literal ID (an integer). It is passed by value, not by reference. If you were to pass the entire $row object, you would be correct. Your initial assumptions were correct. — Madara's Ghost
– Madara's Ghost, Commented Jul 29, 2012 at 19:31
Yes, $row->id contains an int and must be passed by value from the db; the issue being, was that $class->id should not be a "copy by value" from $row->id (hence my question), but instead a "copy by reference". From what it looks like, "lazy-copy" handles that automatically in the way that pointers work. — Franco
– Franco, Commented Jul 29, 2012 at 19:39

Madara's Ghost · Accepted Answer · 2012-07-29 18:54:25Z

With PDO:

<?php $pdo = new PDO("mysql:host=localhost;dbname=database_name", "user", "pass"); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, PDO::ERRMODE_EXCEPTION); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $stmt = $pdo->query("SELECT `id`, `username`, `password` FROM `whatever`"); while ($class = $stmt->fetchObject("YourClassNameHere")) { /* * $class now holds an object of type YourClassNameHere * With all properties set properly. */ }

Note: this is a simplified example. In production code, you may need to take user-input into account, in which case you use prepared statements. That does not change the way you should fetch the results.

Hi Truth, thanks for the input. It does look like the PDO extension takes an existing class structure into account. I've been hearing about PDO, but never really used it, reason being there was no reason to it this far. Just out of curiosity: would it be possible to achieve the end result without using PDO?
@Tobias: Probably but look at how nice and easy this looks like. No setting, no instantiating whatsoever.

Collectives™ on Stack Overflow

Efficient way of retrieving database info to a class

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related