Skip to content
Advertisement

NULL MAX(col) value even though DB has a valid record

So I am trying to extract the maximum invoiceNo for current year and this is how I implemented it with PDO:

$sql = 'SELECT MAX(invoiceNo) AS invoiceId FROM invoices WHERE invoiceDate BETWEEN :yearStart AND :yearEnd HAVING invoiceId IS NOT NULL';
        if($stmt = $pdo1->prepare($sql)){
            $year = date("Y")."-01-01";
            $stmt->bindParam(":yearStart", $year);
            $year = date("Y")."-12-31";
            $stmt->bindParam(":yearEnd", $year);
            if($stmt->execute()){
                if($stmt->rowCount() == 1){
                    $row = $stmt->fetch(PDO::FETCH_ASSOC);
                    $invoiceNo = $row['invoiceId'];
                    $response = date("Y").strval(++$invoiceNo);
                }
                else{
                    $response = date("Y")."-0";
                }
            }
        }

However, $response keeps getting assigned to the else clause : enter image description here.

This is how my DB looks like: enter image description here

Hence I was expecting the $response to be 2022-2.

I think there is something wrong with my SQL query and I apologize for that, still learning the ropes!

Advertisement

Answer

$sql = "SELECT MAX(arbitInvoiceNo) AS invoiceId FROM invoices WHERE invoiceDate BETWEEN :dateStart AND :dateEnd HAVING invoiceId IS NOT NULL";
        if($stmt = $pdo1->prepare($sql)){
            $dateStart = date("Y")."-01-01";
            $stmt->bindParam(":dateStart", $dateStart);
            $dateEnd = date("Y")."-12-31";
            $stmt->bindParam(":dateEnd", $dateEnd);
            if($stmt->execute()){
                if($stmt->rowCount() == 1){
                    $row = $stmt->fetch(PDO::FETCH_ASSOC);
                    $invoiceNo = $row['invoiceId'];
                    $response = date("Y")."-".strval(++$invoiceNo);
                }
                else{
                    $response = date("Y")."-0";
                }
            }
        }

Changed the way I named the variables in PHP and the placeholders in the SQL query. Thank you ADyson for all the help!

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement