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  | [email protected]   |    4 
    2   | another  | [email protected]   |    3
    3   | lastone  | [email protected]   |    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? — user895378
– user895378, Commented Dec 8, 2011 at 18:13
Thats what I'm looking for, it now seems a lot simpler that what I had originally thought! — Oliver Whysall
– Oliver Whysall, Commented Dec 8, 2011 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. — user895378
– user895378, Commented Dec 8, 2011 at 18:21

Brad · Accepted Answer · 2011-12-08 18:11:45Z

2

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.

answered Dec 8, 2011 at 18:11

Brad

164k57 gold badges380 silver badges559 bronze badges

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

Comments

Kevin Vandenborne · Accepted Answer · 2011-12-08 18:14:45Z

1

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

answered Dec 8, 2011 at 18:14

Kevin Vandenborne

1,3871 gold badge10 silver badges28 bronze badges

Comments

Brad · Accepted Answer · 2011-12-08 18:12:22Z

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 8, 2011 at 18:12

Brad

164k57 gold badges380 silver badges559 bronze badges

answered Dec 8, 2011 at 18:12

Stainedart

1,9994 gold badges32 silver badges55 bronze badges

Comments

user1088172 · Accepted Answer · 2011-12-08 18:30:36Z

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.

Collectives™ on Stack Overflow

Maths with mySQL PHP database

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related