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 : .
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!