I am trying to create an associative array in PHP.
The idea is to store an "item Id" as the array key and the total spend on that item Id as the value.
I am using the following code to get data out of my MYSQL two separate tables, one that contains the item info and one that contains a list of expenses.
// GET ITEM DATA FROM DB
$getItemData = mysql_query("SELECT * FROM items;");
// CREATE ARRAY
$orderItemsBySpend = array();
// LOOP THROUGH ITEMS AND CALCULATE SPEND IN LAST 30 DAYS
while($itemData = mysql_fetch_assoc($getItemData)){
$getItemExpenses = mysql_query("
SELECT SUM(cost)
AS productExpenses
FROM expenses
WHERE itemId = $itemData[id]
AND date BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();");
$itemExpenses = mysql_fetch_assoc($getItemExpenses);
// SET TOTAL SPEND VARIABLE
$totalProductSpend = $itemExpenses['productExpenses'];
// SET ITEM ID VARIABLE
$itemId = $itemData[id];
// ADD KEY AND VALUE TO ARRAY
$orderItemsBySpend[$itemId] = $totalProductSpend;
// PRINT ARRAY FOR TESTING PURPOSES
print_r($orderItemsBySpend);
}
Unfortunately instead of getting something like this, as I would expect (and am hoping for!)
Note the 0.00 values are only down to the fact there is only limited data in the expenses table.
Array (
[1] => 14.01
[2] => 0.00
[3] => 0.00
[4] => 0.00
[5] => 0.00
[6] => 20.24
[7] => 0.00
[8] => 0.00
[9] => 0.00
)
Instead I am getting this:
Array (
[0] => 14.01
)
Array (
[0] => 14.01
[1] => 0.00
)
Array (
[0] => 14.01
[1] => 0.00
[2] => 0.00
)
Array (
[0] => 14.01
[1] => 0.00
[2] => 0.00
[3] => 0.00
)
etc.
My question is: Why is this strange "incremental pattern" of arrays being created? And why is the array key not being set as the item Id?
The first array should have key of 1 (the first item Id), not 0.
All advice appreciated!
Thanks,
Bruce.