Insert Into problem

[Andastra]

hallo Leute,
ich teste nun einige stunden schon herum ich hab folgendes JSON string das ich versuche in die DB zu speichern:

{"child":{"JTL_5":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"l"},"JTL_6":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"m"},"JTL_4":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"xl"},"JTL_8":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"l"},"JTL_9":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"m"},"JTL_7":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"xl"},"JTL_11":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"l"},"JTL_12":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"m"},"JTL_10":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"xl"},"JTL_2":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"l"},"JTL_3":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"m"},"JTL_1":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"xl"}}}

ich lasse es in die db schreiben und nun wird aus Gr\\u00fcn plötzlich Gru00fcn kann mir jemand sagen woran das liegen könnte?

[rkr]

Nicht ohne zu wissen, wie das in die Datenbank kommt.

[Andastra]

achja genau hier das sql

INSERT INTO product (`customfield_id`,`product_id`,`custom_id`,`custom_value`,`custom_param`,`custom_price`,`published`,`ordering`)VALUES(NULL, '2317', '10', NULL, '{"child":{"JTL_5":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"l"},"JTL_6":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"m"},"JTL_4":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"xl"},"JTL_8":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"l"},"JTL_9":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"m"},"JTL_7":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"xl"},"JTL_11":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"l"},"JTL_12":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"m"},"JTL_10":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"xl"},"JTL_2":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"l"},"JTL_3":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"m"},"JTL_1":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"xl"}}}', NULL, '1', '1')

[rkr]

... und das führst du wie aus?

[akretschmer]

...[/CODE]

ich lasse es in die db schreiben und nun wird aus Gr\\u00fcn plötzlich Gru00fcn kann mir jemand sagen woran das liegen könnte?

works for me:

test=*# select * from json_test ;
 id |



                                                                            json_data





----+------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-------------------------------------
  1 | {"child":{"JTL_5":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"l"},"JTL_6":{"is_v
ariant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"m"},"JTL_4":{"is_variant":"1","selectoptions1":"
Blau","custom_price":4,"selectoptions2":"xl"},"JTL_8":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selec
toptions2":"l"},"JTL_9":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"m"},"JTL_7":{"is_v
ariant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"xl"},"JTL_11":{"is_variant":"1","selectoptions1"
:"Gr\\u00fcn","custom_price":2,"selectoptions2":"l"},"JTL_12":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_p
rice":2,"selectoptions2":"m"},"JTL_10":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2
":"xl"},"JTL_2":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"l"},"JTL_3":{"is_variant":"
1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"m"},"JTL_1":{"is_variant":"1","selectoptions1":"Rot","cust
om_price":5,"selectoptions2":"xl"}}}
(1 row)

test=*# select json_data #> '{child,JTL_10}' from json_test where id = 1;
                                        ?column?
-----------------------------------------------------------------------------------------
 {"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"xl"}
(1 row)

[Andastra]

mom das ausführen ist etwas komlizierter und wird von Joomla 2.5 verarbeitet:

$dataArray:

Array
(
    [table] => uu8zc_product_customfields
    [colums] => Array
        (
            [customfield_id] =>           //wenn leer Insert sonst update
            [product_id] => 2317
            [custom_id] => 10
            [custom_value] => 
            [custom_param] => {"child":{"JTL_5":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"l"},"JTL_6":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"m"},"JTL_4":{"is_variant":"1","selectoptions1":"Blau","custom_price":4,"selectoptions2":"xl"},"JTL_8":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"l"},"JTL_9":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"m"},"JTL_7":{"is_variant":"1","selectoptions1":"Gelb","custom_price":3,"selectoptions2":"xl"},"JTL_11":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"l"},"JTL_12":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"m"},"JTL_10":{"is_variant":"1","selectoptions1":"Gr\\u00fcn","custom_price":2,"selectoptions2":"xl"},"JTL_2":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"l"},"JTL_3":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"m"},"JTL_1":{"is_variant":"1","selectoptions1":"Rot","custom_price":5,"selectoptions2":"xl"}}}
            [custom_price] => 
            [published] => 1
            [ordering] => 1
        )

    [select] => customfield_id
)

ausführung mittels funktionen:

//ausführung
setMapping($dataArray);


function setMapping($data) {
    
    if (is_array($data)) {

        $database=JFactory::getDBO();
        
        //map blacklist to update
        $blacklist = array('vm_product_Id');
        
        if ($data['select']) {
            
            if ($data['colums']['vendor_ID']) $vendorSelect = "AND `vendor_ID`='".$data['colums']['vendor_ID']."'";
            
            $where = ($data['where']) ? $data['where'] : "`".$data['select']."` = '".$data['colums'][$data['select']]."'";

            $query = "SELECT `".$data['select']."` FROM ".$data['table']."";
            $query .= "\n WHERE $where ".$data['and']." ".$vendorSelect." LIMIT 1";

            $database->setQuery( $query );            
            $mapCheck = $database->loadResult();

            if (!$mapCheck) {
                
                $inTable     = "";

                foreach ($data['colums'] as $key => $value) {

                    $inTable[] = "`".$key."`";
                    
                }
                
                echo $insert = "INSERT INTO ".$data['table']." (".implode($inTable, ',').")".arrayToSqlValues($data['colums']);

                $database->setQuery( $insert );
                $database->query();
                
                return $database->insertid();
                
            } else {
                
                foreach ($data['colums'] as $key => $value) {
                    
                    if (!in_array($key, $blacklist))
                        $inTable[] = "$key='$value'";
                    
                }
                
                unset($inTable[$data['select']]); 
                
                $update = "UPDATE ".$data['table']." SET ".implode($inTable, ',')." WHERE ".$data['select']."='$mapCheck' ";

                $database->setQuery( $update );
                $database->query();
                
                return $mapCheck;

            }
            
        } else {
            
            $inTable     = "";
            
            foreach ($data['colums'] as $key => $value) {
            
                $inTable[] = "`".$key."`";
                    
            }
            
            $insert = "INSERT INTO ".$data['table']." (".implode($inTable, ',').")".arrayToSqlValues($data['colums']);
            
            $database->setQuery( $insert );
            $database->query();
            
            return $database->insertid();
            
        }
        
    }
    
}


function arrayToSqlValues($array)
{
    $sql = "";
    foreach($array as $val)
    {
        //adding value
        if($val === NULL)
            $sql .= "NULL";
        else
            $sql .= "'" . $val . "'";

        $sql .= ", ";
    };

    return "VALUES(" . rtrim($sql, " ,") . ")";
}