CODEWITHSUNDEEP
phpmysqlphpmyadmin
Vincent H Guyo
Vincent H Guyo

Reputation: 395

How to increment a variable in php using the date in PHP

I'm developing a page in which I want to record events using a date. e.g. If we are in March 2016, the value the code has to generate for the first event is 1_03_2016, then if there is another event to happen in March the value will be 2_03_2016, then if there is another event to happen in March again the value will be 3_03_2016 and so on and so on.

I have done the following:

$date= date('Y-m-d');


$time=strtotime($date);
$month=date("m",$time);
$year=date("Y",$time);

$zuva="";
$previous=0;
$sql = "select * from offense order by id desc limit 1";
$query_result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($query_result)){

$previous = $row['previous'];
$zuva=$row['date'];
}
$time1=strtotime($zuva);
$month1=date("m",$time1);
$year1=date("Y",$time1);
if($month === $month1){


$previous++;
$casenumber = $previous."_".$month."_".$year;


}   elseif($date != $zuva){
    //$date= date('Y-m-d');
    $previous=0;
    $previous++;

$casenumber = $previous."_".$month."_".$year;
    }
    elseif(mysql_num_rows($query_result)==0){
    $previous=0;
    $previous++;

    $casenumber = $previous."_".$month."_".$year;

    }
$_SESSION['casenum']=$casenumber;
$_SESSION['previous']=$previous;

}

So i want the variable $casenumber to increment for every entry I insert into the database. Here is the screen of the table I want to insert into: enter image description here

Here is the query Im using to insert:

$query2 = "INSERT INTO `complaint_search` VALUES('','$identification_number','$casenumber')";
            $query_run2 = mysql_query($query2) or die(mysql_error());

After adding apokryfos code:

after adding apokryfos code

Upvotes: 0

Views: 697

Answers (2)

apokryfos
apokryfos

Reputation: 40730

You could simplify your code to something like:

<?php

function getNextCaseNumberId() {
    $date = date('Y-m-d');
    $time = strtotime($date);
    $month = date("m", $time);
    $year = date("Y", $time);
    $sql = "select * from `complaint_search` WHERE casenumber LIKE '%_{$month}_{$year}' order by cs_id desc limit 1"; //Assuming your CS_ID is autoincrement
    $query_result = mysql_query($sql) or die(mysql_error());

    if (mysql_num_rows($query_result) == 0) {
        return "1_{$month}_{$year}";
    } else {
        $row = mysql_fetch_assoc($query_result);        
        $lastId=explode("_",$row["casenumber"])[0];
        return ($lastId+1)."_".$month."_".$year; //Missed an underscore here
    }
}

Then you insert the new complaint like:

// Probably best to lock the complaint_search table here
$query2 = "INSERT INTO `complaint_search` VALUES('','$identification_number','".getNextCaseNumberId()."')";        
$query_run2 = mysql_query($query2) or die(mysql_error());
//But don't forget to unlock it here

Upvotes: 2

Mikey
Mikey

Reputation: 2704

Just another potential solution, rather than your current system could you not potentially implement something like the following:

Events Table
@id, event_name, event_date

CREATE TABLE Events (
  `id` int,
  `event_name` varchar(64),
  `event_date` timestamp
);

Then you could get the amount of events per month with a query like so:

SELECT count(event_name) as event_count,
       MONTH(event_date) as month
FROM Events
WHERE MONTH(event_date) = 3
GROUP BY LEFT(event_date, 7);

So if we had this test data: 

INSERT INTO Events (
  id, event_name, event_date
)
VALUES 
('1', 'Some Event', '2016-03-01'),
('2', 'Another Event', '2016-03-06'),
('3', 'Event Three', '2016-03-12'),
('4', 'Event Four', '2016-03-18'),
('5', 'Event Five', '2016-03-21')

The following would be returned by the query:

| event_count | month |
|-------------|-------|
|           5 |     3 |

You can find the fiddle for it here

That way on the PHP side if you wanted the number of events per month from your database you could do something like:

$month = 3;
$sql = "SELECT count(event_name) as event_count
        FROM Events
        WHERE MONTH(event_date) = '". mysql_real_escape_string($month) ."'
        GROUP BY LEFT(event_date, 7);";

$eventCount = mysql_result(mysql_query($sql), 0);
echo $eventCount; // Outputs 5

Not tested all of this, just a musing I had and I'm about to head home from work so it's a bit rushed towards the end, but hopefully another perspective may help somewhat.

Upvotes: 0

Related Questions