You say I should just do one large query per page and then assign variables rather than do a new query each time it is needed.
Note that this advice comes from what I can only guess is the
purpose of
GetCell. The reason I keep
asking what the purpose is (you should still answer, in a little more detail and at a higher level) is because what you're trying to use it for might be a bad idea. As the sig says, my questions aren't rhetorical.
It's not one query per page. I can't think of a succinct way of putting it, but "one query per modeled object" (or collection of modeled objects) comes close, where "modeled object" is whatever the data in the database models. Basically, any time you need to get data from the database, do it once rather than in a loop.
Don't:
PHP:
<?php
$ids = array(...);
// example 1: fetch a column from DB, one row at a time
$query = $db->prepare('SELECT field FROM table WHERE id=?');
foreach ($ids as $id) {
$query->execute(array($id));
...
}
// example 2: get each table cell value separately
?>
<table>
<thead>...</thead>
<tbody>
<?php foreach ($ids as $id): ?>
<tr>
<?php foreach ($fields as $field): ?>
<td><?php GetCell($db, $table, $field, $id); ?></td>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>
Do:
PHP:
<?php
// example 1: fetch a column from DB all at once, matching some condition
$query = $db->prepare('SELECT field FROM table WHERE ...');
if ($query->execute(array(...))) {
foreach ($query as $idx => $field) {
...
}
}
// example 2: get all the data to appear in the table in one query
$query = $db->prepare('SELECT foo, bar, baz FROM table WHERE ...');
if ($query->execute(array(...))):
?>
<table>
<thead>...</thead>
<tbody>
<?php foreach ($query as $item): ?>
<tr>
<?php foreach ($item as $field => $value): ?>
<td><?php echo $value; ?></td>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php
endif;
// or, better yet, separate data handling from display (the "view"):
class Table { // this class would actually be provided by the framework
public $data;
function display() {
if ($this->data) {
?>
<table>
<thead>...</thead>
<tbody>
<?php foreach ($this->data as $item): ?>
<tr>
<?php foreach ($item as $field => $value): ?>
<td><?php echo $value; ?></td>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php
}
}
}
// the "controller", though it also performs a modeling task (namely, DB access)
$query = $db->prepare('SELECT foo, bar, baz FROM table WHERE ...');
if ($query->execute(array(...))) {
$tableView = new TableView;
$tableView->data = $query;
$tableView->show(); // outputs a table
}
The last is a step towards the
Model-View-Controller pattern pattern (MVC;
[2],
[3]), though Model-View might be a better description of the sample code (there is an implicit "controller" of sorts, as noted in the code, but that scarcely counts).
Would I have to worry about that with code igniter?
Rely on the framework for DB access as much as possible. Even if you need to use a custom query, see if the framework can be used. It might be able to construct the query, or execute it and process the results. Then you don't have to worry about any of it.
Would it still be faster to just do one query at the beginning for all the if/then branches?
Probably not, but it depends on the nature of the queries and the data relationships. Some of the processing might be best performed in the query statement, for example. Looking at the general case, if a row matching a query can be looked up in an index, separate queries may not be so bad. If the DBMS has to scan the table, then multiple queries will definitely perform worse.
If the other fields are from the same row and don't contain large amounts of data (they don't contain, for example, BLOBs), I'd fetch them in a single query, though only the fields you need.
SELECT * (if you're tempted) should only be used in very specific circumstances.
These topics require familiarity with the relational model to properly understand them. Given your current focus, it's probably better to pick a framework and learn how to use it.
repare
