PHP detecting no result for PDO query

ghz 8months ago ⋅ 123 views

I am using PHP to fetch and determine the highest ID number for a category, then create the next id in order. The IDs are alphanumeric, a two letter code plus the numerical count ie: TH2, FR23.

Using the PDO code below, I am successfully able to retrieve the highest numbered ID for the category and increment it by one. But if there are no IDs using the letter pair, the process fails because the $hotelID variable is not generated. If the letter pair doesn't exist in the database yet it should generate the first ID ie: TH1 or FR1.

My SQL:

// find highest existing id code with that two letter code
$newHtlId = $offbase->query("SELECT * FROM hotelMstr WHERE hotelID LIKE '%$cntrycode%' ORDER BY hotelID DESC LIMIT 1");
while ($htlID = $newHtlId->fetch(PDO::FETCH_ASSOC)) {
    if ($newHtlId->rowCount() == 0) {
        $hotelID = $cntrycode.'1';
    }
    else {
        $hotelID = ++$htlID['hotelID'];
    }
}

The $cntrycode is the two letters shown in the id examples.

Revised version based on all the comments below, which does what we want. Thanks all.

// find highest existing id code with that two letter code
$newHtlId = $offbase->query("SELECT * FROM hotelMstr WHERE hotelID LIKE '$cntrycode%' ORDER BY hotelID DESC LIMIT 1");
if($htlID = $newHtlId->fetch(PDO::FETCH_ASSOC)) {
    $hotelID = ++$htlID['hotelID'];
}
else {
    $hotelID = $cntrycode.'1';
}

Answers

Your revised version of the code looks good and addresses the issue you were facing. However, I would like to suggest a slight modification for efficiency and clarity:

// Find the highest existing ID code with that two-letter code
$newHtlId = $offbase->query("SELECT hotelID FROM hotelMstr WHERE hotelID LIKE '$cntrycode%' ORDER BY hotelID DESC LIMIT 1");

if ($htlID = $newHtlId->fetch(PDO::FETCH_ASSOC)) {
    // Extract the numeric part of the highest ID and increment it by 1
    $numericPart = preg_replace('/[^0-9]/', '', $htlID['hotelID']);
    $hotelID = $cntrycode . ++$numericPart;
} else {
    // If no matching ID is found, create the first ID
    $hotelID = $cntrycode . '1';
}

In this version:

  • We only select the hotelID column from the database, which reduces unnecessary data retrieval.
  • We use preg_replace to extract the numeric part of the highest ID retrieved from the database. This allows us to increment the numeric part directly, regardless of its length or format.
  • If no matching ID is found, we simply create the first ID by appending '1' to the two-letter country code.

These modifications improve efficiency and make the code clearer.

#php