Auto increment job number that resets at the start of each month InnoDB locking tables

Question

I have made a similar question 2 days ago but although i accepted a solution, it involved using MyISAM engine.After some researching i found out that i really shouldn't use MyISAM, for many reasons.So i decided to achieve the following result in an InnoDB engine where i was told i would need to use table locking which i am not so familiar with.

The result i want to achieve is a unique a job number something like this: 1311/5.With the two first digits 13 showing the year,the next two 11 the month and the number after the slash i would like it to be an auto_increment number that will reset each month and will serve as a job counter.

UPDATED : Since i have no concussion concern i workded our the followin code for the above problem:

    if($mar = $db->query("SELECT max(id) FROM jobs")) {
    if($mar2 = $mar->fetch_row()) {
        $mar3 = $mar2[0]; //----> max id from jobs table
        $mar4 = $mar3 - 1; //--> the 2nd biggest id which has the last inserted ref
    }
}

if($vak = $db->query("SELECT * FROM jobs where id = $mar4")) {
    if($vak2 = $vak->fetch_object()) {
        $vak3 = $vak2->case_reference;
            $vak3_len = strlen($vak3);
            $vak4 = substr($vak3, 4); //----> the last number of the last job num

        $vak5 = $vak2->created;
        $vak7 = substr($vak5, 8, 2); //----> the date(d) of the one which has a job num
    }
}

if($zan = $db->query("SELECT * FROM jobs where id = $mar3")) {
    if($zan2 = $zan->fetch_object()) {
        $zan3 = $zan2->created;
            $zan4 = substr($zan3, 2, 2); //----> the date(y) of the one without job num 
            $zan5 = substr($zan3, 5, 2); //----> the date(m) of the one without job num
            $zan7 = substr($zan3, 8, 2); //----> the date(d) of the one without job num
    }
}

$realcount = $vak4+1;
$ace = 1;
if($zan7 >= $vak7) {
    $ref = $zan4.$zan5.$realcount;
} else { $ref = $zan4.$zan5.$ace; } //----> $ref = the job num that gets inputed in new inserts


if($sqlref = $db->query("UPDATE  `prinseapals`.`jobs` SET  `case_reference` =  $ref WHERE  `jobs`.`id` = $mar3")) {

}

The desired table would look something like:

customer    vessel         created          ref_num

nomikos     lucky luke     2013-09-04   1309/25
allseas     letto          2013-09-18   1309/26
medcare     marina         2013-10-01   1310/1
golden      kamari         2013-10-14   1310/2
marine      winner         2013-11-01   1311/1

All help is welcome and just to let u know i am really new to PHP-MySQL. Thanks in advance. But now the problem is that in the final $ref variable i cant concatonate .'/'. slash like this before the last number anyone has any idea why?? I tried with double quotes and its not working either.Ans as if that wasnt enough now i tried uploading this page to my actual site and while it was working locally it doesnt online:((

score 0 · Answer 1 · answered Nov 25 '13 at 23:34

0

Try this..

   $ref = $zan4.$zan5.'/'.$realcount;

answered Nov 25 '13 at 23:34

Kishore

352
1
3
19

score 0 · Accepted Answer · answered Nov 27 '13 at 10:41

Only and only if concurrency is not an issue then in a similar problem you can use my solution :

if($mar = $db->query("SELECT max(id) FROM jobs")) {  //----> $db = your connection and jobs = the table
    if($mar2 = $mar->fetch_row()) {
        $mar3 = $mar2[0]; //----> max id from jobs
        $mar4 = $mar3 - 1; //----> the 2nd max id thich has on it assigned the last ref number
    }
}

if($vak = $db->query("SELECT * FROM jobs where id = $mar4")) {
if($vak2 = $vak->fetch_object()) {
    $vak3 = $vak2->case_reference;
        $vak3_len = strlen($vak3);
        $vak4 = substr($vak3, 5); //----> the last ref number

    $vak5 = $vak2->created;
    $vak7 = substr($vak5, 8, 2); //----> date(d) of the row which HAS a ref num
}
}

if($zan = $db->query("SELECT * FROM jobs where id = $mar3")) {
    if($zan2 = $zan->fetch_object()) {
        $zan3 = $zan2->created;
            $zan4 = substr($zan3, 2, 2); //----> date(y) of the row which HAS NOT a ref num 
            $zan5 = substr($zan3, 5, 2); //----> date(m) of the row which HAS NOT a ref num
            $zan7 = substr($zan3, 8, 2); //----> date(d) of the row which HAS NOT a ref num

            $realcount = $vak4+1;
            $ace = 1;
        if($zan7 >= $vak7) {
        $ref = $zan4.$zan5."/".$realcount;
        } else { $ref = $zan4.$zan5."/".$ace; } //----> $ref = the final number you created to use 

            if($db->query("UPDATE  jobs SET  case_reference = '$ref' WHERE  id = $mar3")) {

            }

    }
}

Auto increment job number that resets at the start of each month InnoDB locking tables

2 Answers2