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

  • 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?

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

    Kommentar


    • #3
      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')

      Kommentar


      • #4
        ... und das führst du wie aus?

        Kommentar


        • #5
          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)
          PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

          Kommentar


          • #6
            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" ,") . ")";

            Kommentar

            Lädt...
            X