Best way to receive variables from database

Question

I have a custom Wordpress table with the following contents:

account_info
id   | account_id | wp_title | wp_url
1    | 12345      | website  | website.com

What is the best (or fastest) way to get the results?

Option 1:

global $wpdb;   

$sql = "SELECT id, wp_title. wp_name FROM account_info";
$results = $wpdb->get_results($sql);

if(!empty($results)) { 
    foreach($results as $r) {   
         $id = $r->id;
         $wp_title = $r->wp_title;
         $wp_name = $r->wp_name;

    }
}

Option 2:

$id = $wpdb->get_var("SELECT id FROM account_info");
$wp_title = $wpdb->get_var("SELECT wp_title FROM account_info");
$wp_name = $wpdb->get_var("SELECT wp_name FROM account_info");

Obiously Option1..at a glance you are getting all the results. — GautamD31, Apr 22 '14 at 09:34
Always minimize the number of database searches where possible. — Steve, Apr 22 '14 at 09:37

score 1 · Accepted Answer · edited May 23 '17 at 10:32

When you are talking about "best" and "fastest" - these aren't the only things you need to take into consideration. Readibility is also key for future developers looking at your code.

Option one clearly shows that, if the result set isn't empty, you loop around them and set some variables. Option two is framework specific, and this isn't always the best approach.

So before micro-optimising, make sure readability is at it's utmost - never sacrifice one for the other.

Speed-wise, just make sure you're indexing your database properly. You're using the fastest loops available to PHP. Also, option two is making multiple database queries. Always try and combine multiple queries into one - you don't need to make a load of extra calls to the db that you don't actually need.

You want to avoid using the global keyword. It's bad practice and makes your code untestable. Get access to the $wpdb variable another way, either by passing it into a function or including a file elsewhere. See why global variables are bad and search a little on google.

score 0 · Answer 2 · answered Apr 22 '14 at 09:38

This would depend on your objective, query language and the metta data design around indexing etc.

In you example above collecting your results as an object above and iterating through would be the best option, if more than one option is available.

If only ever expecting a singular result to be safe ad LIMIT 1 in your query and access your object properties accordingly

cardeol · Answer 3 · 2014-04-22T09:48:28.113

IMHO, I recommend the option 1, but even better I recommend using prepared statements, suggested by many authors. It avoids abuse of the protocol and mimimize data transfers.

$stmt = $dbh->prepare("SELECT id, wp_title. wp_name FROM account_info");
if ($stmt->execute(array())) {
  while ($row = $stmt->fetch()) {
     $id = $row['id'];
     $wp_title = $row['wp_title'];
     $wp_name = $row['wp_name']; 
  }
}

Best way to receive variables from database

3 Answers3