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

Dizzy49

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.

IMSoP

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
    )
);

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Unable to Update rows of DataGridView from DB Query

From Dev

Unable to get Result from the query

From Dev

simpleXML get value from CDATA

From Dev

How to get specific field from DB query?

From Dev

Get all scope variables from query parameters

From Dev

How to dynamically get variables from query in PHP

From Dev

Query all variables from myPHP db into array for later use in HTML

From Dev

Query all variables from myPHP db into array for later use in HTML

From Dev

How to get DELETE FROM [QUERY] to work?

From Dev

Unable to get results from H2 db

From Dev

Get multiple items from XML with SimpleXML

From Dev

PHP SimpleXML get value from Object

From Dev

SimpleXML to get specific data from an XML file

From Dev

PHP SimpleXML get value from Object

From Dev

Can't get CI autocomplete from DB to work

From Dev

Get instance of Eloquent model from generic DB query in Laravel 5.1

From Dev

server timeout on Dynamo DB query operation to get items from a table

From Dev

server timeout on Dynamo DB query operation to get items from a table

From Dev

DB Query related to get data from 2 tables which are not null

From Dev

suggestions on query to get counts from other tables on a postgres db

From Dev

Unable to get the vlookup to work

From Dev

Unable to get Aggregator to work

From Dev

unable to get useUnsafeHeaderParsing to work

From Dev

Unable to get the vlookup to work

From Dev

Unable to get Aggregator to work

From Dev

Unable to get the Query Results

From Dev

Unable to get Results of a query

From Dev

PHP EasyRDF : unable to get graph from construct query

From Dev

How to get variables from MSQL query with WHERE IN clause

Related Related

HotTag

Archive