storing database reults in an array problem

[geroido]

Hi

I'm looking for an idea of how to store  results from my  database in an array.  I'm having difficulty finding anything. Here's what bI need. Let's say I'm retrieving results from my database. I want to assign each of these results to an array. I've no problem with retrieving the records but need a hint on how to put these in the array. If I carry out a count of the records and then create an array with that count number, how do I place each record item in an array? Any ideas?

[tibberous]

$result = mysql_query("select * from `videos` where `type`='adult'");
$geroidosCollection = array();

while($video = mysql_fetch_assoc($result)) $geroidosCollection[] = $video;

 

Generally it is a bad idea to put large queries into arrays, especailly if you just want to then loop through that same array and do something.

[geroido]

Hi tibberous

I'm looking for a way to do something and I'm trying to figure it out. Perhaps the array is not the best way. Can I explain the problem? I have a table in my database which looks like this

 

Ordernum  MenuItemID  ItemQuantity itemcost

621              70              3                5.50

621              69              1                14.99

621              53              2                12.99

777              13              4                10.99

777              25              3                11.50   

 

The Ordernum is important here. I want to scan through the table by Ordernum, multipy ItemQuantity by itemcost and them sum the total of the result. So the three results as above would be 16.50, 14.99 and 25.98. The sum of these would be 57.47.  For the second one(777), it would be 43.96 and 34.50. The sum is 78.46. I'm wondering how to do this with a query. I thought I would use an array to capture the distinct Ordernum(621 and 777) then go back through the table multiplying and summing for each unique Ordernum. Is this the best way?

[Barand]

Is this the best way?

 

No.

SELECT Ordernum, SUM(ItemQuantitly * itemCost) as totalCost
FROM tablename
GROUP BY Ordernum

[geroido]

Hi Barand

Sorry for the delay in replying. Thanks for the info. I have a couple of questions. Will this sql statement multiply ItemQuantity by itemcost and add the results together? So in the example provided for Ordernum 621, I need the statement to multiply ItemQuantity * itemcost for all 3 records and add the 3 results together to give me a result for this order. It must do the same for Ordernum the two instances of 777 aswell and so on for any other orders. Can you help?

 

One last thing. I spent ages yesterday trying to find out how to capture the result of a 'sum' in a variable. E.g. you have in your example SUM(ItemQuantity * itemcost). What code do I then need to capture this result. I'm using php. Went through google for hours and no luck.

 

Thanks

[Barand]

$sql = "SELECT Ordernum, SUM(ItemQuantitly * itemCost) as totalCost
         FROM tablename
         GROUP BY Ordernum":
$res = mysql_query($sql);
while (list($ono, $total) = mysql_fetch_row($res))
{
      echo "$ono : $total <br/>";
}