Possible? DB check for balance due

[WatsonN]

I have a custom finance management site and within it I can see amount owed paid and total account balance.

I have a table called `Transactions` with 8 fields

ID - unique post ID

UID - User ID

name - Name of the person or business

amount - amount of the transaction, in the format of -100.00 or +100.00

date

category

comment

delete - default 0

 

What I want is to check every unique name and add up the totals and if it totals up to a negative send out a email.

I already have a PHP script for the user to see this list, I just want to know if there is some way possible to automate this weekly via cron job.

 

Is it possible?

Thanks in advanced!

[btherl]

Yes it's possible.  Your cron job can do the same as your existing php script does, but for every user. 

 

One way is to do one query to get the usernames and then do one query for each user to get the balance.

 

Another way is to use a single query grouped on username, fetching the sum of the totals for each user.  This requires a bit more effort on the SQL but means you get everything in a single query.

[WatsonN]

Thank you btherl

 

I've written the begiging and I already have the problem with the array. It will only print the first result

 

<?php
require('config.inc.php');

$Names = mysql_query("SELECT COUNT( * ) AS  `Rows` ,  `name` 
FROM  `Transactions`
WHERE `delete` = '0' AND `UID` = '2' 
GROUP BY  `name` 
ORDER BY  `name` 
LIMIT 0 , 6000") or die(mysql_error()); 

$names_array = mysql_fetch_array($Names);

print "<pre>";
print_r($names_array);
print "</pre>";
?>

[btherl]

mysql_fetch_array() should be used in a loop, as in the examples here: http://php.net/manual/en/function.mysql-fetch-array.php

 

eg

 

while ($row = mysql_fetch_array($Names)) {
  print "{$row['name']}: {$row['rows']}\n";
}

 

You will probably want sum(amount) in your final query.

 

[WatsonN]

That would make sense. .

 

I've read over the array function and am not sure how to put all the data into a variable so I can call each item in the array to add up balances.

 

I'm sure this is something simple I'm missing

[btherl]

Do you want to add the balances in SQL or in PHP?

[WatsonN]

PHP Its what I've used for the User side

while ($info = mysql_fetch_assoc($data_name)) { 
$sum_name 		+= $info['amount'];
}

[ignace]

Get all accounts with a negative balance instead of retrieving all accounts:

 

$sql = 'SELECT UID, sum(amount) FROM Transactions GROUP BY UID HAVING sum(amount) < 0';
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)) {
    // ..
}

 

Use mysql_fetch_assoc() or mysql_fetch_row() instead of mysql_fetch_array()