UTF8 problem

UTF8 problem

classic12classic12 Posts: 228Questions: 60Answers: 4
edited August 2018 in DataTables

Hi guys,

I am doing some data scraping and storing the data into MYSQL database.

The field in question is set in MYSQL to UTF8.

I get the following data into the database as

 <div class="panel marTop20">
                <h3>Technical Specs</h3>
                <p>No Load Speed: 2,500-4,200/min.<br>Depth of Cut: @90º: 59mm, @45º: 44mm.<br>With Guide Rails: @90º: 55mm, @45º: 40mm.<br>Blade: 165x20mm Bore<br>Bevel Capacity: 47<br>Weight: 4.7kg</p>
            </div>
            &nbsp;

but the ' deg o' is shown as ' º '

I have tried using decodeURIComponent(escape) in the render but I get error 'URIError: URI error.'

my php is

<?php
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST');  
$quoteID = $_GET["quoteID"];
/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "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;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'products3','productID' )
    ->fields(
        Field::inst( 'products3.productID' )->set(false),
        Field::inst( 'products3.manufacturerCode' ),
        Field::inst( 'products3.TBcost' ),
        Field::inst( 'products3.TBmyCost' ),
        Field::inst( 'products3.CTSmyCost' ),
        Field::inst( 'products3.tradeNett' ),
        Field::inst( 'products3.trade' ),
        Field::inst( 'products3.retail' ),
        Field::inst( 'products3.TBstockCode' ),
        Field::inst( 'products3.barCode' ),
        Field::inst( 'products3.shortDesc' ),
        Field::inst( 'products3.longDesc' ),
        Field::inst( 'products3.category' )
            ->options( Options::inst()
                ->table( 'categories2' )
                ->value( 'catID' )
                ->label( 'name' )
            )//,
        //Field::inst( 'categories2.name' )
    )
    ->join(
        Mjoin::inst( 'fileDetails' )
            ->link( 'products3.productID', 'productFiles.productID' )
            ->link( 'fileDetails.id', 'productFiles.fileID' )
            ->fields(
                Field::inst( 'filename' ),
                Field::inst( 'web_path' ),
                Field::inst( 'id' )
                    ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/upload/__ID__.__EXTN__')
                        ->db( 'fileDetails', '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( function ( $file ) {
                            return$file['size'] >= 10000000 ?
                                "Files must be smaller than 10 meg" :
                                null;
                        } )
                        //->allowedExtensions( array( 'png', 'jpg' ), "Please upload an image" )
                    )
            )
    )
    //->leftJoin( 'sites', 'sites.id', '=', 'products3.site' )
    ->process( $_POST )
    ->json();

I also tried adding this to the bootstrap.php file.

```php
$db->sql("SET character_set_client=utf8");
$db->sql("SET character_set_connection=utf8");
$db->sql("SET character_set_results=utf8");
````

How do I handle this issue please.

Cheers

Steve Warby

Answers

  • rf1234rf1234 Posts: 778Questions: 41Answers: 119
    edited January 2018

    Path is Editor-PHP-.../php/config.php In that file you'll find this:

    $sql_details = array(
        "type" => "Mysql",   // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
        "user" => "",        // Database user name
        "pass" => "",        // Database password
        "host" => "",        // Database host
        "port" => "",        // Database connection port (can be left empty for default)
        "db"   => "",        // Database name
        "dsn"  => "",        // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
        "pdoAttr" => array() // PHP PDO attributes array. See the PHP documentation for all options
    );
    

    Mine looks like this - and I have no problem with utf8.

    $sql_details = array(
        "type" => "Mysql",  // Database type: "Mysql", "Postgres", "Sqlite" or "Sqlserver"
        "user" => "youUserName",       // Database user name
        "pass" => "yourPassword",       // Database password
        "host" => "",       // Database host
        "port" => "",       // Database connection port (can be left empty for default)
        "db"   => "youDatabaseName",       // Database name
        "dsn"  => "charset=utf8"        // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
    );
    

    I ran into a similar issue when I built my own PDO PHP class based on this example: http://culttt.com/2012/10/01/roll-your-own-pdo-php-class/

    The example has this statement which doesn't work for UTF8:

    // Set DSN
    $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
    

    I replaced it with this and it has been working fine ever since:

    define("DB_CHARSET",        "utf8");
    
    private $charset = DB_CHARSET;  
    $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname . ';charset=' . $this->charset;
    
  • classic12classic12 Posts: 228Questions: 60Answers: 4

    Thanks for the help.

    I have added "dsn" => "charset=utf8" but I still get the same issue.

    Cheers

    Steve Warby

  • rf1234rf1234 Posts: 778Questions: 41Answers: 119

    Steve, I tried it myself entered "90°" into a VARCHAR field in one of my tables and it worked out fine.
    The table has the collation "utf8 - utf8_unicode_ci". The column has character set "utf8" and collation "utf8_unicode_ci".

    Maybe you want to check your Mysql table and column definitions?!

  • allanallan Posts: 48,657Questions: 1Answers: 7,068 Site admin

    You could also try adding:

    $db->sql("SET character_set_client=utf8");
    $db->sql("SET character_set_connection=utf8");
    $db->sql("SET character_set_results=utf8");
    

    before your initialise the Editor instance in the PHP.

    Allan

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    Hi Guys,

    I am still having an issue.

    I am using the following code config.php

    <?php if (!defined('DATATABLES')) exit(); // Ensure being used in DataTables env.
    
    // Enable error reporting for debugging (remove for production)
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);
    
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Database user / pass
     */
     
    $sql_details = array(
        "type" => "Mysql",   // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
        "user" => "xxx",        // Database user name
        "pass" => "xxx",        // Database password
        "host" => "localhost",        // Database host
        "port" => "",        // Database connection port (can be left empty for default)
        "db"   => "xxx",        // Database name
        "dsn"  => "charset=utf8",        // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
        //"pdoAttr" => array() // PHP PDO attributes array. See the PHP documentation for all options
    );
    

    anims.php


    <?php header('Access-Control-Allow-Origin: *'); header('Access-Control-Allow-Methods: GET, POST'); ini_set('display_errors', 1); ini_set('display_startup_errors', 1); /* * Example PHP implementation used for the index.html example */ // DataTables PHP library include( "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; // Build our Editor instance and process the data coming from _POST Editor::inst( $db, 'anims' ) ->fields( Field::inst( 'id' ), Field::inst( 'title' ), Field::inst( 'url' ), Field::inst( 'status' ), Field::inst( 'thumbnail' ), Field::inst( 'type' ) ) ->process( $_POST ) ->json();

    config for the table attached.

    I have the following manually entered into the field


    <video controls="controls" width="100%" name="Video Name" src="http://www.aceoftoons.com/imageAssets/garyBarlow1.mov"></video>

    When viewed in the editor ( set to CKEditor) I see the video okay.

    When I update via the editor the following is stored in the table.


    <p> &lt;video controls="controls" name="Video Name" src="http://www.aceoftoons.com/imageAssets/garyBarlow1.mov" width="100%"&gt;&nbsp;&lt;/video&gt; </p>

    As a work around I create what I need using editor & CKEditor then select the source code and manually paste into the table.

    What am I not understanding or missing here ?

    Cheers

    Steve Warby

  • allanallan Posts: 48,657Questions: 1Answers: 7,068 Site admin

    Right - that's a different issue. Its encoded HTML Entities for XSS protection. See this section of the Editor manual for details and how to turn it off.

    Allan

Sign In or Register to comment.