Database query and loop

[bbonora]

I want to pass all the columns of a query to an object so that I can access the object in my smarty template like this:

Code: Select all

{foreach from=$items item=item}
{$item->column_1}
{$item->column_2}
{$item->column_3}
{/foreach}

In the past I've done something like this:

Code: Select all

$query = "SELECT * FROM ".cms_db_prefix()."module_".$this->GetName()."_table";
$result = $db->Execute($query);
while($result && $row = $result->FetchRow())
{
  $onerow = new stdClass;
  $onerow->column_1 = $row['column_1'];
  $onerow->column_2 = $row['column_2'];
  $onerow->column_3 = $row['column_3'];
  $entryarray[] = $onerow;
}
$smarty->assign('items',$entryarray);

I don't like this method because it requires that I remember all of my column names and if I change a column name the whole thing breaks. I'm trying to come up with a better solution. Here are two solutions.

Solution 1:

Code: Select all

$query = "SELECT * FROM ".cms_db_prefix()."module_".$this->GetName()."_table";
$result = $db->Execute($query);
while($result && $row = $result->FetchRow())
{
  $onerow = new stdClass;
  foreach($row as $key => $item)
  {
    $onerow->$key = $item;
  }
  $entryarray[] = $onerow;
}
$smarty->assign('items',$entryarray);

This works good but I don't like doing loops within loops. I feel like this can be really memory intensive with large queries.

Solution 2:

Code: Select all

$query = "SELECT * FROM ".cms_db_prefix()."module_".$this->GetName()."_table";
$result = $db->Execute($query);
while($result && $row = $result->FetchRow())
{
  $onerow = new stdClass;
  $onerow->fileds = $row;
  $entryarray[] = $onerow;
}
$smarty->assign('items',$entryarray);

I think I like this solution better because it still gives me access the raw data in my smarty template like this:

Code: Select all

{$item->fields.column_1} 

In addition I don't have to do a loop within a loop.

Which technique do you like better and how do you do this?

[Wishbone]

Do it without a foreach loop:

Code: Select all

$query = "SELECT * FROM ".cms_db_prefix()."module_".$this->GetName()."_table";
$result = $db->Execute($query);
$entryarray = $result->GetRows();          // Get all rows ;)
$smarty->assign('items',$entryarray);

In Smarty:

Code: Select all

{foreach from=$items item=item}
 {$item.column_1}
{/foreach}