1
id  1stPayment  2ndPayment  3rdPayment  4thPayment  Tuition 
8   0           200         2000            2000            9000    
8   2000        0           0               0                 0
9   0           0           0               0               1000    
10  1           0           0               0               0

i want to add all the tuition of id-8 and echo the result of the sum of the tuition. how can i sum all the tuition with out adding the tuition of other id's. the table name is "students_payments"... "i also want to echo the tuition of an id in its own page, like when i access the account of id-8 it shows the sum of its tuition. :D

i have this code, but when i access the account of id-9 and id-10 it shows the added value of all the tuition. tnx in advanced.. :D 

<?php

include("confstudents.php");
$id = $_GET['id'];

$result = mysql_query("select * from student_payments where id='$id' ");
while ($res = mysql_fetch_assoc($result)) {

    $result = mysql_query("SELECT SUM(Tuition) FROM student_payments");
    while ($row = mysql_fetch_assoc($result)) {
        $TT = $row['SUM(Tuition)'];
        echo "Php $TT";
    }
}
?>
Charles
  • 50,943
  • 13
  • 104
  • 142
Anna Mae
  • 95
  • 1
  • 10
  • 1
    [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://www.brightmeup.info/article.php?a_id=2). – NullPoiиteя Jan 30 '13 at 12:39
  • 1
    your code is wide open for sql injection – NullPoiиteя Jan 30 '13 at 12:43
  • sql injection? i have no idea what that means – Anna Mae Jan 30 '13 at 13:13
  • Than you really need to know http://en.wikipedia.org/wiki/SQL_injection – NullPoiиteя Jan 30 '13 at 13:17
  • And normalize your data. – Strawberry Jan 30 '13 at 13:28

3 Answers3

1

You query should be 

SELECT SUM(Tuition) as TotalTuition FROM student_payments WHERE id='$id' GROUP BY id

Then you can just echo TotalTuition.

Warning

your code is vulnerable to sql injection you need to escape all get and post and the better approach will be using Prepared statement

Good Read

  1. How to prevent SQL injection in PHP?
  2. Are PDO prepared statements sufficient to prevent SQL injection?

Note

  1. The entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_, is officially deprecated as of PHP v5.5.0 and will be removed in the future. So use either PDO or MySQLi

Good read

  1. The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead
  2. PDO Tutorial for MySQL Developers
  3. Pdo Tutorial For Beginners
Community
  • 1
  • 1
RisingSun
  • 1,693
  • 27
  • 45
  • thanks pal :) i'm new in php, and new here in stackoverflow, inever thought i could get a response so quickly. :) thanks, gbu :D – Anna Mae Jan 30 '13 at 12:51
  • No problem. Glad to help anytime. If my answer was the one you were looking for, dont forget to accept :D – RisingSun Jan 30 '13 at 12:54
  • What do you mean by name date? – RisingSun Jan 30 '13 at 13:26
  • i have another question, hmm. if there's another row that is named date every example row 1>id-8 13-12-12 row 2>id-8 13-12-26,,, how can i echo the date that has a date which is earlier than the other.. – Anna Mae Jan 30 '13 at 14:00
0
<?php

include("confstudents.php");
$id = $_GET['id'];
$result = mysql_query("SELECT SUM(Tuition) FROM student_payments where id='$id'");
while ($row = mysql_fetch_array($result)) {
    $TT = $row['SUM(Tuition)'];
    echo "$TT";
}
?>
NullPoiиteя
  • 56,591
  • 22
  • 125
  • 143
krizna
  • 1,535
  • 12
  • 13
0

A few things about your code:

Always cast data to what you expect them to be (in the case of your id, that should be an integer).

Never put any unescaped strings into SQL queries. You never know what people type into your applications input fields. In this case I don't use mysql_escape, as the id was casted to integer, which is of no harm to the query.

Never (!) use mysql_query in a loop. You never need it and it will always slow down your application without providing any use.

If your database expects an integer, then give it an integer and not a string. id is expected to be an integer, but '$id' will always be a string. Unfortunately MySQL silently tries to cast this to integer instead of complaining...

As I am very picky: id is an abbreviation for identifier, which in turn means, that you can identify something by it. Resulting from that, an identifier must always be unique. I hope you chose it merely to explain your question.

Use ' instead of " for strings wherever you can. This will keep the PHP parser from trying to interpret the string. Makes your code a little more save and faster.

Though mysql_* functions are deprecated, I have only extended your code. So for an answer to your question see the code below.

<?php

include("confstudents.php");
$id = (int)$_GET['id'];  // cast to int to prevent SQL injection; if you can't do that (e.g. it is a string), use mysql_escape()
if ($id <= 0) {   // check if the id is at all valid and break here if it isn't
    die('Invalid ID');
}

$result = mysql_query('SELECT SUM(tuition) sum_tuition FROM student_payments WHERE id = ' . $id);
if ($result === FALSE) {   // Check if the statement was able be processed
    die('Error in SQL statement');   // break here, if it wasn't
}
$res = mysql_fetch_assoc($result);   // with the SQL above, there's always exactly one row in the result
echo 'Php ' . $res['sum_tuition'];
?>

You can add some more debugging code such as mysql_error() to find errors in your SQL statements. But don't display that to your users. They might know, how use it for exploiting your application...

D. E.
  • 106
  • 1
  • 1
  • 9