CODEWITHSUNDEEP
phpmysql
nunu
nunu

Reputation: 2871

Mysql : reset id start from 1 if day change

I make an unique id, which contain of CURRENT_DATE and character like :

SELECT CONCAT(DATE_FORMAT(CURRENT_DATE,'%Y%m%d'),'Q',
      LPAD(MAX(RIGHT(idreport,3))+1,3,'0')) 
FROM record.report

result :

20140723Q001

I want every day it start from 001. So, if the query above is correct I would get result like 20140724Q001 today. But I get a wrong result 20140724Q002. How to reset 3 digit behind the id if the day change?

complete syntax :

$sql="SELECT CONCAT(DATE_FORMAT(CURRENT_DATE,'%Y%m%d'),'Q',LPAD(MAX(RIGHT(idreport,3))+1,3,'0')) 
                FROM record.report";
  $res=mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );  
  $dat1=mysql_fetch_array($res, MYSQL_NUM);
  if($dat1 == 0){ 
        $sql = "SELECT DATE_FORMAT(CURRENT_DATE,'%Y%m%d')";
        $res1=mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );
        $dat2=mysql_fetch_array($res1, MYSQL_NUM);
        // first number
        $RegNum = $dat2[0]."Q001";
        } else {
                $RegNum = $dat1[0];
                }

Upvotes: 1

Views: 212

Answers (1)

Suvendu Shekhar Giri
Suvendu Shekhar Giri

Reputation: 1384

Although we can have a more optimized solution, this should do your job-

SELECT CONCAT(DATE_FORMAT(CURRENT_DATE,'%Y%m%d'),'Q',
      LPAD(MAX(RIGHT(CASE WHEN LEFT(idreport,8)=DATE_FORMAT(CURRENT_DATE,'%Y%m%d') THEN idreport ELSE '000' END,3))+1,3,'0'))
FROM record.report;

Incase any query, please let me know.

Upvotes: 1

Related Questions