chinese character can't be table column name.

victoryau88 · November 2021

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.

allan · November 2021

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

victoryau88 · November 2021

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?

allan · November 2021

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

victoryau88 · November 2021

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.

allan · November 2021

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

Thanks,
Allan

victoryau88 · November 2021

Dear Allan

Here is Javascript / output html ran by php

```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='

$(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 } ] } ); } );

';
echo $htmlHeader.$editorset.$htmlHeader2;
echo '

'.$echoStringHeader.''; echo ''.$echoStringHeader.''; echo "

</div></body> </html>";

<?php > ``` ?>

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

victoryau88 · 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.

victoryau88 · 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.

victoryau88 · 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.

allan · November 2021

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

victoryau88 · November 2021

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

allan · November 2021

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

victoryau88 · November 2021

Hi Allan

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

victoryau88 · November 2021

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

victoryau88 · November 2021

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.

allan · November 2021

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

victoryau88 · 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.

victoryau88 · November 2021

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.....

colin · November 2021

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

Colin

chinese character can't be table column name.

chinese character can't be table column name.

Replies

Howdy, Stranger!

Categories

DataTables

chinese character can't be table column name.

chinese character can't be table column name.

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables