Closing connection in order to get remaining queries to run

Question

Perhaps I'm missing something very obvious here.

My code doesn't seem to work below the 'GET BOOK ID' annotation if I don't close and reopen the connection.

While it does work, it doesn't seem to be an efficient way at all to do this, any suggestions?

$AddBook = $conn->prepare("CALL makeBooking((SELECT CustID FROM customer WHERE UserName = '$UserName'), ?, ?, ?, ?, now())");
$AddBook->bind_param('iiis',$PerfID, $NumAdults, $NumChilds, $ColTicket);
if ($AddBook->execute())
{
    //----------------DEDUCT SEATS--------------//
    $SeatDeduction = $conn->prepare("CALL deductSeats($TotalSeats,?)");
    $SeatDeduction->bind_param('i',$PerfID);
    $SeatDeduction->execute();
    mysqli_close($conn);
    require ('connect.php');
    //-----------------GET BOOK ID--------------//
    $getBookID = "CALL getBookByUserName('$UserName')";
    $result2 = mysqli_query($conn, $getBookID);
    $Output2 = mysqli_fetch_assoc($result2);
    $BookID = $Output2['BookID'];
    mysqli_close($conn);
    require ('connect.php'); 
    //-------------------SET COST---------------//
    $setCost = "CALL setBookingPrice($BookID)";
    mysqli_query($conn,$setCost);
    mysqli_close($conn);
    require ('connect.php');
    //-------------------GET COST---------------//
    $getCost = "CALL getCost($BookID)";
    $result6 = mysqli_query($conn,$getCost);
    $Output6 = mysqli_fetch_assoc($result6);
    $Cost = $Output6['TotalCost'];
    mysqli_close($conn);
    require ('connect.php');
    //---------------BOOKING CONFIRM------------//
    $ShowRef = 'Booking Completed<p>Reference Number: <b>' . $BookID . '</b><p>';
    $showCost = 'Total Cost: <b><u>£' . $Cost . '<u><b><br>';
    $Confirm = $ShowRef.$showCost;
}
else
{
    die(mysqli_error($conn));
}
mysqli_close($conn);

Alex Andrei · Accepted Answer · 2015-11-06T23:35:02.417

0

You don't have to keep including the connect.php file over and over. Assuming you already included it somewhere at the top since you run $AddBook->execute()

See this updated code:

$AddBook = $conn->prepare("CALL makeBooking((SELECT CustID FROM customer WHERE UserName = '$UserName'), ?, ?, ?, ?, now())");
$AddBook->bind_param('iiis',$PerfID, $NumAdults, $NumChilds, $ColTicket);
if ($AddBook->execute())
{
    //----------------DEDUCT SEATS--------------//
    $SeatDeduction = $conn->prepare("CALL deductSeats($TotalSeats,?)");
    $SeatDeduction->bind_param('i',$PerfID);
    $SeatDeduction->execute();

    $SeatDeduction->close();
    $conn->next_result();

    // mysqli_close($conn); // remove this
    // require ('connect.php'); // remove this
    //-----------------GET BOOK ID--------------//
    $getBookID = "CALL getBookByUserName('$UserName')";
    $result2 = mysqli_query($conn, $getBookID);
    $Output2 = mysqli_fetch_assoc($result2);
    $BookID = $Output2['BookID'];
    // mysqli_close($conn); remove this
    // require ('connect.php');  remove this
    //-------------------SET COST---------------//
    // ...
    //-------------------GET COST---------------//
    // ...
    //---------------BOOKING CONFIRM------------//
    // ...
}
else
{
    die(mysqli_error($conn));
}

mysqli_close($conn);

edited Nov 06 '15 at 23:35

answered Nov 06 '15 at 23:20

Alex Andrei

7,315
3
28
42

Yes the reason why I have closed and reconnected is because the following queries won't run and I have no idea why – Albertoe86 Nov 06 '15 at 23:21
see this answer too http://stackoverflow.com/a/14561639/5043552, i believe it will help, notice how after each stored procedure call, the result object is closed, *not* the connection, and your equivalent of `$conn->next_result()` is called – Alex Andrei Nov 06 '15 at 23:32
Aha, I saw this very post and tried this, it gives me this...Call to a member function close() on string – Albertoe86 Nov 06 '15 at 23:36
also this http://stackoverflow.com/questions/32773694/2-prepared-statements-2-stored-procedures-1-mysqli-connection – Alex Andrei Nov 06 '15 at 23:36
make sure it's not a string but an object, like `$result` – Alex Andrei Nov 06 '15 at 23:37
$conn->next_result(); fixed my issue, thanks once again! – Albertoe86 Nov 06 '15 at 23:41

Closing connection in order to get remaining queries to run

1 Answers1