cannot insert Json into Mysql

Question

I have a small api that I fetch remotely to get a Json (from a MSSQL DB) to insert into a Mysql DB:

{
    "data": [{
        "BrandID": 1,
        "AccountTranID": "138483",
        "Datetime": {
            "date": "2019-07-31 21:26:15.513000",
            "timezone_type": 3,
            "timezone": "UTC"
        },
        "PartyID": 1819748,
        "UserID": "adel2X",
        "Currency": "USD",
        "ProductID": 3,
        "ProductCode": "EBZZ",
        "ProductTranID": "EBZZ-f90f07cdfeff820904c56696dd72e321",
        "GameInfoID": 698,
        "GameID": "80233",
        "GameTranID": "80238cc6c07f85144d8ade97760f2cbdbff",
        "TranType": "GAME_BET",
        "AmountReal": "-.010000000000000000",
        "AmountPlayableBonus": ".000000000000000000",
        "AmountReleasedBonus": ".000000000000000000",
        "BalanceReal": ".000000000000000000",
        "BalancePlayableBonus": ".000000000000000000",
        "BalanceReleasedBonus": ".000000000000000000",
        "RollbackTranID": null,
        "RollbackTranType": null
    }, {
        "BrandID": 1,
        "AccountTranID": "138484",
        "Datetime": {
            "date": "2019-07-31 21:26:16.037000",
            "timezone_type": 3,
            "timezone": "UTC"
        },
        "PartyID": 1819748,
        "UserID": "adel2X",
        "Currency": "USD",
        "ProductID": 3,
        "ProductCode": "GROOVY",
        "ProductTranID": "Bo-f90f07cdfeff820904c56696dd72e321re",
        "GameInfoID": 698,
        "GameID": "80233",
        "GameTranID": "80238cc6c07f85144d8ade97760f2cbdbff",
        "TranType": "GAME_WIN",
        "AmountReal": ".000000000000000000",
        "AmountPlayableBonus": ".000000000000000000",
        "AmountReleasedBonus": ".000000000000000000",
        "BalanceReal": ".000000000000000000",
        "BalancePlayableBonus": ".000000000000000000",
        "BalanceReleasedBonus": ".000000000000000000",
        "RollbackTranID": null,
        "RollbackTranType": null
    }]
}

then I try to insert it into the table DataFeed of my local Mysql DB using the following code:

<?php
$conn = new PDO("mysql:host=mysite.com;dbname=mydb", 'root', 'xxxx');
$data = file_get_contents('https://myapi.com/test/test3.php?table=DataFeed&key=mykey&trans=xxx');
$products = json_decode($data);
foreach ($products as $product) {
    $stmt = $conn->prepare('insert ignore into DataFeed (BrandID, AccountTranID, Datetime, PartyID, UserID, Currency, ProductID, ProductCode, ProductTranID, GameInfoID, GameID, GameTranID, TranType, AmountReal, AmountPlayableBonus, AmountReleasedBonus, BalanceReal, BalancePlayableBonus, BalanceReleasedBonus, RollbackTranID, RollbackTranType) values(:BrandID, :AccountTranID, :Datetime, :PartyID, :UserID, :Currency, :ProductID, :ProductCode, :ProductTranID, :GameInfoID, :GameID, :GameTranID, :TranType, :AmountReal, :AmountPlayableBonus, :AmountReleasedBonus, :BalanceReal, :BalancePlayableBonus, :BalanceReleasedBonus, :RollbackTranID, :RollbackTranType)');
    $stmt->bindValue('BrandID', $product->BrandID);
    $stmt->bindValue('AccountTranID', $product-> AccountTranID);
    $stmt->bindValue('Datetime', $product-> Datetime);
    $stmt->bindValue('PartyID', $product-> PartyID);
    $stmt->bindValue('UserID', $product-> UserID);
    $stmt->bindValue('Currency', $product-> Currency);
    $stmt->bindValue('ProductID', $product-> ProductID);
    $stmt->bindValue('ProductCode', $product-> ProductCode);
    $stmt->bindValue('ProductTranID', $product-> ProductTranID);
    $stmt->bindValue('GameInfoID', $product-> GameInfoID);
    $stmt->bindValue('GameID', $product-> GameID);
    $stmt->bindValue('GameTranID', $product-> GameTranID);
    $stmt->bindValue('TranType', $product-> TranType);
    $stmt->bindValue('AmountReal', $product-> AmountReal);
    $stmt->bindValue('AmountPlayableBonus', $product-> AmountPlayableBonus);
    $stmt->bindValue('AmountReleasedBonus', $product-> AmountReleasedBonus);
    $stmt->bindValue('BalanceReal', $product-> BalanceReal);
    $stmt->bindValue('BalancePlayableBonus', $product-> BalancePlayableBonus);
    $stmt->bindValue('BalanceReleasedBonus', $product-> BalanceReleasedBonus);
    $stmt->bindValue('RollbackTranID', $product-> RollbackTranID);
    $stmt->bindValue('RollbackTranType', $product-> RollbackTranType);
    $stmt->execute();

}

?>

MySql Db has the following structure:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for DataFeed
-- ----------------------------
DROP TABLE IF EXISTS `DataFeed`;
CREATE TABLE `DataFeed` (
  `BrandID` int(11) NOT NULL,
  `AccountTranID` bigint(20) NOT NULL,
  `Datetime` datetime(3) NOT NULL,
  `PartyID` int(11) NOT NULL,
  `UserID` varchar(100) CHARACTER SET utf8 NOT NULL,
  `Currency` char(3) CHARACTER SET utf8 NOT NULL,
  `ProductID` int(11) DEFAULT NULL,
  `ProductCode` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `ProductTranID` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `GameInfoID` int(11) DEFAULT NULL,
  `GameID` varchar(100) COLLATE utf8_swedish_ci DEFAULT NULL,
  `GameTranID` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `TranType` varchar(10) COLLATE utf8_swedish_ci NOT NULL,
  `AmountReal` decimal(38,18) NOT NULL,
  `AmountPlayableBonus` decimal(38,18) NOT NULL,
  `AmountReleasedBonus` decimal(38,18) NOT NULL,
  `BalanceReal` decimal(38,18) NOT NULL,
  `BalancePlayableBonus` decimal(38,18) NOT NULL,
  `BalanceReleasedBonus` decimal(38,18) NOT NULL,
  `RollbackTranID` bigint(20) DEFAULT NULL,
  `RollbackTranType` varchar(10) COLLATE utf8_swedish_ci DEFAULT NULL,
  UNIQUE KEY `AccountTranID` (`AccountTranID`,`PartyID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

SET FOREIGN_KEY_CHECKS = 1;

When running my php code, it does not generate an error but does not insert the rows as expected... (One row, 0 only)

It does not seem to be a PDO issue since there is at least connection with the Mysql DB...

what am I doing wrong?

Possible duplicate of [My PDO Statement doesn't work](https://stackoverflow.com/questions/32648371/my-pdo-statement-doesnt-work) — Dharman, Jul 31 '19 at 21:17
You can pass the values in `execute` and you would not have to bind all of them one by one. — Dharman, Jul 31 '19 at 21:18
If your database connection is not set to exception mode, then if the query fails `$stmt->execute()` will return `false`. If it does, `$stmt->errorInfo()` will return an array with some error details that could be useful. — rickdenhaan, Jul 31 '19 at 21:33
My wild guess: you have a column named `Datetime`. That is a reserved keyword in MySQL and can only be used as a column identifier if you escape it (using backticks). — rickdenhaan, Jul 31 '19 at 21:34
@rickdenhaan there is not pdo error returned : PDOStatement::errorInfo(): Array ( [0] => 00000 [1] => [2] => ) — R_life_R, Jul 31 '19 at 21:39
I tried, @rickdenhaan but adding backticks to query does not help ... — R_life_R, Jul 31 '19 at 21:43
What happens if you `insert` regularly instead of `insert ignore`? — rickdenhaan, Jul 31 '19 at 21:44
in this case I got a PDO error PDOStatement::errorInfo(): Array ( [0] => 23000 [1] => 1048 [2] => Column 'BrandID' cannot be null ) — R_life_R, Jul 31 '19 at 21:50
Awesome, so apparently your data does not match your table structure (since you defined the `BrandID` column as `NOT NULL`). Now that you can see the errors, you can fix them :) — rickdenhaan, Jul 31 '19 at 21:52
It looks like all the values read from json are null, and therefore not inserted. Am I reading the json incorreclty? as you can see BrandID is 1 in Json, not null... — R_life_R, Jul 31 '19 at 21:53
Depends on whether `$data` actually contains valid JSON. You *might* need to use `json_decode($data, true)` to treat it as an array instead of an object, but then you'll also need to change all properties, e.g. `$product->BrandID` to `$product['BrandID']`. — rickdenhaan, Jul 31 '19 at 21:56
Sorry, completely missed that huge JSON block at the beginning of your question :$ It looks like the products array is in a `data` key, so I think you need to `foreach ($products->data as $product`) — rickdenhaan, Jul 31 '19 at 22:06
$products = json_decode($data); foreach ($products->data as $product) {... generates an error — R_life_R, Jul 31 '19 at 22:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/197309/discussion-between-renaud-dugert-and-rickdenhaan). — R_life_R, Jul 31 '19 at 22:25

cannot insert Json into Mysql

0 Answers0