mitchel mitchel - 4 months ago 17
SQL Question

Add new array key inside function

I have an array that is populated by a mysql_fetch_assoc. After the array is populated by the columns I want from the database, I would like to add another key to the array, using values that I obtain earlier in the function. I tried to accomplish this with a foreach loop, but my function is not returning the array. Where have I gone wrong?

//calculates payout
function calculate_payout($id){
$result = mysql_query("SELECT `result` FROM `bets` WHERE `id` = {$id}");
echo mysql_error();
$wager_total = mysql_query("SELECT SUM(`wager_amount`) AS `wager_total` FROM `wagers` WHERE `id` = '{$id}'");
$correct_wager_total = mysql_query("SELECT SUM(`wager_amount`) AS `correct_wager_total` FROM `wagers` WHERE `id` = '{$id}' AND `wager_option` = '{$result}'");
echo mysql_error();
$incorrect_wager_total = $wager_total - $correct_wager_total;

$sql = " SELECT * FROM `wagers` WHERE `id` = '{$id}' AND `wager_option` = '{$result}'";
echo mysql_error();
$data = mysql_query($sql);

$rows = array();
while(($row = mysql_fetch_assoc($data)) !== false){
$rows[] = array(
'bet_id' => $row['bet_id'],
'id' => $row['id'],
'wager_amount' => $row['wager_amount']
);
}

foreach ($rows as $p_row){
$payout = $p_row['wager_amount'] / $incorrect_wager_total;
$payout = $p_row['wager_amount'] + $payout;
$p_row['payout'] = $payout;
}

return $p_row;
}

Answer

The problem is that $p_row is a copy of the row in the array, so modifying it in the loop doesn't have any effect on the original array.

You can fix this by using a reference in the foreach:

foreach ($rows as &$p_row)

Or you can just do this as you're creating the $rows array in the while loop:

while ($row = mysql_fetch_assoc($data)) {
    $new_row = array(
        'bet_id'         => $row['id'],
        'id'             => $row['id'],
        'wager_amount'   => $row['wager_amount'],
        'payout'         => $row['wager_amount'] / $incorrect_wager_total + $row['wager_amount']
    );    

Also, your return statement is wrong, it should be return $rows; to return the whole array; return $p_row will just return the last row of the array.