[SOLVED] PHP Balance ?

[nathan1]

Hi everyone,

I have two tables orders and payments, i want to know the balance, i have done the following;

 

<?php
$c = $ro['user_id'];
//total payments
$p_result = mysql_query("SELECT * FROM payment WHERE user_id=$id") or die(mysql_error());

$ptotal = 0;

while ($p_ro = mysql_fetch_assoc($p_result)) {
$ptotal += $p_ro['payment'];
}

//total debit


$debit_result = mysql_query("SELECT * FROM hdebit WHERE user_id='$c' ") or die(mysql_error());

$total = 0;

//going to use num row to use while loop

$num_rows = mysql_num_rows($debit_result);


while ($num_rows = mysql_fetch_array($debit_result)){

//got to get event details
$event_id = $num_rows['event_desc'];
$trans_fee = $num_rows['price_two'];

if ($trans_fee == 'yes'){

$tq = mysql_query("SELECT * FROM events WHERE event_id=$event_id") or die("Error querying customer database1");
	$dotq = mysql_fetch_array($tq);

$qqq = $dotq['event_price'];


}
//lets get all the event details!

$qq = mysql_query("SELECT * FROM events WHERE event_id=$event_id") or die("Error querying customer database1");
	$doq = mysql_fetch_array($qq);

$ep = $doq['event_price'];


$total += $ep + $qqq;
}

$balance = $ptotal - $total;
?>

 

I am wondering if there is a better way of doing this? It seams to work but when i have multiple entries sometimes i get different answers for 0 - for instance 0.0832783728347e etc

 

Thanks Heaps!

[F1Fan]

I don't know the structure of your tables, and it's cumbersome to try and figure it out, but I would suggest combining queries and using the SUM functions. You can use math in your SQL.

[nathan1]

Hi, thanks for replying -  i just have the numbers/values stored as a double, with a similar structure as;

 

id, user_id, payment, date and for.

 

Thanks

[F1Fan]

You have multiple tables, right? If so, what are the structures of each?

 

Thanks.

[nathan1]

Hi,

The structure for the payment table is this;

-id, user_id, payment, date and for.

 

The stucture for the debit table is this;

-id, user_id, debit, date and for.

 

The structure for the event is;

-id, name, price, description

 

 

Thanks

[F1Fan]

OK, I had to go through your code and figure out what you needed. This is how I understand it:

The total of payment.payment is the total payments made

hdebt.user_id = payment.user_id

hdebt.event_desc = events.event_id

The total of events.event_price is the total debt

if hdebt.price_two equals "yes", double the debt

 

If that is correct, try this:

<?php
$query = "SELECT SUM(a.payments) AS payments, SUM(c.event_price) AS debt, (SUM(a.payments)-SUM(c.event_price)) AS bal1, (SUM(a.payments)-(SUM(c.event_price)*2)) AS bal2, b.price_two FROM payments AS a, hdebt AS b, events AS c WHERE a.user_id = $id AND a.user_id = b.user_id AND b.event_desc = c.event_id GROUP BY b.price_two";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
?>

 

Now $row has all you need. Just add an IF statement that checks $row['prive_two']. If it's "yes," use $row['bal2'] as your balance. Otherwise use $row['bal1'].

[nathan1]

Thanks so much, ill give that a go - didnt know you could do SUMs with MYSQL, thanks so much!!