chinese character can't be table column name.

chinese character can't be table column name.

victoryau88victoryau88 Posts: 13Questions: 0Answers: 0


Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT ID as 'ID' FROM aaa WHERE ID = :where_0 ","bindings":[{"name":":where_0","value":"3","type":null}]},{"query":"UPDATE aaa SET Avatar = :Avatar, Name = :Name, \u5730\u5740 = :\u5730\u5740 WHERE ID = :where_0 ","bindings":[{"name":":Avatar","value":"68e1684d-0af7-44ab-8a5d-db72d26b95d08373945243958866952.jpg","type":null},{"name":":Name","value":"ddd","type":null},{"name":":\u5730\u5740","value":"gggg","type":null},{"name":":where_0","value":"3","type":null}]}]}
Description of problem:
I am newbee of the Editor, I have succesed make column dynamic, however, there is problem when my table's columns cotain chinese character, the problems were accounted in New insert and Edit.

Anyway, when I tried Editor table generator (as shown in pic) with chinese character column, it remind me "SQL column name: Not set"

It is running well when all columns are English character, May I know how to solve it?

Thanks.

Replies

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

    Hi,

    If you click on the red text just below the entry, you will get the option of setting the SQL column name, which I think will need to be ASCII (based on the limits of MySQL).

    Allan

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0

    I've succes made SQL column name: 中文, however, when I click run, it shows

    Database column names must only contain letters, numbers, underscores or dashes.

    Anythings I am doing wrong?

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

    It looks like our validation might be somewhat too strict when automatically generating a column name.

    As a workaround at the moment, until I fix this, if you use ASCII character, download the package and then modify the HTML (and SQL / PHP if you want to change the column names in the db as well) then that should work.

    Allan

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0

    Hi Allan

    I still not figure out how to NEW/Edit with chinese column name, attached pls find chromo tool (initialer) for the normal english column edit (long one) and chinese column edit (short one), it seems it is problem come from js, thanks.

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

    Can you show me your PHP and give me a link to your page please?

    Thanks,
    Allan

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0
    edited November 2021

    Dear Allan

    Here is Javascript / output html ran by php

    <?php
    //ini_set('display_errors', 1);
    //ini_set('display_startup_errors', 1);
    //error_reporting(E_ALL);
    $htmlHeader='<!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8">
        <link rel="shortcut icon" type="image/ico" href="https://www.datatables.net/favicon.ico">
        <meta name="viewport" content="width=device-width, initial-scale=1, minimum-scale=1.0, user-scalable=no">
        <title>ecotag.hk</title>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.0/css/jquery.dataTables.min.css">
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/2.0.0/css/buttons.dataTables.min.css">
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.3.3/css/select.dataTables.min.css">
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/datetime/1.1.1/css/dataTables.dateTime.min.css">
        <link rel="stylesheet" type="text/css" href="./css/editor.dataTables.min.css">
        <link rel="stylesheet" type="text/css" href="./resources/syntax/shCore.css">
        <link rel="stylesheet" type="text/css" href="./resources/demo.css">
        <style type="text/css" class="init">    </style>
        <script type="text/javascript" language="javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
        <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.11.0/js/jquery.dataTables.min.js"></script>
        <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/2.0.0/js/dataTables.buttons.min.js"></script>
        <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/select/1.3.3/js/dataTables.select.min.js"></script>
        <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/datetime/1.1.1/js/dataTables.dateTime.min.js"></script>
        <script type="text/javascript" language="javascript" src="./js/dataTables.editor.js"></script>
        <script type="text/javascript" language="javascript" src="./resources/syntax/shCore.js"></script>
        <script type="text/javascript" language="javascript" src="./resources/demo.js"></script>
        <script type="text/javascript" language="javascript" src="./resources/editor-demo.js"></script>';
    
    
    $htmlHeader2='</head><body class="dt-example php">
        <div class="container">';
        require "resizeClass.php";
        $servername = "localhost";
        $username = "XXXXX";
        $password = "XXXXXX";
        $dbname = "XXXX";
    
    if ($_SERVER['REQUEST_METHOD'] == 'GET'){ 
    $action=$_GET['action'];
    $table = $_GET['table'];
    $tableparm = $_GET['tableparm'];
    
    }
    
    $avatarfolder='./'.$table.'/avatar';
        // for get about $avatarfolder and code, it is reserve for flutter apps.
    
    
         
        // Create Connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check Connection
        if($conn->connect_error){
            die("Connection Failed: " . $conn->connect_error);
            return;
        }
     
    
    
    
    $echoStringHeader='';
    $documentfields='';
    $documentColumns='';
            $sqlA = "SELECT * from $tableparm order by columnOrder + 0 ASC";
            $col=array();
            $types=array();
            $resultA = $conn->query($sqlA);
            if($resultA->num_rows > 0){
                while($rowA = $resultA->fetch_assoc()){
               
               $col[]=$rowA["cols"];
             if($rowA["types"]=="avatar"){
               $echoStringHeader= $echoStringHeader.'<th>'.$rowA["cols"].'</th>';   
             $documentfields=$documentfields.'{"label":"'.$rowA["cols"].':","name": "'.$rowA["cols"].'",  type: "upload",
                    display: function ( file_id ) {
                        return '.'\'<img height=50 src="\'+editor.file(  \'files\', file_id  ).web_path+\'"/>\';
                    },
                    clearText: "Clear",
                    noImageText: "No image"
                },';
           $documentColumns=$documentColumns.'{ data: "'.$rowA["cols"].'", render: function ( file_id ) {
                        return file_id ?
                            \'<img height=50 src="\'+editor.file(  \'files\', file_id  ).web_path+\'"/>\' :
                            null;
                    },
                    defaultContent: "No image",
                    title: "Image" },';
        
    }else{
    
           $echoStringHeader= $echoStringHeader.'<th>'.$rowA["cols"].'</th>';   
            $documentfields=$documentfields.'{"label":"'.$rowA["cols"].':","name": "'.$rowA["cols"].'"},';
           $documentColumns=$documentColumns.'{ data: "'.$rowA["cols"].'" },';
    
    } // end of avatar
        
                 $types[]=$rowA["types"];
    
               } // end of fetch_assoc
    
    
    $getparm='?table='.$table.'&tableparm='.$tableparm.'&action=GET_ALL';
    
    } // end of num rows >0
    
    
    
            $sql = "SELECT * from $table ORDER BY id DESC";
            $result = $conn->query($sql);
            if($result->num_rows > 0){
       
    
    for ($j=0; $row = $result->fetch_assoc(); $j++) {
    for ($i=0;$i<count($col);$i++){
    if($types[$i]=='time'||$types[$i]=='date'||$types[$i]=='timestamp')
    $row[$col[$i]]=date("Y-m-d", strtotime($row[$col[$i]]));
    if($types[$i]=="avatar"){
    $_path='./'.$avatarfolder.'/'.$row[$col[$i]];
    $_thumbpath='./'.$avatarfolder.'/'.$row[$col[$i]].'thumb.jpg';
    if (!file_exists($_thumbpath)) {    
    file_put_contents($_thumbpath, base64_decode(resizeImage($_path)));
    }  // end of thumbpath
    
    }else{
    
    } //end of type[$i]== avatar
    
    $db_data['set'.$i]=$row[$col[$i]];
    
    }//end of for ($i=0;$i<count($col);$i++)
    
    
    } //end of for loop fetch_assoc()
                // Send back the complete records as a json
     
            }else{
             //   echo "error";
            }// end of $result->num_rows > 0
            
       
     $conn->close();
     
          
    $editorset='<script type="text/javascript" language="javascript" class="init">
    
    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor( {
            "ajax": "./controllers/ecotag.php'.$getparm.'",
            "contentType": "UTF-8",
            "table": "#'.$table.'",
            "fields": ['.$documentfields.'
            ]
        } );
    
        $("#'.$table.'").DataTable( {
            dom: "Bfrtip",
            ajax: {
                url: "./controllers/ecotag.php'.$getparm.'",
                type: "POST"
    
            },
            serverSide: true,
            columns: ['.$documentColumns.'
                
            ],
            select: true,
            
            "contentType": "UTF-8",
            buttons: [
                { extend: "create", editor: editor },
                { extend: "edit",   editor: editor },
                { extend: "remove", editor: editor }
            ]
        } );
    } );
    
        </script>';
    echo $htmlHeader.$editorset.$htmlHeader2;
    echo '<table id="'.$table.'" class="display" style="width:100%"><br><thead><tr>'.$echoStringHeader.'</tr></thead>';
    echo '<tfoot><tr>'.$echoStringHeader.'</tr></tfoot>';  
    echo "</table></div></body> </html>"; 
      
    ?>
    

    Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0
    edited November 2021

    Here is server side script with some php function, filename: ecotag.php

    <?php
    
    /*
     * Example PHP implementation used for the index.html example
     */
    //ini_set('display_errors', 1);
    //ini_set('display_startup_errors', 1);
    //error_reporting(E_ALL);
    
    
        $servername = "localhost";
        $username = "XXXXX";
        $password = "XXXXX";
        $dbname = "XXXX";
    
    $action=$_GET['action'];
    $table = $_GET['table'];
    
    $tableparm = $_GET['tableparm'];
    
    $avatarfolder='./'.$table.'/avatar';
    
    
         
        // Create Connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check Connection
        if($conn->connect_error){
            die("Connection Failed: " . $conn->connect_error);
            return;
        }
     
        // If connection is OK...
    
    if("GET_ALL" == $action){
    $echoStringHeader='';
    $documentfields='';
    $documentColumns='';
    $avatarColumnName="not set";
    
    $FieldinstArray=array();
            $sql = "SELECT * from $tableparm order by columnOrder + 0 ASC";
            $col=array();
            $types=array();
            $result = $conn->query($sql);
            if($result->num_rows > 0){
                while($row = $result->fetch_assoc()){
               
               $col[]=$row["cols"];
             
               $echoStringHeader= $echoStringHeader.'<th>'.$row["cols"].'</th>';   
             $documentfields=$documentfields.'{"label":"'.$row["cols"].':","name": "'.$row["cols"].'"},';
           $documentColumns=$documentColumns.'{ data: "'.$row["cols"].'" },';
    if($row["cols"]=="ID"){
    
    }elseif($row["types"]=="avatar"){
    $avatarColumnName=$row['cols'];
    }else{
    $FieldinstArray[] =  $row['cols'];
    }
                 $types[]=$row["types"];
    
                }
    $documentColumns=rtrim($documentColumns, ',');
    $documentfields=rtrim($documentfields, ',');
    }
    
    
        
            $sql = "SELECT * from $table ORDER BY id DESC";
            $result = $conn->query($sql);
            if($result->num_rows > 0){
    
    
               
    $echoStringContents='';
    for ($j=0; $row = $result->fetch_assoc(); $j++) {
    
    
    $echoStringContents= $echoStringContents.'<tr>';
    
    for ($i=0;$i<count($col);$i++){
    if($types[$i]=='time'||$types[$i]=='date'||$types[$i]=='timestamp')
    $row[$col[$i]]=date("Y-m-d", strtotime($row[$col[$i]]));
    if($types[$i]!="avatar"){
    $echoStringContents= $echoStringContents.'<th>'.$row[$col[$i]].'</th>';
    }else{
    $_path='./'.$avatarfolder.'/'.$row[$col[$i]];
    $_thumbpath='./'.$avatarfolder.'/'.$row[$col[$i]].'.thumb.jpg';
    
    
    $echoStringContents= $echoStringContents .'<th> <img src="'.$_thumbpath.'" height=50 alt="img"></img></th>';
    }
    
    
    
    $echoStringContents= $echoStringContents. '</tr>';        
    
    }
            
     
    $echoStringContents= $echoStringContents;    
    
    
    }
    }
    }
    // DataTables PHP library
    include( "../lib/DataTables.php" );
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
    $sql_tabla = $db->quote( $_GET['table'] );
    $sql_tabla = str_replace("'", "", $sql_tabla);
    
    $primary_key='ID';
    $editor = Editor::inst($db,$sql_tabla, $primary_key);
    $editor->fields(Field::inst('ID')->set(false));
    if($avatarColumnName!= "not set" ){
    $editor->fields(  Field::inst($avatarColumnName )
                ->setFormatter( Format::ifEmpty( null ) )
                ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/digi/uploads/__ID__.__EXTN__' )
                    ->db( 'files', 'ID', array(
                        'filename'    => Upload::DB_FILE_NAME,
                        'filesize'    => Upload::DB_FILE_SIZE,
                        'web_path'    => Upload::DB_WEB_PATH,
                        'system_path' => Upload::DB_SYSTEM_PATH
                    ) )
                    ->validator( Validate::fileSize( 5000000, 'Files must be smaller that 5mb' ) )
                    ->validator( Validate::fileExtensions( array( 'png', 'jpg', 'jpeg', 'gif' ), "Please upload an image" ) )
                ));
    }
    
    foreach ($FieldinstArray as $field) {
        $editor->fields(Field::inst($field));
    }
    $editor->debug(true)
         ->process($_POST)
        ->json();
    

    **** kindly help me to check/ findout the problem I got, thank you Allan`

    Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0
    edited November 2021

    here is config.php

    <?php if (!defined('DATATABLES')) exit(); // Ensure being used in DataTables env.
    
    
    // Enable error reporting for debugging (remove for production)
    //error_reporting(E_ALL);
    //ini_set('display_errors', '1');
    
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Database user / pass
     */
    $sql_details = array(
        "type" => "Mysql",     // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
        "user" => "xxxxxx",          // Database user name
        "pass" => "xxxxxx",          // Database password
        "host" => "localhost", // Database host
        "port" => "",          // Database connection port (can be left empty for default)
        "db"   => "xxxxx",          // Database name
        "dsn"  => "charset=utf8mb4",        // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
        "pdoAttr" => array()   // PHP PDO attributes array. See the PHP documentation for all options
    );
    
    
    // This is included for the development and deploy environment used on the DataTables
    // server. You can delete this block - it just includes my own user/pass without making 
    // them public!
    if ( is_file("./datatables/pdo.php") ) {
        include "./datatables/pdo.php";
    }
    // /End development include
    

    Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0
    edited November 2021

    Hi Allan
    for server side script ecotag.php, I removed unnecessary code that may help you to review, thx.

    <?php
    
        $servername = "localhost";
        $username = "";
        $password = "";
        $dbname = "";
    
        $action=$_GET['action'];
        $table = $_GET['table'];
    
        $tableparm = $_GET['tableparm'];
        $avatarfolder='./'.$table.'/avatar';
    
    
         
        // Create Connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check Connection
        if($conn->connect_error){
            die("Connection Failed: " . $conn->connect_error);
            return;
        }
     
        // If connection is OK...
    
    if("GET_ALL" == $action){
    $avatarColumnName="not set";
    
    $FieldinstArray=array();
            $sql = "SELECT * from $tableparm order by columnOrder + 0 ASC";
            $col=array();
            $types=array();
            $result = $conn->query($sql);
            if($result->num_rows > 0){
                while($row = $result->fetch_assoc()){
               
               $col[]=$row["cols"];
              
          if($row["cols"]=="ID"){
    
    }elseif($row["types"]=="avatar"){
    $avatarColumnName=$row['cols'];
    }else{
    $FieldinstArray[] =  $row['cols'];
    }
                 $types[]=$row["types"];
    
                }
    }
         
    }
    // DataTables PHP library
    include( "../lib/DataTables.php" );
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
    $sql_tabla = $db->quote( $_GET['table'] );
    $sql_tabla = str_replace("'", "", $sql_tabla);
    
    $primary_key='ID';
    $editor = Editor::inst($db,$sql_tabla, $primary_key);
    $editor->fields(Field::inst('ID')->set(false));
    
    if($avatarColumnName!="not set"){
    
    $editor->fields(  Field::inst($avatarColumnName )
                ->setFormatter( Format::ifEmpty( null ) )
                ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/digi/uploads/__ID__.__EXTN__' )
                    ->db( 'files', 'ID', array(
                        'filename'    => Upload::DB_FILE_NAME,
                        'filesize'    => Upload::DB_FILE_SIZE,
                        'web_path'    => Upload::DB_WEB_PATH,
                        'system_path' => Upload::DB_SYSTEM_PATH
                    ) )
                    ->validator( Validate::fileSize( 5000000, 'Files must be smaller that 5mb' ) )
                    ->validator( Validate::fileExtensions( array( 'png', 'jpg', 'jpeg', 'gif' ), "Please upload an image" ) )
                ));
    }
    foreach ($FieldinstArray as $field) {
        $editor->fields(Field::inst($field));
    }
    $editor->debug(true)
         ->process($_POST)
        ->json();
    

    Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

    Thanks for all the code. A little more I'm afraid - could you show me the rendered Javascript for DataTables / Editor? Even better would be a link to the page as I could diagnose that directly.

    Thanks,
    Allan

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0

    Hi Allan

    Yes, kindly help me to diagnose these two links, thanks.

    https://www.ecotag.hk/digi/dynamicWebTableEditable_beta.php?table=bbb (normal)
    https://www.ecotag.hk/digi/dynamicWebTableEditable_beta.php?table=aaa (problem)

    Thanks Allan

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

    Thank you! It looks like it might be a problem with using non-ASCII characters for the PDO bindings:

    UPDATE `aaa` SET `Avatar` = :Avatar, `Name` = :Name, `地址` = :地址 WHERE `ID` = :where_0
    

    Could you try the following for me please?

    $sql = "SELECT `Name` from `aaa` WHERE `Name` = :地址";
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':地址', 'Test');
    $stmt->execute();
    

    Where $dbh is a PDO database handle. If you don't have one already, you could use the one created by Editor - $db->resource().

    My guess is that will give an error.

    Thanks,
    Allan

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0

    Hi Allan

    I am not familiar to use PDO, can you provide a php pdo file code for me, thx.

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0

    Hi Allan
    I got pdo test script online. and I guess it may be php version problem, I now trying to figure out.

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0

    Hi Allan

    I upgraded php from 7.2.2.4 to 7.3, still not ok,
    then 7.4, the result still not OK.
    Next step upgrade will be a big change that I am I afraid my source code will be affacted by PHP version 8.0.

    it seems it is not come form php version differs.

    I am not familiar with PDO, would you provide simple php code for me to running requested test in my server, thanks.

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin
    include( "lib/DataTables.php" );
    
    $dbh = $db->resource();
    
    $sql = "SELECT `Name` from `aaa` WHERE `Name` = :地址";
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':地址', 'Test');
    $stmt->execute();
    

    What does that result in? (where lib/DataTables.php is the same as what you'd use for your Editor PHP scripts).

    Allan

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0
    edited November 2021

    HI Allan

    I am giving up to findout the non-ASCII code problem.
    Currentlly I am setting all column names to set0, set1, set2......... dynamiclly, then use another table as table parm to match back the correct column name, though this I got extra advance, any character, space can be as a title of column name.

    There is minor problem, when I do new and edit, the dialog still show set0, set1.... as shown in picture, could you tell me how can I replace these name in New, Edit Dialog, thanks.

  • victoryau88victoryau88 Posts: 13Questions: 0Answers: 0

    Hi Allan
    Please close this thread, I just find out by myself, it only need modify label to corresponding value.

    Have a nice day, so happy.....

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Excellent, glad it's sorted, thanks for posting back,

    Colin

Sign In or Register to comment.