UTF8 problem

classic12

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

rf1234

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;

classic12

Thanks for the help.

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

Cheers

Steve Warby

rf1234

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

allan

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

classic12

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

allan

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