John Davidson

Message:

I'm trying to find a quick way of adding multiple buildings to a quote. There are just 4 fields required, QuoteID, scope, known hazards and the building row id. The same scope and hazards details are the same for each record.

So say I have the following fields and data:

$Scope: "All rooms in the building"

$knownHazards: "None"

$quoteID: 1234

$buildingsToAdd: "1,2,4,7,8,10,26"

I can use the $buildingsToAdd, turn into an array and loop through and use that to create an Insert Query for each element in the array, but I was hoping there was a better, quicker and more elegant way.

---

My Answer:
One way to achieve this is by using a prepared statement with a single query to insert multiple records at once. Here's an example of how you can do this in PHP:

php
// Define the common fields
$scope = "All rooms in the building";
$knownHazards = "None";
$quoteID = 1234;
$buildingsToAdd = "1,2,4,7,8,10,26";

// Convert the buildingsToAdd string into an array
$buildingIds = explode(",", $buildingsToAdd);

// Create the prepared statement
$query = "INSERT INTO buildings (quoteID, scope, knownHazards, building_row_id) VALUES (?, ?, ?, ?)";
$stmt = $pdo->prepare($query);

// Bind the parameters and execute the query for each building ID
foreach ($buildingIds as $buildingId) {
$stmt->execute([$quoteID, $scope, $knownHazards, $buildingId]);
}

// Close the statement
$stmt->close();


In this code snippet, we first define the common fields for all the records. We then convert the `$buildingsToAdd` string into an array of building IDs. Next, we create a prepared statement for the insert query and bind the parameters for each building ID in the array. Finally, we execute the query for each building ID and close the statement.

This approach allows you to insert multiple records with the same scope and known hazards details in a more efficient and elegant way.