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.