Maths with mySQL PHP database

Question

I'm looking into doing some maths to work out the total amount of 'credits' all users in a database have.

I have a database with a table called users with userid, username, email and credit columns. I was wondering if there is a way of diplaying all the results in a table like userid, username, email and credits as table colums and then at the bottom have the total of all user credits:

 UserID | Username | Email Address | Credits
 -------+----------+---------------+--------
    1   | example  | e@mail1.com   |    4 
    2   | another  | e@mail2.com   |    3
    3   | lastone  | e@mail3.com   |    1
 -------+----------+---------------+--------
        |          | Total Credits |    8
 -------+----------+---------------+--------

I was wondering if this was possible and any other math equations you can do with mySQL databases and PHP

Oliver

If you want to simply retrieve the rows from the database you can easily do that with php and loop over the output to print it in table format. It's just as simple to sum the values of the Credits column as you iterate over the db results for printing. Is this what you're asking? — , Dec 08 '11 at 18:13
Thats what I'm looking for, it now seems a lot simpler that what I had originally thought! — Oliver Whysall, Dec 08 '11 at 18:19
Well, there are a million existing tutorials for how to retrieve data from a database in PHP and loop over the results. Google (or SO search) is your friend. — , Dec 08 '11 at 18:21

score 2 · Answer 1 · answered Dec 08 '11 at 18:11

If you need to do this all in one query, you can use WITH ROLLUP in certain conditions.

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

In particular, read the comments at the bottom of that link for examples related to what you are trying to do.

Personally, I prefer to do aggregates separately, or on the application side.

score 1 · Accepted Answer · answered Dec 08 '11 at 18:14

1

You can use SUM(Credits) as Total Credits. More info here

answered Dec 08 '11 at 18:14

Kevin Vandenborne

1,397
1
10
28

score 0 · Answer 3 · edited Dec 08 '11 at 18:12

0

You could do :

select UserId, Username, EmailAddress, Credits from users order by userid
union
select '' as UserId,'' as Username,'Total Credits' as email,sum(credits) as credits from users;

edited Dec 08 '11 at 18:12

Brad

159,648
54
349
530

answered Dec 08 '11 at 18:12

Stainedart

1,929
4
32
53

score 0 · Answer 4 · answered Dec 08 '11 at 18:30

First of all all you need to do is just create the database in MySQL, if you don't know how to create here's the syntax all you need to do just run the query but don't forget to create the database first.

create table mhs ( UserID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, example varchar(20), Email_Address varchar(30), Credits INT )

After you execute the database now we just need to configure it with the php file.

Here's the list of code in php you can choose anyway to write your own code

<?php

showdatame();
function showdatame()
{
$inttotalcredits=0;
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("praktikum", $con);

$result = mysql_query("SELECT * FROM mhs");

echo "<table border='1'>
<tr>
<th>UserId</th>
<th>UserName</th>
<th>Email Adress</th>
<th>Credits</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row[0] . "</td>";
echo "<td>" . $row[1] . "</td>";
echo "<td>" . $row[2] . "</td>";
echo "<td>" . $row[3] . "</td>";
$inttotalcredits += $row[3];
echo "</tr>";
}
echo "<td>" . "" . "</td>";
echo "<td>" . "" . "</td>";
echo "<td>" . "Total Credits" . "</td>";
echo "<td>" . $inttotalcredits . "</td>";

echo "</table>";
mysql_close($con);   
}

?>

I hope this quite help you.

Maths with mySQL PHP database

4 Answers4