CODEWITHSUNDEEP
phpjavascriptmysqlsqldatabase
rohan_vg
rohan_vg

Reputation: 1129

Automatically create entries in database table (php/mysql)

I am working on a project which keeps a record of lets say amount of money spent in a day. Lets say the beginning day(first entry) in the database is 1-Jan-2013. Now everyday I will submit an entry to the database but on some days I cant. The table looks like this:

Date______|______Spent

2013-01-09 | 2000

2013-01-11 | 1200

Lets suppose today is 11-jan-2012 and I forgot to make an entry on 10-jan-2012. Now I want to write the code using php+mysql, which will go through the table and check if there are any dates missing between the first entry and the latest entry. And when it finds that the entry for the day 10-jan-2012 is missing, it should create the entry itself with amount 0 in the spent column. How can I achieve this? Is there any better approach then the one I am trying to use?

Upvotes: 1

Views: 1954

Answers (4)

sgeddes
sgeddes

Reputation: 62841

You could create a stored procedure and schedule it to run nightly. Something like create cursor to loop through the table, order by the Date column, then check the previous record to see how many days are missing, if more than 1, insert individually.

Upvotes: 0

Maulik Shah
Maulik Shah

Reputation: 421

here is my suggestion,create one file that will store the last date of the script run,suppose today(2013-01-11) you have run that script than it will store simply 2013-01-11 in that file,if you run it again tomorrow it will store tomorrow's date 2013-01-12.

we will use this date to create query and reduce our processing time.

date stored in file is our $minDate,

find out max date from db using query say it is $maxDate,

query:Select Date,Spent from table Where date('Date')>$minDate;

Store result in a array $dateArray

create one date array using period between $minDate and $maxDate

$starting_date = new DateTime($minDate." 00:00");
$ending_date = new DateTime($maxDate." 23:59:59");


$interval = new DateInterval('P1D');
$period = new  DatePeriod($starting_date , $interval, $ending_date);
foreach ($period as $date) {
  $curDate=$date->format('Y-m-d');
  if(!in_array($curDate,$dateArray)){
     //insert new row with $curDate
  }
}
update lastProcessedDate in your file

Upvotes: 1

mpaepper
mpaepper

Reputation: 4022

I would approach this like this: Create a cronjob which runs your PHP script in the early morning hours (say 2am or so). The script will take the current date minus 1 (yesterday) and use a select query to the database. If it does not find an entry, it will create one. When you have it running every day, you will make sure, that there is always an entry of yesterday.

Upvotes: 0

Kermit
Kermit

Reputation: 34063

Only way to accomplish this is to JOIN on a table that has a full calendar or make a table of dates. You could write a cursor, but it's considered a last resort.

Upvotes: 2

Related Questions