Difference of 2 sums

[robbo5899]

I have 2 tables, one named donations and the other named costs. Both have a common column, Amount, I want to Sum each column then take costs away from donations.

 

Is this possible?

 

Thanks for any help.

 

Robbie

[requinix]

So they have anything else in common? Would it make sense to combine the tables into one, then "donations" have Amount>0 and "costs" have Amount

Edited May 28, 2013 by requinix

[robbo5899]

The tables look like this,

 

Donations has ID Name Date Amount

Costs has ID Name Date Payment_to Server Description and Amount

 

This is the code i have so far but it just prints 0 even although there is data in the tables.

<?php
$con = mysql_connect("ipaddress","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("rwscnnpe_Donations", $con);
 
$cost = 'SELECT sum(amount) FROM costs';
$donations = 'SELECT sum(amount) FROM donations';
$total = $donations - $cost;
echo $total;
?>

Edited May 29, 2013 by robbo5899

[Jessica]

Probably because you're subtracting one string from another string. You've never run the queries, or fetched the data.

[robbo5899]

Well as my title suggests I'm a newbie. This is the first thing I've ever written in php. I am here asking for help on how to fix it not whats wrong with it. Can you explain further?

[Jessica]

All you've done is just write out two strings, and try to subtract one from the other. You'll need to actually execute the queries and then fetch the data.

 

If you don't know how to do that, try googling for a basic MySQL/PHP tutorial. Look for one that uses PDO or mysqli_, not mysql_ functions. Also read the manual, the basic examples in there are really all you'll need.

[robbo5899]

When i search how to execute a SQL query using php they all say connect to your server then SELECT column_name FROM table_name

[Jessica]

That's if you're using the MySQL console or a tool like MySQL Workbench or phpMyAdmin.

 

See how you called some functions to connect and select your database? You need the appropriate functions to run your query.

(You'll want to switch to using PDO/mysqli)

[Barand]

try

$db = mysqli_connect(HOST, USERNAME, PASSWORD, DATABASE); // connect to database

$costSql = 'SELECT sum(amount) FROM costs';         // define the query
$result = mysqli_query($db, $costSql);              // execute the query
$row = mysqli_fetch_row($result);                   // get return row
$costs = $row[0];                                   // get the total

$donationsSql = 'SELECT sum(amount) FROM donations'; // define the query
$result = mysqli_query($db, $donationsSql);         // execute the query
$row = mysqli_fetch_row($result);                   // get return row
$donations = $row[0];                               // get the total

$result = $donations - $costs;

[robbo5899]

Thanks a bunch Barand, that worked perfectly.