Struggling with logic for checking consecutive data entries
<p>I'm trying to figure out how to handle a tricky little situation I've found myself in this morning. I have an entries table in my database where I store details about users' monthly entries (information capture stuff) - I want to increment the number (not the ID) of each entry once a month has passed. The idea is to use the "number" field to be able to identify consecutive monthly entries and to disregard entries within close proximity to one another.</p>

<p>When a user visits the site to start a new entry, I check the date of the last entry completed to see if it is more than 21 days ago (which qualifies as being a valid month) then I increment the "number" for this new entry. The problem is that I can end up with a sequence of entries which are all less than 21 days apart (and thus all have the same number), but collectively span more than 21 days! I need to be able to find some logic to handle this - anyone have any ideas? </p>

<p>An example of how this data is stored, and the problem I'm having, can be seen below.</p>

<pre><code>+------+--------+------------+------------+----------------------------+
| id   | number | initiated  | updated    | last_category_reached      |
+------+--------+------------+------------+----------------------------+
|    4 |      1 | 1277914181 | 1277914320 | complete                   |
|  105 |      2 | 1282639343 | 1283444717 | complete                   |
|  397 |      3 | 1284999429 | 1285001298 | complete                   |
|  404 |      3 | 1287478550 | 1287478631 | complete                   |
|  636 |      3 | 1287479243 | 1287479377 | complete                   |
|  649 |      3 | 1287581361 | 1287581466 | complete                   |
|  652 |      3 | 1287585123 | 1287585365 | complete                   |
|  656 |      3 | 1290185205 | 1290424128 | complete                   |
| 1105 |      3 | 1292421193 | 1292426686 | complete                   |
| 1106 |      3 | 1292426769 | 1292426870 | complete                   |
+------+--------+------------+------------+----------------------------+
</code></pre>

<p>My php logic is below...</p>

<pre><code>public function update_entry($stage = NULL)
    {
        // Get last number entered for this user
        $last_entry = $this-&gt;last_entry();

        // If part one, user profile is calling the update (passing the next stage as a param)
        if ($stage === 'user/profile/2?s=p_prof&amp;p=2')
        {
            // Only at this stage do we ever create a new entry
            $entry = ORM::factory('data_entry');

            // If no previous sessions, start from 1
            if ($last_entry === FALSE)
                $num = 1; 

            //Here we need to check the time period elapsed since the last submission
            else
            {
                // Check if time difference between last visit and current time is less than 49 days and more than 21 days
                if (($last_entry-&gt;initiated &gt; time() - 4233600) &amp;&amp; ($last_entry-&gt;initiated &lt; time() - 1814400))
                {
                    // Within allowed timeframe, ok to increment by one as a new entry
                    $num = $last_entry-&gt;number + 1;
                }
                // More than 49 days since last visit
                elseif (($last_entry-&gt;initiated &lt; time() - 4233600)) 
                {
                    // Increment by two to break consecutive entries
                    $num = $last_entry-&gt;number + 2;
                }
                // Entry is within the last 21 days - if user never finished stages, use last entry created instead of creating a new one
                else
                {
                    // If they are back at the start having completed a full entry the last time, ok to create a new entry - otherwise use the one created the last time
                    if ($last_entry-&gt;last_category_reached !== 'complete')
                        $entry = $last_entry;

                    $num = $last_entry-&gt;number;
                }

            }

            // Save the rest of the data for a new entry
            $entry-&gt;number = $num;
            $entry-&gt;initiated = time();
            $entry-&gt;updated = time();
            $entry-&gt;last_category_reached = $stage;
            $entry-&gt;user_id = $this-&gt;id;
            $entry-&gt;save();
        }
        // If it's been more than 49 days since last part completion of an entry, user won't be given option to finish the entry, so no need for time check here
        elseif ($stage !== NULL)
        {
            // This must be a continuation of a form, not the beginning of a new one
            // Just update the stage reached and save
            $last_entry-&gt;last_category_reached = $stage;
            $last_entry-&gt;updated = time();
            $last_entry-&gt;save();
            // Assign to $entry for return
            $entry = $last_entry;
        }

        return $entry;
    }

    /**
     * Returns the the last data entry session
     * @return 
     */
    public function last_entry()
    {
            return $this
                    -&gt;limit(1)
                    -&gt;data_entries
                    -&gt;current();
    }
</code></pre>
