Insert local XML data to 2 MySQL tables via PHP script

Question

I'm attempting to cherry pick elements from a large xml file (inventory for a boat dealer with all the specs for each unit) and push that data into two separate tables. I don't know if I need two Xpaths to do this or if it can be compressed into one.

The XML file holds about 50+ elements with child elements per boat but I wanted to take this one step at a time until I get the hang of doing this and left it at three, as an example, before adding complexities.

I used this previous answer to model the script so far and added what I needed:

<?php
$db = new mysqli('localhost', 'username', 'password', 'database');
$boats=simplexml_load_file("WinboatsWebXMLAllRevA.xml") or die ("Error: Cannot create object");
$values = <<<XPATH
(
    |element
    |element
    |element
    )
XPATH;
$pattern_custom = <<<SQL
 INSERT INTO pmb_rsdirectory_entries_custom
(
    column1, column2, column3
)
VALUES
(
    '%s', '%s', '%s'
)
SQL;
foreach ($boats as $boat)
{
    $data = $boat->xpath($values);
    $escaped = array_map('mysql_real_escape_string', $data);
    $query = vsprintf($pattern, $escaped);

$result - mysql_query($query);
if(mysql_errno())
{
    printf(
        '<h4 style="color: red;">Query Error:</h4>
        <p>(%s) - %s</p>
        <p>Query:
            <pre>%s</pre>
        </p>
        </hr />',
        mysql_errno(),
        htmlspecialchars(mysql_errno()),
        htmlspecialchars($query)
    );
  }
}
$values_custom = <<<XPATH
(
    |element3
    |element4
    |element5

 )
XPATH;
$pattern_custom = <<<SQL
INSERT INTO pmb_rsdirectory_entries
    (
        column3, column4, column5
    )
    VALUES
    (
    '%s', '%s', '%s'
)
SQL;
foreach ($boats as $boat)
{
    $data = $boat->xpath($values_custom);
    $escaped = array_map('mysql_real_escape_string', $data);
    $query = vsprintf($pattern_custom, $escaped);

$result - mysql_query($query);
if(mysql_errno())
{
    printf(
        '<h4 style="color: red;">Query Error:</h4>
        <p>(%s) - %s</p>
        <p>Query:
            <pre>%s</pre>
        </p>
        </hr />',
        mysql_errno(),
        htmlspecialchars(mysql_errno()),
        htmlspecialchars($query)
        );
     }
}

?>

If there is a better way to manage this I am certainly open to suggestions.

Update (XML Snippet):

<document>
 <node type="boat">
  <uniqueid>ID00004146</uniqueid>
  <category>Ski and Wakeboard Boat</category>
  <boatyear>2015</boatyear>
  <make>Malibu</make>
  <model>Wakesetter 22 VLX</model>
  <length units="feet">22.8</length>
  <total>89630.00</total>
  <engines>Single</engines>
  <enginetype>Other</enginetype>
  <fueltype>Gas</fueltype>
  <hulltype>Fiberglass reinforced</hulltype>
  <engine_manfacturer>Indmar</engine_manfacturer>
  <eng_model>Monsoon 350HP 5.7L CAT</eng_model>
  <eng_hp>350</eng_hp>
  <newused>New</newused>
  <availability>Out of Stock</availability>
  <options>
   <option>Ballast HI FLO - Bow Malibu Launch System</option>
   <option>Ballast HI FLO - Rear PNP Plumbing</option>
   <option>Pull Up Cleats - Two Pair</option>
   <option>Wakesurf Technology (Same Color as Swim Platform)</option>
  </options>
  <pictures>
   <picture>ID00004146_1.jpg</picture>
   <picture>ID00004146_2.jpg</picture>
   </pictures>
 </node>
</document>

Answer 1

What I'd do is in a single foreach loop grab all the info for each XML row and populate two strings or arrays for the two different tables with the info for the VALUES clause of the INSERT INTO and then out of the foreach run the two INSERT INTO statements with all the rows:

INSERT INTO pmb_rsdirectory_entries_custom
(
    column1, column2, column3
)
VALUES
(
    'value1', 'value2', 'value3'
),
(
    'value1', 'value2', 'value3'
) ...


INSERT INTO pmb_rsdirectory_entries
(
    column4, column5, column6
)
VALUES
(
    'value4', 'value5', 'value6'
),
(
    'value4', 'value5', 'value6'
) ...

If you provide me with a short XML as an example, I may build the php code for you, but it won't be hard for you to figure it out. :)

Update (my version of the PHP script):

Sorry, it took me ages to have some time to create the script. Here is what I'd do.

Let's say you want to save the values uniqueid, category, boatyear, make, model in one table, and uniqueid, length, total, engines, enginetype in another. I've added the uniqueid to both tables, but you might want to use an autoincrement field as key to keep records linked. That's homework for you. :)

<?php
$boats = simplexml_load_file( "WinboatsWebXMLAllRevA.xml" ) or die ( "Error: Cannot create object" );
$values = <<<XPATH
(
    uniqueid
    |category
    |boatyear
    |make
    |model
    |length
    |total
    |engines
    |enginetype
)
XPATH;

$valuesFirstTable = array(); //sql row values for the first table
$valuesSecondTable = array(); //sql row values for the second table

foreach ( $boats as $boat )
{
    $data = $boat->xpath( $values );
    $escaped = array_map( 'mysql_real_escape_string', $data );

    $valuesFirstTable[] = "('" . implode( "','", array_slice( $escaped, 0, 5 ) ) . "')"; //includes the uniqueid as first element
    $valuesSecondTable[] = "('" . $escaped[0] . "','" . implode( "','", array_slice( $escaped, 5 ) ) . "')";
}

//execute first sql
$values = implode( ',', $valuesFirstTable );
$query = (
    "INSERT INTO
    t_first_table
    (
        uniqueid,
        category,
        boatyear,
        make,
        model
    )
    VALUES
    {$values}"
);

//this can be a function
$result = mysqli_query( $query );
if ( mysqli_errno() )
{
    printf(
        '<h4 style="color: red;">Query Error:</h4>
        <p>(%s) - %s</p>
        <p>Query:
            <pre>%s</pre>
        </p>
        </hr />',
        mysqli_errno(),
        htmlspecialchars( mysqli_errno() ),
        htmlspecialchars( $query )
    );
}

//execute second sql
$values = implode( ',', $valuesSecondTable );
$query = (
    "INSERT INTO
    t_second_table
    (
        uniqueid,
        `length`,
        total,
        engines,
        enginetype,
    )
    VALUES
    {$values}"
);

//this can be a function
$result = mysqli_query( $query );
if ( mysqli_errno() )
{
    printf(
        '<h4 style="color: red;">Query Error:</h4>
        <p>(%s) - %s</p>
        <p>Query:
            <pre>%s</pre>
        </p>
        </hr />',
        mysqli_errno(),
        htmlspecialchars( mysqli_errno() ),
        htmlspecialchars( $query )
    );
}