MySQLi INSERT to multiple tables with prepared statements

[Fernando_4]

Here's how I'm thinking about inserting values into two tables. Each reservation will have a set of services the amount of which can vary, so I need to setup the query dynamically, here's my first sketch, although not tested yet.

function saveBooking($fullBook) {
    $mysqli = new mysqli(HOST, USER, PASSWORD, DB_PRICE);
    if (mysqli_connect_errno()) {
        echo ("Connect failed: " .mysqli_connect_error());
        exit();
    }

    $reservation = 
        "BEGIN;
        INSERT INTO reservations (userID, fullPrice)
          VALUES(?, ?);
        SELECT LAST_INSERT_ID() INTO @reservationID;";
    $reservationParamType = 'id';//int, double

    $serviceQuery = 
        "INSERT INTO services (reservationID, fromDestination, toDestination) 
          VALUES(@reservationID,?, ?);";
    $serviceParamType = 'ii';//int int

    $fullService;
    $fullServiceParamType;
    $fullServiceParams;
    foreach($fullBook as $k => $booking){
        $reservationParams = $booking['userID'].', '.$booking['fullPrice'];
        $serviceParams = $booking[$k]['fromDestination'].', '.$booking[$k]['toDestination'];

        $fullServiceQuery .= $serviceQuery;
        $fullServiceParamType .= $serviceParamType;
        $fullServiceParams .= $serviceParams;
    }

    $query = $reservation.$fullServiceQuery."COMMIT;";
    $paramTypes = $reservationParamType.$fullServiceParamType;
    $params = $reservationParams.$fullServiceParams;

    if ($stmt = $mysqli->prepare($query)) {
        $stmt->bind_param($paramTypes, $params);
        if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit();
        }
        if (!$mysqli->set_charset("utf8")) {
            printf("Error loading character set utf8: %s\n", $mysqli->error);
        }
        $stmt->execute();
        printf("%d Row inserted.\n", $stmt->affected_rows);
        /* close statement and connection */
        $stmt->close();
        /* close connection */
        $mysqli->close();
    } else {
        echo 'There was a problem connecting to the database, please refresh your browser.';
    }
    $mysqli->close();
}

Would this type of logic concern you in any way? Given that the values have already been sanitized with the following:

function sanitize($input) {
    if (is_array($input)) {
        $output = array();
        foreach ($input as $key => $value){
            $output[$key] = sanitize($value);
        }
        return $output;
    }
    return htmlentities(strip_tags(trim($input)));
}

Meaning I haven't taken care of quotes, but I'm assuming that's what prepared statements are for.
Any pointers as to what could not be a best practice, something that will fail on a certain occasion or another way to go about this is what I'm looking for.
Thanks in advance.

[pritaeas]

Have a look at mysqli_multi_query, because I think execute does not execute multiple queries.

[Fernando_4]

So basically you're saying change if ($stmt = $mysqli->prepare($query)) to if ($stmt = $mysqli->multi_query($query)).

I've checked the PhP documentation and they present this sample:

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

I'm just not sure if I should go with the do{}while() statement since I'm not going for a SELECT but rather an INSERT...

On the other hand, while at lunch, I thought that I was missing a table on this query, since I'll need to CREATE a user or UPDATE a user if he exists, before going with the queries I created here. That way I can have a userID in the reservations table to create the relationship.

Is there any tool that would allow us to paste PhP code and do this test? Something like SQLFiddle (that one only lets us use SQL statements) that would allow the use of the mysqli object and see if that "guy" knows what I want him to do xD

[pritaeas]

So basically you're saying change if ($stmt = $mysqli->prepare($query)) to if ($stmt = $mysqli->multi_query($query)).

No. I suggested to replace execute

[pritaeas]

In addition. You can keep using execute if instead of the method above you execute each query seperately, and start with begin-transaction

[Fernando_4]

I'm glad you said that. The block I posted was my pseudo-almost-code before I tried to pull it off. Now that I've built the databases and set everything up, I noticed I was missing a few things. Let me post what I've got so far and what issues I'm up against.

Current block:

function saveBooking($fullBook) {
    $mysqli = new mysqli(HOST, USER, PASSWORD, DB_PRICE);
    if (mysqli_connect_errno()) {
        echo ("Connect failed: " .mysqli_connect_error());
        exit();
    }

    $clientQuery = 
        "BEGIN;
        IF EXISTS (SELECT * FROM `clients` WHERE EMAIL = ?)
            UPDATE clients SET (NAME = ?, SNAME = ?) 
              WHERE EMAIL = ?
            SELECT LAST_UPDATE_ID() INTO @clientID
        ELSE
            INSERT INTO clients (NAME, SNAME, EMAIL)
              VALUES (?, ?, ?)
            SELECT LAST_INSERT_ID() INTO @clientID;";
    $clientParamType = 'sssssss';//strings
    $clientParams = "'".$_SESSION['buyerEmail']."', '".$_SESSION['buyerName']."', '".$_SESSION['buyerSName']."', '".$_SESSION['buyerEmail']."', '".$_SESSION['buyerName']."', '".$_SESSION['buyerSName']."', '".$_SESSION['buyerEmail'];

    $reservationQuery = "
        INSERT INTO Breservations (clientID, siteID)
          VALUES(@clientID, ?);
        SELECT LAST_INSERT_ID() INTO @reservationID;";
    $reservationParamType = 'i';
    $reservationParams = $_SESSION['dbSiteConfig']['id'];

    $serviceQuery = "
        INSERT INTO Bservices (
          reservationID, carID, 
          destFrom, destTo, 
          fromDetails, fromDate, fromTime, 
          toDetails, toDate, toTime, 
          pax, comments, greeting, mobile, phone, 
          discount, price) 
            VALUES(@reservationID, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
        SELECT LAST_INSERT_ID() INTO @servicesID;";
    $serviceParamType = 'iiiissssssisssiid';
    //taken care of in foreach loop
    $fullServiceQuery;
    $fullServiceParamType = "";
    $fullServiceParams;

    $extrasQuery = "
        INSERT INTO extras (servID, name, amount) 
          VALUES(@servicesID, ?, ?);";
    $extrasParamType = 'si';//string int
    //$fullExtraQuery, $fullExtraParamType, $fullExtraParams
    //these variables aren't created because of the foreach loops
    //foreach booking add each extra
    //the extras queries are appended to the service queries so we don't loose the @servicesID for the extras

    foreach($fullBook as $k => $booking){
        $serviceParams = 
          $booking['carID']."', '".
          $booking['bookFrom']."', '".$booking['bookTo']."', '".
          $booking['bookFromDetails']."', '".$booking['bookDate']."', '".$booking['bookTime']."', '".
          $booking['bookToDetails']."', '".$booking['bookToDate']."', '".$booking['bookToTime']."', '".
          $booking['bookPax']."', '".$booking['bookComments']."', '".$booking['bookGreeting']."', '".
          $booking['bookMobile']."', '".$booking['bookPhone']."', '".
          $booking['bookPrice']."', '".$booking['bookDisct'];

        $fullServiceQuery .= $serviceQuery;
        $fullServiceParamType .= $serviceParamType;
        $fullServiceParams .= "', '".$serviceParams;

        foreach($booking['chosenExtras'] as $key => $extra) {
            $extraParams = $key."', '".$extra;

            $fullServiceQuery .= $extrasQuery;
            $fullServiceParamType .= $extrasParamType;
            $fullServiceParams .= "', '".$extraParams;
        }
    }

    $query = $clientQuery.$reservationQuery.$fullServiceQuery."COMMIT;";
    $paramTypes = "'".$clientParamType.$reservationParamType.$fullServiceParamType."'";
    $params = $clientParams."', '".$reservationParams."', '".$fullServiceParams."'";
    $bindStr = $paramTypes.', '.$params;

    if ($stmt = $mysqli->multi_query($query)) {
        $stmt->bind_param($bindStr);
        if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit();
        }
        if (!$mysqli->set_charset("utf8")) {
            printf("Error loading character set utf8: %s\n", $mysqli->error);
        }
        $stmt->execute();
        printf("%d Row inserted.\n", $stmt->affected_rows);
        $stmt->close();
        $mysqli->close();
        return true;
    } else {
        $mysqli->close();
        printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);
        return false;
    }
}//saveBooking()

So what I would end up would be, the following query:

IF EXISTS (SELECT * FROM `clients` WHERE EMAIL = ?)
  UPDATE clients SET (NAME = ?, SNAME = ?) 
  WHERE EMAIL = ?
  SELECT LAST_UPDATE_ID() INTO @clientID
ELSE
  INSERT INTO clients (NAME, SNAME, EMAIL)
    VALUES (?, ?, ?)
    SELECT LAST_INSERT_ID() INTO @clientID;
INSERT INTO Breservations (clientID, siteID)
  VALUES(@clientID, ?);
SELECT LAST_INSERT_ID() INTO @reservationID;
INSERT INTO Bservices (
  reservationID, carID, 
  destFrom, destTo, 
  fromDetails, fromDate, fromTime, 
  toDetails, toDate, toTime, 
  pax, comments, greeting, mobile, phone, 
  discount, price) 
    VALUES(@reservationID, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
SELECT LAST_INSERT_ID() INTO @servicesID;
INSERT INTO extras (servID, name, amount) 
  VALUES(@servicesID, ?, ?);
INSERT INTO extras (servID, name, amount) 
  VALUES(@servicesID, ?, ?);
INSERT INTO extras (servID, name, amount) 
  VALUES(@servicesID, ?, ?);
INSERT INTO Bservices (
  reservationID, carID, 
  destFrom, destTo, 
  fromDetails, fromDate, fromTime, 
  toDetails, toDate, toTime, 
  pax, comments, greeting, mobile, phone, 
  discount, price) 
    VALUES(@reservationID, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
  SELECT LAST_INSERT_ID() INTO @servicesID;
INSERT INTO extras (servID, name, amount) 
  VALUES(@servicesID, ?, ?);
INSERT INTO extras (servID, name, amount) 
  VALUES(@servicesID, ?, ?);
INSERT INTO extras (servID, name, amount) 
  VALUES(@servicesID, ?, ?);
COMMIT;

And $bindStr would be as follows:

'sssssssiiiiissssssisssiidsisisiiiiissssssisssiidsisisi', '[e-mail]', 'Fernando', 'Silva', '[e-mail]', 'Fernando', 'Silva', '[e-mail]', '100', '', '1', '2', '7', 'bookFromDetails', '2014-04-11', '03:05', 'bookToDetails', '', '', '4', 'abc', 'Fernando Silva', 'mobile', '44123456789', '', '', 'baby', '01', 'booster', '02', 'luggage', '03', '1', '7', '2', 'bookToDetails', '', '', 'bookToDetails2', '2014-04-12', '04:20', '4', 'abc', 'Fernando Silva', 'mobile', '44123456789', '', '', 'baby', '02', 'booster', '01', 'luggage', '05'

The error I'm getting at the moment is Fatal error: Call to a member function bind_param() on a non-object in /home/algarvet/public_html/func/dbConnect.php on line 450 line 450 = $stmt->bind_param($bindStr);

I did what you said, to keep ->prepare but replace ->execute with ->multi_query but there I'm getting this error Warning: saveBooking() [function.savebooking]: Couldn't fetch mysqli in /home/algarvet/public_html/func/dbConnect.php on line 465 line 465 = printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);. If I comment this line out, I don't get an error, but it resolves to if ($stmt = $mysqli->multi_query($query)) being false and nothing is saved to the database.

So I basically hit a wall :S I kept googling though and found this.

mysqli::multi_query takes a query string as its argument, not a prepared statement.
mysql::prepare can only prepare a single statement:
The query must consist of a single SQL statement.

So I think what you just posted might be the solution, I'm just afraid it might decrease performance since instead of one hit with several instructions, I'll cascade the list of SQL statements at the database, one-by-one.

Going to look at the transaction link you posted, but I think I've read that already. I'm using the BEGIN; COMMIT; on this query. And sorry for the long post.

[Fernando_4]

Ok, just read about the beginTransaction, I had read about the SQL transaction statements, not the php version, same principle though.

Now that gave me another question. I'm using prepared statements to avoid SQL Injection (as suggested somewhere on the internet xD), since from what I understood the bind_param method will pass the variables without having to escape quotes (this is what I read/understood, I haven't got a clue if it really is that way, I haven't done the test yet).

The way I see this implemented (although downvoted on php.net) would execute 1 query at a time and I just have that feeling there's something out there that could do what I was trying to pull off in one go.

$gbd->beginTransaction();
$gbd->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
$gbd->exec("insert into salarychange (id, amount, changedate) 
  values (23, 50000, NOW())");
$gbd->commit();

Go in write what you need, move along. Quick and clean. I'll keep googling... if by tomorrow I haven't found a way to pull it off like this I'll just execute within the loop, one query at a time.

[Fernando_4]

While poking google I found my answer... gotta read more for a solution though.

Use of the multiple statement with prepared statements is not supported.

This is right before the "User Contributed Notes" at PhP.net.

And the read goes on xD

[camille_1]

Do you know about the vb.net?

[Fernando_4]

I know vb.net exists, but I never used it, I'm also going to stick to PhP for the time being as what you're suggesting is a change in programming language to a question that is tied to how to manipulate the mysqli object in PhP.

If I wanted to change to another programming language I'd probably go with Java and get my foot into getting the whole thing ready for mobile apps at the same time. But that will be a battle for another time, for now I've gotta stick to PhP and try to master it.

Back on topic:
I've been reading the documentation on the mysqli object and something struck me as a possibility, although I'm not sure.
So they say a prepared statement sends that statement to the db server and then sends the bound params to get executed according to that statement. So I thought about creating one mysqli object, and then several statement objects and loop through the parameters to bind to each statement. In pseudo-code it would be something like this:

$mysqli = new mysqli(HOST, USER, PASSWORD, DB_PRICE);

$stmt1 = $mysqli->prepare($query1);
$stmt2 = $mysqli->prepare($query2);

foreach($something as $some){
    $stmt1->bind_param($bindStr1);
    $stmt1->execute();

    foreach($some as $thing){
        $stmt2->bind_param($bindStr2);
        $stmt2->execute();
    }
}

So I will keep using just one $mysqli object, but creating several statements and parameter binds, although I'm unsure if I should use the $stmt = $mysqli->stmt_init(); method to initialize a stmt object or just assign the mysqli object to different variables like I did in the block above.

I'm going to try it out. If anyone has suggestions, they're very welcome since I need to get familiar with the mysqli object. If you think the PDOobject is better, let me know why since I never used it.

[pritaeas]

I'm going to try it out.

I think you're on the right track now.

If you think the PDOobject is better

It's different, not better. On a lower level it uses the same code for MySQL. PDO is useful if you want to support multiple databases.

[Fernando_4]

Ok, I've banged my head over the weekend and finally got it to work. Now I think I just need to add $mysqli->beginTransaction(); and $mysqli->commit();. But here's the function fully working:

<?php
function saveBooking($fullBook) {
    $mysqli = new mysqli(HOST, USER, PASSWORD, DB_PRICE);
    if ($mysqli->errno) {
        echo ("Connect failed: " .$mysqli->error);
        exit();
    }
    if (!$mysqli->set_charset("utf8")) {
        printf("Error loading character set utf8: %s\n", $mysqli->error);
    }

    $clQry = 
        "INSERT INTO `clients` (EMAIL, NAME, SNAME) 
            VALUES (?, ?, ?)
        ON DUPLICATE KEY 
            UPDATE NAME = VALUES(NAME), SNAME = VALUES(SNAME)";
    $clStmt =  $mysqli->stmt_init();

    $idQry = "SELECT ID FROM `clients` WHERE EMAIL = ?";
    $idStmt = $mysqli->stmt_init();

    $resQry = "
        INSERT INTO Breservations (clientID, siteID)
            VALUES(?, ?)";
    $resStmt = $mysqli->stmt_init();

    $svQry = "
        INSERT INTO Bservices (
            resID, carID, 
            destFrom, destTo, 
            fromDetails, fromDate, fromTime, 
            toDetails, toDate, toTime, 
            pax, comments, greeting, mobile, phone, 
            discount, price) 
                VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    $svStmt = $mysqli->stmt_init();

    $xtQry = "
        INSERT INTO Bextras (servID, name, amount) 
            VALUES(?, ?, ?)";
    $xtStmt = $mysqli->stmt_init();

    if ($clStmt->prepare($clQry)) {
        $clStmt->bind_param('sss', $_SESSION['buyerEmail'], $_SESSION['buyerName'], $_SESSION['buyerSName']);
        $clStmt->execute();
        //echo 'clientID = '.$clID = $clStmt->insert_id; //doesnt work
        //printf("<br />Client row inserted (%d) \n", $clStmt->affected_rows);
        $clStmt->close();

        $idStmt->prepare($idQry);
        $idStmt->bind_param('s', $_SESSION['buyerEmail']);
        $clID = $idStmt->execute();
        //printf("<br />Client ID =  (%d) \n", $clID);
        $idStmt->close();

        $resStmt->prepare($resQry);
        $resStmt->bind_param('ii', $clID, $_SESSION['dbSiteConfig']['id']);
        $resStmt->execute();
        //printf("<br />Reservation row inserted (%d) \n", $resStmt->affected_rows);
        $resID = $resStmt->insert_id;
        $resStmt->close();

        foreach($fullBook as $k => $booking){
            $svStmt->prepare($svQry);
            $svStmt->bind_param('iiiissssssisssiid', 
                $resID, $booking['carID'],
                $booking['bookFrom'], $booking['bookTo'],
                $booking['bookFromDetails'], $booking['bookDate'], $booking['bookTime'],
                $booking['bookToDetails'], $booking['bookToDate'], $booking['bookToTime'],
                $booking['bookPax'], $booking['bookComments'], $booking['bookGreeting'],
                $booking['bookMobile'], $booking['bookPhone'],
                $booking['bookPrice'], $booking['bookDisct']);/**/

            if(!$svStmt->execute()){
                //printf('svStmt->execute error (%d): %s', $svStmt->errno, $svStmt->error);
            }
            //printf("<br />Service row inserted (%d) \n", $svStmt->affected_rows);
            $svID = $svStmt->insert_id;

            foreach($booking['chosenExtras'] as $key => $extra) {
                $xtStmt->prepare($xtQry);
                $xtStmt->bind_param('isi', $svID, $key, $extra);
                $xtStmt->execute();
                //printf("<br />Extras row inserted (%d) \n", $xtStmt->affected_rows);
            }
        }
        $svStmt->close();
        $xtStmt->close();
        $mysqli->close();
        return true;
    } else {
        printf('MySQLi error (%d): %s', $mysqli->errno, $mysqli->error);
        echo '<br />';
        printf('Statement error (%d): %s', $stmt->errno, $stmt->error);
        $mysqli->close();
        return false;
    }
}//saveBooking ?>

I'll leave this discussion open for improvement suggestions. Thanks for the input and just for being there so I can toss ideas around^^