Encode JSON From MySQL Fetch Assoc & Fetch Array

Question

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.

Linus Kleen · Accepted Answer · 2012-06-14 14:53:02Z

7

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

edited Jun 14, 2012 at 14:53

Linus Kleen

34.8k10 gold badges97 silver badges100 bronze badges

answered Jun 14, 2012 at 14:48

Sherlock

7,6356 gold badges42 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Eugen Rieck Over a year ago

You beat me by 19 seconds ... +1

Sherlock Over a year ago

And I even added an explanation! ;)

Rem.co Over a year ago

And added braces for clarity ;)

Saint Robson Over a year ago

Guys, TotalSchoolTuition now have this number format : 6000.0000. there are 4 zeros as decimal number. how to remove this? FYI, NumberOfChildren and SchoolTuition are Integer on SQL structure. but why the result of calculation produce decimal number? thanks.

Eugen Rieck Over a year ago

... which is why I gave a +1 ;-)

|

Eugen Rieck · Accepted Answer · 2012-06-14 14:49:05Z

3

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

answered Jun 14, 2012 at 14:49

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Collectives™ on Stack Overflow

Encode JSON From MySQL Fetch Assoc & Fetch Array

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related