在数据库表中自动创建条目(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:

我正在做一个项目，这个项目记录了每天花的钱。假设数据库中的开始日期(第一个条目)是2013年1月1日。现在我每天都会向数据库提交一个条目，但有些时候我不能。表格是这样的:

Date______|______Spent

Date______ | ______Spent

2013-01-09 | 2000

2000 | 2013-01-09

2013-01-11 | 1200

1200 | 2013-01-11

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?

假设今天是2012年1月11日，我忘记在2012年1月10日报名。现在我想用php+mysql编写代码，它将遍历表，检查在第一个条目和最新的条目之间是否缺少任何日期。当它发现日期为2012年1月10日的条目丢失时，它应该在已使用的列中创建数量为0的条目。我如何做到这一点?有什么比我正在尝试的更好的方法吗?

4 个解决方案

#1

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.

实现这一点的唯一方法是在具有完整日历的表上进行联接或制作日期表。你可以写一个光标，但它被认为是最后的办法。

#2

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.

我的建议是，创建一个文件来存储脚本运行的最后日期，假设您今天(2013-01-11)运行了该脚本，而不是在该文件中只存储2013-01-11，如果您明天再运行它，它将存储明天的日期为2013-01-12。

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

我们将使用这个日期来创建查询并减少处理时间。

date stored in file is our $minDate,

存储在文件中的日期是我们的$minDate，

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

使用查询查找db的最大日期，假设它是$maxDate，

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

查询:选择Date，从日期(“Date”)>$minDate的表中支出;

Store result in a array $dateArray

将结果存储在数组$dateArray中

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

使用$minDate和$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

#3

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.

我将这样处理:创建一个cronjob，它在凌晨(比如凌晨2点)运行PHP脚本。该脚本将使用当前日期- 1(昨天)并对数据库使用select查询。如果它没有找到一个条目，它将创建一个条目。当你让它每天运行，你将确保，总有一个条目昨天。

#4

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.

您可以创建一个存储过程，并安排它每晚运行。比如创建游标来循环遍历表，按日期列排序，然后检查前面的记录，看看少了多少天，如果超过1，单独插入。

#1