获取一年的记录数

Question

I am trying to write some PHP code so that when a user enters a quote, the application looks at the quote table, counts how many quotes have been raised for that year, then adds 1 to the count, the end result is to provide a unique and sequential quote number for that year. The format is supposed to be Q/YY/sequential number

//Insert New Q Reference
    //set standards
    $time=strtotime($values['QuoteDate']);
    $LongYear=date("Y",$time);
    $ShortYear=date("y",$time);

    //Get current number quotes for the year which quote was issued
    global $dal;
    $dal_table = $dal->Table("quotations"); 
    if ($values["QuoteDate"])
        { 
            $rstmp = CustomQuery("select count(*) as count1 from quotations where '$LongYear' = ".$LongYear); 
            $datatmp = db_fetch_array($rstmp); 
            $count_value = 0;   
            if ($datatmp["count1"]) $count_value = $datatmp["count1"];

            $values['QuoteReference'] = 'Q/'.$ShortYear.'/'.str_pad(($count_value+1), 4, '0', STR_PAD_LEFT);

        }

The problem i am having is that the code is just returning the total sequential number + 1 and it is not taking into account the count of the year!

See the following screenshot, the 2018 is supposed to be Q/18/0001

Answer 1

In your query, you are comparing

'$LongYear' = ".$LongYear

this will always succeed. You should be comparing the year from the value of the column instead:

YEAR(QuoteDate) = ".$LongYear