Inserting Multiple Values with PDO and a Loop

Question

To give you some background, the flow is: Connect to a 3rd party API, pull data stored as json, convert to php and use the data in the below code.

I found this work originally but unable to figure out how to modify it to my needs. Perhaps one of you could understand it better?

I am doing 3 things here. First checking the ID of a house + last_update stamp to determine which houses need to be updated in my database. If they exist but details have changed, drop the current data and store it in a variable ready to be inserted. If the data does not exist, insert it.

Something to note: The script takes so long to execute that I have to set set_time_limit(0); which I realise is bad practise but I needed to force the script to complete.

I have cut my code down quite a lot given that I had over 40 different manually entered prepared statements for either:

• Updating records
• Deleting records
• Inserting records

I have identified the expected outputs using screenshots so please ignore any open braces at this point as the main issue is refining the code to a more dynamic approach and making it quicker of course.

<?php 


$update = '';
$add = '';

if (!empty($houses)) {
foreach($houses as $travel_Prop) {

    $Prop = $travel_Prop['data'][0]; // Need to check this!

    if ($Prop['id'] > '0') { // Ignore empty arrays

        $sql= "SELECT * FROM travel_a_property WHERE travel_prop_id = :travel_prop_id";
        $stmt = $extDb->prepare("$sql");
        $stmt->bindParam(':travel_prop_id', $Prop['id'], PDO::PARAM_INT);
        $stmt->execute();
        $Result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (!empty($Result)) {
            $travel_last_update = $Prop['last_update'];
            $local_last_update = $Result[0]['last_update'];

            if ($travel_last_update > $local_last_update) {
                $update[] = $Prop;
                echo 'Property ID: ' .$Prop['id'] .' Property modified: Updating Records.<br>';
            } else {
                echo 'Property ID: ' .$Prop['id'] .' Property details: Up to Date.<br>';
            }

        } else {
            $add[] = $Prop;
            echo 'Property ID: ' .$Prop['id'] .' Property Created: Adding to Records.';
        }
    }
}

NOTE: Code will carry on after screenshot output

    # UPDATE
if (!empty($update)) {
//print_r($update);
foreach ($update as $PropUpdate) {

    // Get all_prop_id
    $sql= "SELECT * FROM travel_a_property WHERE travel_prop_id = :travel_prop_id";
    $stmt = $extDb->prepare("$sql");
    $stmt->bindParam(':travel_prop_id', $PropUpdate['id'], PDO::PARAM_INT);
    $stmt->execute();
    //$Result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $obj = $stmt->fetchObject();
    //echo $obj->filmName;

    $all_prop_id = $obj->all_prop_id;
    echo $all_prop_id;


    // Update master db table a_property
    $sql = "UPDATE travel_a_property SET last_update = :last_update 
    HERE all_prop_id = :all_prop_id";
    $stmt = $extDb->prepare($sql);
    $stmt->bindParam(':last_update', $PropUpdate['last_update'], PDO::PARAM_STR);
    $stmt->bindParam(':all_prop_id', $all_prop_id, PDO::PARAM_INT);
    $stmt->execute();

    echo '<br>Prop Updated - all_prop_id : ' .$all_prop_id .'<br>';


    # DELETe & INSERT

    $sql = "DELETE FROM ot_b_address WHERE glob_prop_id = :glob_prop_id";
    $stmt = $extDb->prepare($sql);
    $stmt->bindParam(':glob_prop_id', $glob_prop_id, PDO::PARAM_INT);
    $stmt->execute();

    $sql = "INSERT INTO ot_b_address(glob_prop_id, address1, address2, city, state, zip_code, 
    country, latitude, longitude) VALUES ( :glob_prop_id, :address1, :address2, :city, :state, 
    :zip_code, :country, :latitude, :longitude)";

    $stmt = $extDb->prepare($sql);

    $stmt->bindParam(':glob_prop_id', $glob_prop_id, PDO::PARAM_INT);
    $stmt->bindParam(':address1', $PropUpdate['address']['address1'], PDO::PARAM_STR);
    $stmt->bindParam(':address2', $PropUpdate['address']['address2'], PDO::PARAM_STR);
    $stmt->bindParam(':city', $PropUpdate['address']['city'], PDO::PARAM_STR);
    $stmt->bindParam(':state', $PropUpdate['address']['state'], PDO::PARAM_STR);
    $stmt->bindParam(':zip_code', $PropUpdate['address']['zip_code'], PDO::PARAM_STR);
    $stmt->bindParam(':country', $PropUpdate['address']['country'], PDO::PARAM_STR);
    $stmt->bindParam(':city', $PropUpdate['address']['city'], PDO::PARAM_STR);
    // use PARAM_STR although a number
    $stmt->bindParam(':latitude', $PropUpdate['address']['latitude'], PDO::PARAM_STR);
    $stmt->bindParam(':longitude', $PropUpdate['address']['longitude'], PDO::PARAM_STR);

    $stmt->execute();

    echo 'Address Updated <br>';



    $sql = "DELETE FROM travel_d_urls WHERE all_prop_id = :all_prop_id";
    $stmt = $extDb->prepare($sql);
    $stmt->bindParam(':all_prop_id', $all_prop_id, PDO::PARAM_INT);
    $stmt->execute();

    if (!empty($PropUpdate['urls'])) {
        foreach($PropUpdate['urls'] as $row => $Url) {
            $sql = "INSERT INTO travel_d_urls(all_prop_id, type, url) 
            VALUES ( :all_prop_id, :type, :url)";
            $stmt = $extDb->prepare($sql);

            $stmt->bindParam(':all_prop_id', $all_prop_id, PDO::PARAM_INT);
            $stmt->bindParam(':type', $Url['type'], PDO::PARAM_STR);
            $stmt->bindParam(':url', $Url['url'], PDO::PARAM_STR);
            $stmt->execute();

            echo 'URL '.$row .' Updated <br>';
        }
    }  
}
} else {
echo 'no rates to Update <br>';
}

The output is pretty much just the same thing (whatever is being updated) URL ADDED URL ADDED etc

The following code is the last if statement which tells the script to add the remaining properties if they do not exist.

} // end foreach $update

# INSERT ONLY
if (!empty($add)) {
    foreach ($add as $PropAdd) {

        $sql = "INSERT INTO travel_a_property(travel_prop_id, last_update) 
        VALUES ( :travel_prop_id, :last_update)";

        $stmt = $extDb->prepare($sql);

        $stmt->bindParam(':travel_prop_id', $PropAdd['id'], PDO::PARAM_INT); 
        $stmt->bindParam(':last_update', $PropAdd['last_update'], PDO::PARAM_STR);

        $stmt->execute();

        $all_prop_id = $extDb->lastInsertId(); // Use this ID in all the following record inserts

        echo '<br>Prop Added - all_prop_id : ' .$all_prop_id .'<br>';

        ##########################

        $sql = "INSERT INTO travel_b_address(all_prop_id, address1, address2, city, state, zip_code, country, 
        latitude, longitude) VALUES ( :all_prop_id, :address1, :address2, :city, :state, :zip_code, :country, 
        :latitude, :longitude)";

        $stmt = $extDb->prepare($sql);

        $stmt->bindParam(':all_prop_id', $all_prop_id, PDO::PARAM_INT);
        $stmt->bindParam(':address1', $PropAdd['address']['address1'], PDO::PARAM_STR);
        $stmt->bindParam(':address2', $PropAdd['address']['address2'], PDO::PARAM_STR);
        $stmt->bindParam(':city', $PropAdd['address']['city'], PDO::PARAM_STR);
        $stmt->bindParam(':state', $PropAdd['address']['state'], PDO::PARAM_STR);
        $stmt->bindParam(':zip_code', $PropAdd['address']['zip_code'], PDO::PARAM_STR);
        $stmt->bindParam(':country', $PropAdd['address']['country'], PDO::PARAM_STR);
        // use PARAM_STR although a number
        $stmt->bindParam(':latitude', $PropAdd['address']['latitude'], PDO::PARAM_STR);
        $stmt->bindParam(':longitude', $PropAdd['address']['longitude'], PDO::PARAM_STR);

        $stmt->execute();

        echo 'Address Added <br>';
    } // end foreach
} // end !empty
$extDb = null;
}
?>

So to reiterate, the question here is not to identify what is wrong with my code as other than the speed, it is actually working fine. I would like to know if someone could identify the best way to make this dynamic to avoid having to tediously write the code 40 + times?

If anything is unclear, please let me know.

Cheers, bench.

Answer 1

You are creating the prepared statements inside the foreach loop. Try to create the prepared statement outside of it. The idea of a prepared statement is that you prepare the statement once and execute it multiple times with different parameter values. This way the database only have to compile and optimize the SQL query once, which is more efficient than doing it foreach iteration.

if (!empty($houses)) {
    $stmt = $extDb->prepare("SELECT * FROM travel_a_property WHERE travel_prop_id = :travel_prop_id");
    //$stmt2 = ...
    foreach ($houses as $travel_Prop) {

        $prop = $travel_Prop['data'][0]; // Need to check this!

        if ($prop['id'] > '0') { // Ignore empty arrays
            if ($stmt->execute(array(':travel_prop_id' => $prop['id']))) {
                $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
                //Do something with $result
            }
        }
        //$stmt2->execute(...);
    }
}