CODEWITHSUNDEEP
phpmysqldate
DavidF
DavidF

Reputation: 265

Add a new row when date (day) change - MySQL, PHP

I need some support as I'm not so skilled in PHP and MySQL like all in this board :) So, thanks in advance.

I'm storing data from an external parsed XML feed with a PHP script. This script update MySQL database with a CRON Job twice a day. This is a snipped of such PHP script:

mysql_query("UPDATE my_table SET amount=$jackpot->amount WHERE gameId = '".$jackpot->gameId."'")
or die(mysql_error());

This update a table and a row in my database. This table is structured like this (columns):

gameId | gameName | amount | currency | date

where the "date" column is in DATE format (dd/mm/yyyy hh:mm), CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

What I need... I need, yes, to update this table twice a day but I need also to INSERT a new row with the same columns at DATE change (every day, new day->new row).

I'm thinking in something similar, in two words (no coding):

if today --> update rows
elseif date change (another day, so "tomorrow") --> INSERT new row with updated data (collected from the XML feed)

How can I do this? Have I to check the current data with a MySQL query and then INSERT a new row via PHP? Or can I skip the query with a bunch of PHP lines?

Sorry, but I'm a little bit confused... Any help would be really appreciated!

Again, thanks to all!

Upvotes: 0

Views: 2697

Answers (3)

Oliver A.
Oliver A.

Reputation: 2900

You should use a query to check if there already is a matching row unless you are absolutely certain that no new games are added.

If your script runs at 1 am and 1 pm and you only create a new rows at 1 am, adding a new game 2 am (in the external xml, your backend or both) will result in an error.

Upvotes: 0

RobMasters
RobMasters

Reputation: 4148

If I understand correctly, you'd like to be able to insert/update data twice a day (although the number of times shouldn't be important) and end up with a single row of data per unique date?

You could achieve this in a single query if you create a UNIQUE index on your date column and use the INSERT...ON DUPLICATE KEY UPDATE syntax for your query. e.g.

INSERT INTO my_table (amount, currency, date) VALUES (....)
ON DUPLICATE KEY UPDATE amount = '....';

You can easily increment the values of columns rather than re-assigning them if you wish. To do this, the update part of the query would be like: UPDATE amount = amount + ....

Upvotes: 2

Marc B
Marc B

Reputation: 360762

I'd suggest keeping it simple: add a new dupedate field to store ONLY the date of insertion. Put a unique key on it, then it's a simple matter of:

INSERT INTO (gameID, gameName, ammount, currency, date, dupedate) VALUES (...)
ON DUPLICATE KEY UPDATE amount=xxx

If you "insert" a record with the same date as an existing record, you get an update. If it's a new date (e.g. 'tomorrow'), you'll get a new record instead.

This also has the advantage of not being a racey as your initial "fetch/check/update|insert" version. Such a process leaves open the possibility that some OTHER process may duplicate the logic and snipe away your new record from under you in between the time you fetch the record and when you do the insert/update.

Upvotes: 2

Related Questions