Problem with query and arrays.

ethought

I have 2 tables; t_item and t_type. t_item has no reference to t_type however t_type consists of type_id, item_id, desc, and cost. An item can have more than 1 type so in t_type there may be 6 records all with the same item_id. I need to query and retrieve this information. Currently, I am using the code listed below, however, I believe it is incorrect and would like a shove in the right direction on the best way to resolve it. I need to end up with an array that contains both the records from t_item and each items associated types. Thanks.

$item_results = array();
$result = mysql_query("select * from t_item", $pnp);
while($row = mysql_fetch_assoc($result)){
    $item_results[] = $row;
    $type_results = array();
    $type_r = mysql_query("select * from t_type where item_id = " . $row['item_id]', $pnp);
    while($row2 = mysql_fetch_assoc($type_r)) {
        array_push($row2, $item_results);
    }
}

devinemke

<?php
$result = mysql_query('SELECT * FROM t_item') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$items[$row['item_id']] = $row;}

$result = mysql_query('SELECT * FROM t_type') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$items[$row['item_id']]['types'][] = $row;}
?>

you now have a multi-dimensional array called $items which has the item_id as the keys and an array of each row as the values. addtionally each array index has a subarray of all of the associated types for that item.

ethought

devinemke wrote:
<?php
$result = mysql_query('SELECT * FROM t_item') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$items[$row['item_id']] = $row;}

$result = mysql_query('SELECT * FROM t_type') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$items[$row['item_id']]['types'][] = $row;}
?>
you now have a multi-dimensional array called $items which has the item_id as the keys and an array of each row as the values. addtionally each array index has a subarray of all of the associated types for that item.

Thanks, that seemed to do what I wanted. Although now I am trying to get that to translate into a Smarty {section} tag. I've asked about it on the Smarty forums, but if anyone here can get me started, that would be great. Doing the following isn't work with {section}

{section name=item loop=$items}
{$items[item].title}
{/section}

Thanks.

programmer79

You should first assign your array to a Smarty variable and then display a template file even if it is empty. Make sure the Smarty debug is enabled and you will be able to see a visual representation of the array. Once you have this it is pretty easy to manipulate the values via the {section} function. If you have problems once you get this far let me know. I am very comfortable with Smarty and should be able to help you out.

Thanks,
Tony