Unable to get DB::query() to work with variables from simpleXML

Question

I am reading an XML file, and using the data to create queries with MeekroDB. Everything looks good with the XML read and parsing, and I confirmed the variables doing simple echos. However, when I build the DB::query() it doesn't seem to be adding the 'criteria_list' array to the query.

Here is my XML code:

<?xml version="1.0" encoding="utf-8"?>
<result>
    <product>
        <data>
            <field>spr_tech1</field>
            <value>S7</value>
            <criteria field="xfield_3">
                <criteria_list>Green</criteria_list>
                <criteria_list>Beige</criteria_list>
            </criteria>
        </data>
        <data>
            <field>spr_tech1</field>
            <value>S1</value>
            <criteria field="xfield_3">
                <criteria_list>Red</criteria_list>
                <criteria_list>Blue</criteria_list>
                <criteria_list>Yellow</criteria_list>
            </criteria>
        </data>
        <data>
            <field>spr_tech1</field>
            <value>S7</value>
            <criteria field="xfield_3">
                <criteria_list>Green</criteria_list>
            </criteria>
            <criteria field="tech_rt2">
                <criteria_list>Transistor</criteria_list>
            </criteria>
        </data>
    </product>
</result>

Here is the code I'm using to read and build the query:

$xml_content = file_get_contents('transformations.xml');
$xform = simplexml_load_string($xml_content);
foreach ($xform->product->data as $data)
{
    echo (string)$data->field . '<br>';
    echo (string)$data->value . '<br>';

    foreach($data->criteria->attributes() as $att => $val)
    { echo $att . ' = ' . $val . '<br>'; }
    echo $data->criteria->attributes()->{'field'} . '<br>';

    foreach($data->criteria->criteria_list as $att => $val)
    { echo $att . ' = ' . $val . '<br>'; }

    echo "-----------------------------------<br>";
}

foreach ($xform->product->data as $data)
{
    DB::query("UPDATE %b_table SET %?_new_field = %?_new_value WHERE %b_criteria_field IN %?_criteria_list", 
        array(
            'table' => 'product', 
            'new_field' => (string)$data->field,
            'new_value' => (string)$data->value,
            'criteria_field' => (string)$data->criteria->attributes()->{'field'},
            'criteria_list' => $data->criteria->criteria_list
        )
    );
}
print "<pre>";
print_r($xform);
print "</pre>";

When I run it, I get the following error: QUERY: UPDATE product SET 'spr_tech1' = 'S7' WHERE xfield_3 IN ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''spr_tech1' = 'S7' WHERE xfield_3 IN' at line 1

I also tried using json_decode(json_encode($obj), true) to convery the XML into an array, but then I couldn't figure out how to access the @attributes field of the 'criteria' node.

Answer 1

Your DB library is expecting an array of strings for the criteria_list placeholder, but you are giving it an object ($data->criteria->criteria_list will be an instance of SimpleXMLElement).

What you need to do is use the foreach loop you wrote for debugging to get the actual strings out of the XML:

foreach($data->criteria->criteria_list as $att => $val)

I'd rename those variables, and write this:

$criteria_strings = [];
foreach($data->criteria->criteria_list as $list_item) {
     $criteria_strings[] = (string)$list_item;
}

Then:

DB::query("UPDATE %b_table SET %?_new_field = %?_new_value WHERE %b_criteria_field IN %?_criteria_list", 
    array(
        'table' => 'product', 
        'new_field' => (string)$data->field,
        'new_value' => (string)$data->value,
        'criteria_field' => (string)$data->criteria['field'],
        'criteria_list' => $criteria_strings
    )
);