How to skip an iteration if record already exists? - PHP

Question

I'm working on a plate distribution system. I assigned a value to the variable $quantity and it will say how many plates to be distributed.

$plate_prefix and $plate_suffix is the starting point of the number of plates to be distributed. But there is an instance that there's a customized plate that will be between the number of plates.

Example: I need 30 plates, AAA-1001 is the start but AAA-1020 is already taken, so I need to skip AAA-1020 to get the plates AAA-1001 to AAA-1032.

    $region = $_POST['region'];
    $district_office = $_POST['district_office'];
    $quantity = $_POST['quantity'];
    $plate_prefix = $_POST['plate_prefix'];
    $plate_suffix = $_POST['plate_suffix'];

        $loop = 0;
        while($loop < $quantity)
        {
            if($plate_suffix <= 9999)
            {
                $sql1 = mysql_query("INSERT INTO mv_plate (`plate_prefix`, `plate_suffix`, `region`, `district_office`, `status`) 
                VALUES ('$plate_prefix', '$plate_suffix', '$region', '$district_office', 'Available')");

                $plate_suffix = $plate_suffix+1;
                $loop = $loop+1;
            }
                else
                {
                    $plate_prefix = ++$plate_prefix;
                    $plate_suffix = 1001;
                }
            }

make a select query where `yourfield` = '$yourplate'. then do a `if else` statement before you insert... — Sam Teng Wong, Dec 15 '15 at 02:10
If the DB is well designed (`UNIQUE` contraint on `plate_prefix` and `plate_suffix`) the `INSERT` will raise an error anyway thus preventing the duplicate plate. Note that your code is vulnerable to SQL injection : use [PDO](http://stackoverflow.com/a/6981200/1980659). — ForguesR, Dec 15 '15 at 02:16

score 1 · Accepted Answer · answered Dec 15 '15 at 04:54

Consider using the continue command to conditionally skip to next iteration. I also modified code to use the mysqli database API as mysql* is deprecated (even discontinued in PHP 7 - should your webhost update, you will face issues):

# DATABASE CONNECTION
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$loop = 0;
while($loop < $quantity) {  

    # SKIP TO NEXT ITERATION FOR PLATE ALREADY TAKEN  
    if($plate_suffix == 1020) { 
       $plate_suffix++;
       $loop++;
       continue; 
    }

    if($plate_suffix <= 9999) {          
       # PREPARING APPEND QUERY STATEMENT  
       $stmt = $conn->prepare("INSERT INTO mv_plate (`plate_prefix`, `plate_suffix`,
                                                   `region`, `district_office`, `status`) 
                               VALUES (?, ?, ?, ?, 'Available')");

       # BINDING PARAMETERS
       $stmt->bind_param("siss", $plate_prefix, $plate_suffix, $region, $district_office);
       # EXECUTING QUERY
       $stmt->execute();    

       $plate_suffix++;
       $loop++;
   }
   else {
        $plate_prefix = ++$plate_prefix;
        $plate_suffix = 1001;
   }                       
}

# CLOSE STATEMENT AND CONNECTION
$stmt->close();
$conn->close();

Thank you very much. I will consider this. – gauche23 Dec 15 '15 at 06:22 — gauche23, Dec 15 '15 at 06:22

How to skip an iteration if record already exists? - PHP

1 Answers1