Insert multiple rows to an MySQL table from XML file using PHP

If you consider to insert multiple rows (or even a single one) consider to use prepared statement. Let's see step by step.

First of all let's check that the connection went well:

if ($link->connect_errno) {
    throw new Exception(sprintf("Mysqli: (%d): %s", $link->connect_errno, $link->connect_error));
}

This step might be superfluous because you did that already. In case not, this shows how $link can be checked for a successful Mysqli connection.

The next useful idea is to create a mapping. This works for everything but the $postcode in your example automatically. So you might need to extend the following code a little. Bascially the mapping is a XML-Element to Column-Name array:

$xmlToTableMapping = [
    'average_sold_price_1year' => 'price1',
];

Here the <average_sold_price_1year> element is mapped onto price1 column. Extend that array with all fields and a fake-field for the $poscode then.

The next big thing is to prepare the query. That is creating the list of fields, their placeholders and the bound parameters. This is done with the help of the just defined mapping:

$insertMask  = 'INSERT INTO tablename (%s) VALUES (%s)';
$insertTypes = 's'; // for each column one type needs to be set (s = string)

// bind insert values based on mapping by creating references
$insertValues = [];

foreach ($xmlToTableMapping as $column) {
    $insertValues[$column] = '';
    $insertValues[$column] = & $insertValues[$column]; // make this parameter a reference, mysqli needs references
}

// just a little check in between if there is something to insert at all:
if (!$insertValues) {
    throw new Exception('Nothing to insert.');
}

// create and prepare the query
$query = sprintf(
    $insertMask,
    implode(', ', array_keys($insertValues)),
    implode(', ', array_fill(0, count($insertValues), '?'))
);
$stmt  = $link->prepare($query);
if (!$stmt) {
    throw new Exception(sprintf("Mysqli: (%d): %s", $link->errno, $link->error));
}

// bind parameters
$result = call_user_func_array([$stmt, 'bind_param'], [$insertTypes] + $insertValues);
if (!$result) {
    throw new Exception(sprintf("Mysqli: (%d): %s", $stmt->errno, $stmt->error));
}

This did build the SQL string and prepared the query - including the binding of all parameters. After this has been done, all that needs to be done to insert into the database is to set the variables inside the $insertValues array and to execute the query. Again with the help of the mapping the data from the XML can be easily extracted:

// execute the statement per each XML element
foreach ($xml->areas as $area) {
    foreach ($xmlToTableMapping as $xml => $column) {
        $value = $area->$xml;
        if ($value->getName() !== $xml) {
            throw new Exception('Element %s not found in %s.', $xml, $area->asXML());
        }
        $insertValues[$column] = trim($value);
    }

    if (!$stmt->execute()) {
        throw new Exception(sprintf("Mysqli: (%d): %s", $stmt->errno, $stmt->error));
    }
}

This was pretty much a high-level demonstration of how this can be done. Mysqli probably has the downside here that binding parameters for such a dynamic list are not that straight forward. However I can imagine that wrapping this inside a function or two or extending from Mysqli can give this a better interface.

The real benefits of this method outlined here can be seen in the final foreach loop and as well by it's dynamic nature. Normally only the mapping should need some modifications not the rest of the code (much).

I finally got it working like this:

// specify url of xml file
$url = 'http://api.zoopla.co.uk/api/v1/average_sold_prices?area=AL8&area_type=postcodes&output_type=outcode&page_size=20&page_number=1';
// get xml file
echo "Loading XML file...\n";
$xml = simplexml_load_file($url);

// loop
foreach ($xml->areas as $row) {
    $price1 = mysqli_real_escape_string($link,$row->average_sold_price_1year);
    $price3 = mysqli_real_escape_string($link,$row->average_sold_price_1year);
    $price5 = mysqli_real_escape_string($link,$row->average_sold_price_1year);
    $price7 = mysqli_real_escape_string($link,$row->average_sold_price_1year);
    $sales1 = mysqli_real_escape_string($link,$row->number_of_sales_1year);
    $sales3 = mysqli_real_escape_string($link,$row->number_of_sales_1year);
    $sales5 = mysqli_real_escape_string($link,$row->number_of_sales_1year);
    $sales7 = mysqli_real_escape_string($link,$row->number_of_sales_1year);
    $priceurl = mysqli_real_escape_string($link,$row->prices_url);
    $postcode = substr($priceurl,-7);
    $turnover = mysqli_real_escape_string($link,$row->turnover);

$insetquery = "INSERT INTO zoopla (price1, price3, price5, price7, sales1, sales3, sales5, sales7, postcode, turnover)VALUES ('$price1', '$price3', '$price5', '$price7', '$sales1', '$sales3', '$sales5', '$sales7', '$postcode', '$turnover')";
$result = @mysqli_query($link,$insetquery);
}
?>