Ankündigung

Einklappen
Keine Ankündigung bisher.

Insert Into problem

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • Andastra
    hat ein Thema erstellt Insert Into problem.

    Insert Into problem

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

    Code:
    {"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?

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

    $dataArray:
    Code:
    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:
    PHP-Code:
    //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" ,") . ")";

    Einen Kommentar schreiben:


  • akretschmer
    antwortet
    Zitat von Andastra Beitrag anzeigen
    ...[/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:

    Code:
    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)

    Einen Kommentar schreiben:


  • rkr
    antwortet
    ... und das führst du wie aus?

    Einen Kommentar schreiben:


  • Andastra
    antwortet
    achja genau hier das sql
    Code:
    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')

    Einen Kommentar schreiben:


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

    Einen Kommentar schreiben:

Lädt...
X