PHP download

PHP code example of aaron-lin / json-schema-sql-builder

1. Go to this page and download the library: Download aaron-lin/json-schema-sql-builder library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

aaron-lin / json-schema-sql-builder example snippets


use Lin\JsonSchemaSqlBuilder\Storage;
use Lin\JsonSchemaSqlBuilder\SelectSQLBuilder;

$SchemaURI = 'path/to/schema.json#';
$DSN = 'mysql:host=db;dbname=test;charset=utf8mb4';
$DB = new \PDO($DSN, 'test', 'test');

try {
  Storage::SetSchemaFromURI($SchemaURI);
} catch (\Exception $e) {
  echo $e->getMessage();
  exit;
}
Storage::AddSelectExpression($SchemaURI . '#/properties/_type_name', '(SELECT name FROM product_types WHERE product_types.id = products.type_id LIMIT 1)');
Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight', 'products.weight');
Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight_unit', 'products.weight_unit');
$Builder = new SelectSQLBuilder($SchemaURI, $DB);
$Builder->SetSelectExpressions()
  ->AddWhere("products.keywords like :keywords", ['keywords' => '%apple%'])
  ->AddOrderBy('products.price', 'DESC')
  ->SetLimit(10)
  ->SetOffset(0);
$Result = $Builder->Execute();
echo json_encode($Result, JSON_PRETTY_PRINT);
// [
//   {
//     "id": "1",
//     "name": "Apple",
//     "_type_name": "Fruit",
//     "_weight": {
//       "weight": "100.00",
//       "weight_unit": "g"
//     },
//     "_bids": [
//       {
//         "id": "7",
//         "price": "400",
//         "time": "2018-01-04 00:00:00"
//       },
//       {
//         "id": "5",
//         "price": "300",
//         "time": "2018-01-03 00:00:00"
//       },
//       {
//         "id": "3",
//         "price": "200",
//         "time": "2018-01-02 00:00:00"
//       },
//       {
//         "id": "1",
//         "price": "100",
//         "time": "2018-01-01 00:00:00"
//       }
//     ]
//   }
// ]


use Lin\JsonSchemaSqlBuilder\Storage;
use Lin\JsonSchemaSqlBuilder\UpsertSQLBuilder;

$SchemaURI = __DIR__ . '/schema.json#';
$DSN = 'mysql:host=db;dbname=test;charset=utf8mb4';
$DB = new \PDO($DSN, 'test', 'test');

try {
  Storage::SetSchemaFromURI($SchemaURI);
} catch (\Exception $e) {
    echo $e->getMessage();
  exit;
}

Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight', 'products.weight');
Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight_unit', 'products.weight_unit');
$Builder = new UpsertSQLBuilder($SchemaURI, $DB, $Data);
$Builder->SetAssignmentList();
$ResultCount = $Builder->Execute();
echo $ResultCount;
// for every new rows added, the return value is 1,
// for every existing rows updated, the return value is 2.
// data contains 1 row in products, 4 rows in bids,
// therefore, if data are all new rows, the return value is 5,
// or data are all existing rows, the return value is 10

json
{
  "id": "1",
  "name": "Apple",
  "_type_name": "Fruit",
  "_weight": {
    "weight": "100.00",
    "weight_unit": "g"
  },
  "_bids": [
    {
      "id": "7",
      "price": "400",
      "time": "2018-01-04 00:00:00"
    },
    {
      "id": "5",
      "price": "300",
      "time": "2018-01-03 00:00:00"
    },
    {
      "id": "3",
      "price": "200",
      "time": "2018-01-02 00:00:00"
    },
    {
      "id": "1",
      "price": "100",
      "time": "2018-01-01 00:00:00"
    }
  ]
}