Prepared Statement (PHP5, SQLite3) exceeds 30 seconds execution Time

Question

I am trying to avoid duplicate entries of automatically generated random numbers in an SQLite3 DB through PHP. For that i have prepared Statements in a do while loop. The random numbers are generated and then a query checks if the number already exists. If Yes, generate again, if no, carry on.

Atleast, this is what i am trying to achieve...

But for some reason unknown to me, the PHP log keeps showing me that the maximum execution Time of 30 secs has been exeeded at the query line. Firstly, i tried doing the whole thing without prepared statements and it didn't work. I thought that was because i had php variables in the query. So i switched to Prepared Statements without success.

I checked all the POST Variables via Firebug and everything seems to be fine there. It is the Prepared Statement which is giving me diarrhea!!

Can you guys please help me ?

The PHP Code:

<?php

$adate    = $_POST['adate'];
$ddate    = $_POST['ddate'];
$ad       = $_POST['ad'];
$dd       = $_POST['dd'];
$fname    = $_POST['fname'];
$lname    = $_POST['lname'];
$email    = $_POST['email'];
$address  = $_POST['address'];
$postal   = $_POST['postal'];
$city     = $_POST['city'];
$country  = $_POST['country'];
$tel      = $_POST['tel'];
$message  = $_POST['message'];
$price    = $_POST['price'];
$bkfst    = $_POST['bkfst'];
$rnum     = $_POST['rnum'];
$rtype    = $_POST['rtype'];
$robotest = $_POST['blnk'];
$bid      = 0;
$cid      = 0;
$adate    = $adate . " 20:00:00";
$ddate    = $ddate . " 13:00:00";

if ($robotest)
    $error = "You are a gutless robot.";

else {

    function bid()
    {
        $bid = mt_rand(111111, 999999);
        if (($bid % 10) == 0) {
            $bid = $bid + 123;
        }
    }

    function cid()
    {
        $cid = mt_rand(11111, 99999);
        if (($cid % 10) == 0) {
            $cid = $cid + 123;
        }
    }

    include 'connect.php';

    do {
        cid();
    --> $sth = $db->prepare("SELECT COUNT (CustomerID) from Customer WHERE CustomerID = ?");
        $sth->execute(array($cid));
    } while ($sth->fetchColumn() > 0);

    $sth = $db->prepare("INSERT INTO Customer (CustomerID, FirstName, LastName, Address, PostalCode, City, Country, EMail, Phone) VALUES ('$cid', '$fname', '$lname', '$address', '$postal', '$city', '$country', '$email', '$tel')");
    $sth->execute();

    do {
        bid();
    --> $sth = $db->prepare("SELECT COUNT (BookingID) from Booking WHERE BookingID = ?");
        $sth->execute(array($bid));
    } while ($sth->fetchColumn() > 0);

    $sth = $db->prepare("INSERT INTO Booking (BookingID, Arrival, Checkout, RoomNumber, CustomerID, Breakfast, Comment, Paid) VALUES ('$bid', '$adate', '$ddate', '$rnum', '$cid', '$bkfst', '$message', 'N')");
    $sth->execute();

    $subject = "Your Booking";
    $message = "Hi $fname,\n\nA $rtype from $ad to $dd has been booked for you.\n\nYour Booking Code is $bid.\n\nRegards.";

    mail($email, $subject, $message);

    echo 'The Booking completed successfully! Check your E-Mail for further Information.';
}

?>

Lines beginning with --> in the code are the problematic lines.

And Yes, I am a Newbie who is learning by doing and also learning by annoying people in the Stack Overflow Forums :)

Thanks.

EDIT:

This is how my Code looks now. All the errors are gone but php is not inserting anything to the DB. The Email is sent correctly with the generated number.

<?php

$adate    = $_POST['adate'];
$ddate    = $_POST['ddate'];
$ad       = $_POST['ad'];
$dd       = $_POST['dd'];
$fname    = $_POST['fname'];
$lname    = $_POST['lname'];
$email    = $_POST['email'];
$address  = $_POST['address'];
$postal   = $_POST['postal'];
$city     = $_POST['city'];
$country  = $_POST['country'];
$tel      = $_POST['tel'];
$message  = $_POST['message'];
$price    = $_POST['price'];
$bkfst    = $_POST['bkfst'];
$rnum     = $_POST['rnum'];
$rtype    = $_POST['rtype'];
$robotest = $_POST['blnk'];
$adate    = $adate . " 20:00:00";
$ddate    = $ddate . " 13:00:00";
$cid;
$bid;

if ($robotest)
    $error = "You are a gutless robot.";

else {

    function bid()
    {
        global $bid;
        $bid = mt_rand(111111, 999999);
        if (($bid % 10) == 0) {
            $bid = $bid + 123;

        }
    }

    function cid()
    {
        global $cid;
        $cid = mt_rand(11111, 99999);
        if (($cid % 10) == 0) {
            $cid = $cid + 123;

        }
    }

    include 'connect.php';

    do {
        global $cid;
        cid();
        $sth = $db->prepare('SELECT COUNT (CustomerID) from Customer WHERE CustomerID = ?');
        $sth->execute(array($cid));
        } while ($sth->fetchColumn() > 0);


    global $cid;
    $sth = $db->prepare('INSERT INTO Customer (CustomerID, FirstName, LastName, Address, PostalCode, City, Country, EMail, Phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)');
    $sth->execute(array($cid, $fname, $lname, $address, $postal, $city, $country, $email, $tel));

    do {
        global $bid;
        bid();
        $sth = $db->prepare('SELECT COUNT (BookingID) from Booking WHERE BookingID = ?');
        } while ($sth->fetchColumn() > 0);


    global $bid;
    global $cid;
    $sth = $db->prepare('INSERT INTO Booking (BookingID, Arrival, Checkout, RoomNumber, CustomerID, Breakfast, Comment, Paid) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
    $sth->execute(array($bid, $adate, $ddate, $rnum, $cid, $bkfst, $message, 'N'));


    $subject = "Your Booking";
    global $bid;
    $message = "Hi $fname,\n\nA $rtype from $ad to $dd has been booked for you.\n\nYour Booking Code is $bid.\n\nRegards.";
    mail($email, $subject, $message);


    echo 'The Booking completed successfully! Check your E-Mail for further Information.';
}

?>

hhmmm...

Answer 1

It's working now:

<?php

$adate    = $_POST['adate'];
$ddate    = $_POST['ddate'];
$ad       = $_POST['ad'];
$dd       = $_POST['dd'];
$fname    = $_POST['fname'];
$lname    = $_POST['lname'];
$email    = $_POST['email'];
$address  = $_POST['address'];
$postal   = $_POST['postal'];
$city     = $_POST['city'];
$country  = $_POST['country'];
$tel      = $_POST['tel'];
$message  = $_POST['message'];
$price    = $_POST['price'];
$bkfst    = $_POST['bkfst'];
$rnum     = $_POST['rnum'];
$rtype    = $_POST['rtype'];
$robotest = $_POST['blnk'];
$adate    = $adate . " 20:00:00";
$ddate    = $ddate . " 13:00:00";
$cid;
$bid;

if ($robotest)
    $error = "You are a gutless robot.";

else {

    function bid()
    {
        global $bid;
        $bid = mt_rand(111111, 999999);
        if (($bid % 10) == 0) {
            $bid = $bid + 123;

        }
    }

    function cid()
    {
        global $cid;
        $cid = mt_rand(11111, 99999);
        if (($cid % 10) == 0) {
            $cid = $cid + 123;

        }
    }

    include 'connect.php';

    $sth = $db->prepare('SELECT COUNT (EMail) from Customer WHERE EMail = ?');
    $sth->execute(array($email));
    if($sth->fetchColumn() < 1){

    do {
        global $cid;
        cid();
        $sth = $db->prepare('SELECT COUNT (CustomerID) from Customer WHERE CustomerID = ?');
        $sth->execute(array($cid));
        } while ($sth->fetchColumn() > 0);

    global $cid;
    $sth = $db->prepare('INSERT INTO Customer (CustomerID, FirstName, LastName, Address, PostalCode, City, Country, EMail, Phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)');
    $sth->execute(array($cid, $fname, $lname, $address, $postal, $city, $country, $email, $tel));   

    }else{
    global $cid;    
    $sth = $db->prepare('SELECT CustomerID from Customer WHERE EMail = ?');
    $sth->execute(array($email));
    $id = $sth->fetch(PDO::FETCH_ASSOC);
    $cid = $id['CustomerID'];
    }

    do {
        global $bid;
        bid();
        $sth = $db->prepare('SELECT COUNT (BookingID) from Booking WHERE BookingID = ?');
        } while ($sth->fetchColumn() > 0);


    global $bid;
    global $cid;
    $booktime = date('Y-m-d H:i:s');
    $sth = $db->prepare('INSERT INTO Booking (BookingID, Arrival, Checkout, RoomNumber, CustomerID, Breakfast, Comment, Paid, BookTime, Invoice) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
    $sth->execute(array($bid, $adate, $ddate, $rnum, $cid, $bkfst, $message, 'N', $booktime, NULL));


    $subject = "Your Booking";
    global $bid;
    $message = "Hi $fname,\n\nA $rtype from $ad to $dd has been booked for you.\n\nYour Booking Code is $bid.\n\nMention this Code if you need to get in touch with us.\n\nRegards.";
    mail($email, $subject, $message);


    echo 'The Booking completed successfully! Check your E-Mail for further Information.';
}

?>

No Clue, if this is the best way to do it but it is working perfectly.

Thanks for all the hints.

Answer 2

This is an infinite loop:

do {
    cid();
    $sth = $db->prepare("SELECT COUNT (CustomerID) from Customer WHERE CustomerID = ?");
    $sth->execute(array($cid));
} while ($sth->fetchColumn() > 0);

Since your cid/bid() functions are badly constructed, the $cid you're using inside this do() loop will NEVER change from the $cid = 0 you did at the top of the script.

So the loop starts, you prepare/execute the query with CustomerID = 0, get back one of row of data with the count() results, which you fetch.

Then the loop rolls around again, and you RE-EXECUTE the query, with the exact same $cid = 0 value, so you continue reset the loop termination condition - you never end up with a value, because you keep query with the same bad/invalid cid=0.

It's pretty much the same like the good old BASIC program: 10 GOTO 10.