CODEWITHSUNDEEP
phpmysqldatabasemathequations
Oliver Whysall
Oliver Whysall

Reputation: 349

Maths with mySQL PHP database

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

Upvotes: 0

Views: 1047

Answers (4)

user1088172
user1088172

Reputation:

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.

Upvotes: 0

Kevin Vandenborne
Kevin Vandenborne

Reputation: 1387

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

Upvotes: 1

Stainedart
Stainedart

Reputation: 1979

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;

Upvotes: 0

Brad
Brad

Reputation: 163438

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.

Upvotes: 2

Related Questions