updating multiple values in my database.

[system]

Hi, I am currently working on a web application for travel budgeting. I have managed to get it to insert values into my database from a form, which puts information about how much money has been spent on each category (i.e. Food, travel, entertainment, etc). However, I don’t know how to get the database to update the values in the database fields each time the user enters a new value. By update, I mean add the numbers in the form to the total of each category currently in the database.
so far this is my PHP to insert the data into the database table.

[php]if(isset($_POST[‘addinfo_button’])){
$Food = $_POST[‘food’];
$Transport = $_POST[‘transport’];
$Accom = $_POST[‘accomodation’];
$Entertain = $_POST[‘entertainment’];
$Souvenir = $_POST[‘souvenirs’];
$Misc = $_POST[‘miscellaneous’];
$Date = date(“Y-m-d”);
$Trip_id;
$sql = “SELECT * FROM trips WHERE id =$user_id_session AND date1 <= ‘$Date’ && date2 >= ‘$Date’”;
$records = mysql_query($sql);
while($trip=mysql_fetch_assoc($records)){
$Trip_id = $trip[‘trip_id’];
}
$foreignkey = $user_info[‘id’];

$sql = $con->query("INSERT INTO todays_spend (food, transport, accomodation, entertainment, souvenirs, miscellaneous,date, trip_id, id)Values('{$Food}', '{$Transport}', '{$Accom}', '{$Entertain}','{$Souvenir}', '{$Misc}','{$Date}','{$Trip_id}','{$foreignkey}')");

header('Location: budgetbuddy.php');	

}[/php]

And this is what I have tried for updating it, but doesn’t work:

[php]$sql = $con->query(“UPDATE todays_spend SET food= ‘food+$Food’ transport= ‘transport+$Transport’, accomodation = ‘accomodation+$Accom’, entertainment = entertainment+$Entertain, souvenirs = ‘souvenirs+$Souvenir’, miscellaneous = ‘miscellaneous+$Misc’,date = ‘$Date’, trip_id=’$Trip_id’, id =’$foreignkey’ WHERE trip_id = $Trip_id” );[/php]

[astonecipher]

the database design needs work. Each expense should be it’s own record. What happens if you add a category later? A new column needs adding.

[ultimateIan]

the database design needs work. Each expense should be it’s own record. What happens if you add a category later? A new column needs adding.

Is that definitely the best choice to make each expense its own table? I’m not planning to add any categories, as its only for a project at university not a real product. I’m not experienced in php or sql so need a more experienced person’s opinion. Would you possibly be able to elaborate a little bit more on why that is the best way to do it?
Thank you!

[astonecipher]

I didn’t say put each expense in its’ own table, I said record. A record is a row.

If this is for school, ignore the comment. That is not how you would want it done in a production application.

[ultimateIan]

I didn’t say put each expense in its’ own table, I said record. A record is a row.

If this is for school, ignore the comment. That is not how you would want it done in a production application.

So how would you reccomend it be done?

[astonecipher]

Each expense should be it’s own record.

SELECT SUM(expenseAmount) as TOTAL FROM expenses WHERE userID = 1 AND expenseType = 'food' AND travelDate BETWEEN ('2016-02-01 12:00:00' AND '2016-02-29 12:00:00')

[benanamen]

Depending on a button name being submitted for your script to work will completely fail in certain instances.

This:
if(isset($_POST[‘addinfo_button’]))

Should be: (This will not fail under any conditions)
if ($_SERVER[‘REQUEST_METHOD’] == ‘POST’)