Table Math

[ririe44]

Okay... I need some math help here...

 

I have a table with columns 'budget' and 'balance'.  When I run my form and hit "submit", I want the value in 'budget' to be added to 'balance'... for the entire table.  Any ideas?

[premiso]

SELECT (`budget`+`balance`) as BudgBal FROM table_name WHERE condition = somecondition

 

Or an update:

UPDATE table_name SET `balance` = (`balance`+`budget`) WHERE condition = somecondition

 

Or from a variable:

UPDATE table_name SET `balance` = (`balance`+ $budget) WHERE condition = somecondition

[ririe44]

Could I just write this to add the rows individually?:

UPDATE table_name SET `balance` = (`balance`+`budget`)

 

Or do I need to add the WHERE condition to make it unique to the row?

[Ken2k7]

Without the WHERE clause, it will update all balance in the table. If that's what you want, then yeah.

[ririe44]

But it would update all 'balances' with the same associated row 'budget' amount, right?

 

Would I code it in like this?  I'm not sure where to make the $add_period_balance work?

 

$query = "INSERT INTO `$tbl_name` (`id`, `period`, `start`, `end`) VALUES ('NULL','".$period."','".$start."','".$end."')";

$add_period_balance = "UPDATE `tbl_name2` SET `balance` = (`balance`+`budget`)";

if (!mysql_query($query));
{
echo "1 Period added.  Make another entry?  <a href='entries.php'>Yes</a>";
}
die(mysql_error());

?>

[Ken2k7]

Is there any relationship between the table you're inserting values in and table2? Are they the same table?

[ririe44]

no... different tables... one is called periods, the other is called accounts

[Ken2k7]

Say you're inserting into periods. Do you need to run an update on accounts? I mean, nothing changed in account right? So why do you want to run an update there?

[ririe44]

I'm inserting a new row into periods, and I'm updating accounts...

 

Basically, when I complete the form to collect the information for a new period, I also need the accounts table to add a value to the account balance for the new period. 

[Ken2k7]

I am beyond confused. Example please!

[ririe44]

Okay, let's see...

 

I have an html form called "Add a Period" (basically just to add a pay period into a personal finance workbook)... the form asks for the period title, period start date, and period end date...

 

Period Title:  _________  Period Start: __________  Period End: _____________

Submit btn

 

I also have an accounts table, which is basically a budget.  Each account, or budget item (such as mortgage, groceries, etc) has a pay period spending limit... the budget.  I am calling these budget items accounts because I plan to have the account maintain a balance...  So, every new pay period which is started, I want to add the budgeted amount to the account balance... 

 

So, let's say groceries budget is $150, and there's an account balance of $23 when I start my new pay period.  Once I enter my info into the form above, I hit submit.  It send the information to the periods table to generate the new row in periods.  Upon submit, I'd also like it to add $150 to the current $23 in the grocery  account. 

 

So, after I've clicked submit, I should now have a new pay period, and all my accounts (budget items) should have an increase by the budgeted amount.

 

I hope this makes sense now.

 

So, you can see below the $query is inserting a new row into my periods table... and I was hoping to get $add_period_balance to add the budgeted amount to the balance category of my accounts table...

 

$query = "INSERT INTO `$tbl_name` (`id`, `period`, `start`, `end`) VALUES ('NULL','".$period."','".$start."','".$end."')";

$add_period_balance = "UPDATE `tbl_name2` SET `balance` = (`balance`+`budget`)";

if (!mysql_query($query));
{
echo "1 Period added.  Make another entry?  <a href='entries.php'>Yes</a>";
}
die(mysql_error());

?>

 

 

[Ken2k7]

Sorry to be a pain, but can you lay out the table structures for each table? I guess I'm not understanding. Maybe someone else understands.

 

So you're inserting a row into periods. It has id, period, start and end fields. You're not inserting anything into accounts, so I don't see where balance and budget is coming from. Maybe the table structures will help. I'm not sure.

 

Thanks!

[ririe44]

accounts has the following columns:

id, category, sub_category, budget, and balance...

 

for example...

 

id, cat, sub, budget, balance

1, food, groceries, 150, 23

2. home, electricity, 30, 12

etc.

 

I want to insert the new row into table periods, and add the budget amount to balance so it would end up like this...

 

id, cat, sub, budget, balance

1, food, groceries, 150, 173

2. home, electricity, 30, 42

etc.

 

[Ken2k7]

Oh yeah, you can run that SQL then. If you're worried about any mess ups, run

 

SELECT `balance` + `budget` AS `bal` FROM `table2`

 

See if that selects the right values.

[ririe44]

So, how would I modify this to do what I want?

 

$query = "INSERT INTO `$tbl_name` (`id`, `period`, `start`, `end`) VALUES ('NULL','".$period."','".$start."','".$end."')";

$add_period_balance = "UPDATE `tbl_name2` SET `balance` = (`balance`+`budget`)";

if (!mysql_query($query));
{
echo "1 Period added.  Make another entry?  <a href='entries.php'>Yes</a>";
}
die(mysql_error());

?>

[Ken2k7]

mysql_query($add_period_balance);

?

 

Make sure you ran the SELECT statement above to verify the data you get back are correct. Better yet, run this:

 

SELECT `balance`, `budget`, `balance` + `budget` AS `bal` FROM `accounts`