Is it best to make fewer calls to the database and output the results in an array?

Question

I'm trying to create a more succinct way to make hundreds of db calls. Instead of writing the whole query out every time I wanted to output a single field, I tried to port the code into a class that did all the query work. This is the class I have so far:

class Listing {

/* Connect to the database */
private $mysql;

function __construct() {
    $this->mysql = new mysqli(DB_LOC, DB_USER, DB_PASS, DB) or die('Could not connect');
}
function getListingInfo($l_id = "", $category = "", $subcategory = "", $username = "", $status = "active") {
    $condition = "`status` = '$status'";
    if (!empty($l_id)) $condition .= "AND `L_ID` = '$l_id'";
    if (!empty($category)) $condition .= "AND `category` = '$category'";
    if (!empty($subcategory)) $condition .= "AND `subcategory` = '$subcategory'";
    if (!empty($username)) $condition .= "AND `username` = '$username'";
    $result = $this->mysql->query("SELECT * FROM listing WHERE $condition") or die('Error fetching values');
    $info = $result->fetch_object() or die('Could not create object');
    return $info;
}
}

This makes it easy to access any info I want from a single row.

$listing = new Listing;
echo $listing->getListingInfo('','Books')->title;

This outputs the title of the first listing in the category "Books". But if I want to output the price of that listing, I have to make another call to getListingInfo(). This makes another query on the db and again returns only the first row.

This is much more succinct than writing the entire query each time, but I feel like I may be calling the db too often. Is there a better way to output the data from my class and still be succinct in accessing it (maybe outputting all the rows to an array and returning the array)? If yes, How?

Answer 1

Either cache the result in an internal var

Or Comment it with a warning and explain to function users to copy the result in an var instead of calling it again and again with the same params

Answer 2

Yes, that would be calling the db too often.

A couple of solutions

1) put the listing info in a variable

2) cache the results in a hashmap or dictionary (be careful for memory leaks)

Answer 3

You should be able to store the whole object from the query into a variable and then access the single values from that object:

$object = $listing->getListingInfo('','Books');
$title = $object->title;
$price= $object->price;

But you can also use fetch_assoc() and return the whole assiciative array:

$array = $listing->getListingInfo('','Books');
$title = $object['title'];
$price= $object['price'];

This will give you the same results and also with only one query to the DB.

EDIT: If the getListingInfo() is the only function you should think of the following:

1. rename the function to prepareListingInfo() and within the function only prepare the query and store it in a class variable.
2. add a getNextListingInfo() function, which will return an object or associative array with the next row.

Using this new function, you can get every row that matches your query.

Answer 4

Do you actually have a performance issue?

If your current setup works and doesn't suffer from performance issues, I wouldn't touch it.

This sort of DB access abstraction will likely become a maintenance issue and probably won't help performance.

Also, you're susceptible to SQL injection.