2

Cannot insert french words into database.

<?php  


Class Row{

    private $db = NULL;

    public function __construct(){

        $this->db = $this->connect();

    }

    private function connect(){
        include_once dirname(__FILE__) . "/../config/globalconfig.php";
        $db = new PDO("mysql:host=" . DB_SERVER . ";dbname=" . DB_NAME . ";", DB_USER, DB_PASSWORD);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $db;
    }

    public function insert(){

        $sql  = "INSERT INTO reservation (userId,workingDay,vehicleId,customerId,beginTime,endTime,beginTimeActual,endTimeActual,finalCustomer,phone, mobile,floor,address,zipCodeId,info,paymentModeId,price,kilometerNbr,price_total_ttc,price_received,status,infoSystem, isNote, no_email, no_gsm, owner_id_company_profile, multiday_reservation, parent_res_id, res_endDate,originalCreater) 
        VALUES ('54','2020-02-10','10','60546','00:00','00:00','00:00','00:00','test data','78956855','','1','Rue des Ėtudiants 28, Saint-Gilles, Belgium','8257','','2','0.00','0','0','0','planned','', '0', '0', '0','1', '0', '0','2020-01-10' ,'54' )";

        $stmt = $this->db->prepare($sql);
        return $stmt->Execute();

    }

    private function returnId(){

    }


}



$Row = new Row();

var_dump($Row->insert());

?

ERROR : Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column ...

After remove ' Ė ' character from insert query , it's worked fine.

I alread tried utf8 encode method , htmlentities methods

ADyson
  • 57,178
  • 14
  • 51
  • 63
C.V
  • 909
  • 2
  • 9
  • 20
  • 1
    I would suggest using prepared statements to prevent quoting issues. Since you're using PDO, take advantage of [prepared statements](https://secure.php.net/manual/en/pdo.prepared-statements.php) and [bindParam](http://php.net/manual/en/pdostatement.bindparam.php) or [bindValue](http://php.net/manual/en/pdostatement.bindvalue.php). – aynber Feb 10 '20 at 13:43
  • 5
    That alone will probably not solve any underlying encoding issues though, so you should probably have a good read of https://stackoverflow.com/questions/279170/utf-8-all-the-way-through first of all as well. – misorude Feb 10 '20 at 13:52
  • i used bind method, it's still same – C.V Feb 10 '20 at 14:14
  • Have you tried $mysqli->real_escape_string ? – Simone Rossaini Feb 10 '20 at 14:16
  • @SimoneRossaini using prepared statements and parameters would be far preferable to doing that, for multiple reasons. Although in this example all the data is hard-coded so neither real_escape_string or parameterisation would solve anything. – ADyson Feb 10 '20 at 14:37
  • Please paste your SQL structure and show us you country/encoding/language settings. Are you shure your datetime-format ist YYYY-MM-DD? – h.m.i.13 Feb 10 '20 at 14:59
  • 2
    Are you ***very** sure* that the list of inserted values actually corresponds to the list of fields? It sure sounds suspicious to me that SQL says "invalid *DATETIME* format!" – Mike Robinson Feb 10 '20 at 16:25
  • Huh? What is `Ė` doing in a date?? See "best practice" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Feb 11 '20 at 06:06
  • @RickJames I'm sure the problem is not the `Ė`. @CharmathViranga please try to replace the `Ė` by a `E`. I expect that the error is the same. – h.m.i.13 Feb 11 '20 at 08:21
  • @h.m.i.13 when i replace Ė with E it's perfectly fine – C.V Feb 12 '20 at 05:49
  • ok, so I was wrong. It's very strange. There are two dates in the statement `workingDay` and `res_endDate`. You posted only the beginning of the error message. Witch column is stated in the error message? – h.m.i.13 Feb 12 '20 at 11:38
  • @h.m.i.13 Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xC4\x96tudi...' for column `localift_dump`.`reservation`.`address` at row 1 in /Applications/dev/project/test.php:153 Stack trace: #0 /Applications/dev/project/test.php(153): PDOStatement->execute() #1 /Applications/dev/project/test.php(168): Row->insert() #2 {main} thrown in /Applications/dev/project/test.php on line 153 – C.V Feb 12 '20 at 11:50

1 Answers1

1

Address column has cp1252 west european encode. It changed to utf8mb4. Now its working fine.

For extra information refer this external link

https://www.eversql.com/mysql-utf8-vs-utf8mb4-whats-the-difference-between-utf8-and-utf8mb4/

C.V
  • 909
  • 2
  • 9
  • 20