Saint Robson
Saint Robson

Reputation: 5525

Encode JSON From MySQL Fetch Assoc & Fetch Array

I have this PHP syntax and works as I expected :

$sqldata = mysql_query("
SELECT FirstName, LastName, NumberOfChildren, SchoolTuition FROM User WHERE User.Username = '$Username' LIMIT 1;
");

$rows = array();
while($r = mysql_fetch_assoc($sqldata)) {
  $rows[] = $r;
}

echo json_encode($rows);

now, the problem is I want to calculate total school tuition (NumberOfChildren * SchoolTuition) and it must be done before json_encode() function. and my client doesn't want to add one extra column on their database.

so I must calculate this TOTAL School Tuition using PHP and put it back on $rows array as latest value of array before json_encode.

I tried calculate using mysql_fetch_array first to get $rows[2] and $rows[3], but since I already use mysql_fetch_assoc before, this mysql_fetch_array won't work.

last thing, I mysql_fetch_assoc generates 2 dimensional array. because when I use print_r($rows), I see this :

Array ( [0] => Array ( [FirstName] => Mary [LastName] => Smith [NumberOfChildren] => 3 [SchoolTuition] => 2000 ) ) 

while array_push function only used to push 1 dimensional array, right?

please kindly give me solution of this problem. thank you very much.

Upvotes: 0

Views: 2227

Answers (2)

Sherlock
Sherlock

Reputation: 7597

You can just do the calculation in SQL, without having to add the column.

SELECT FirstName, LastName, NumberOfChildren, SchoolTuition, 
    (NumberOfChildren * SchoolTuiton) AS TotalSchoolTuiton 
FROM User WHERE User.Username = '$Username' LIMIT 1

Upvotes: 7

Eugen Rieck
Eugen Rieck

Reputation: 65294

SELECT FirstName, LastName, NumberOfChildren, SchoolTuition, NumberOfChildren*SchoolTuition AS TotalTuition FROM User WHERE User.Username = '$Username' LIMIT 1;

Upvotes: 3

Related Questions