PHP error

menashe · November 1

I keep getting the error message shown below:

I am assuming that it is because the value for parameter 5 (item_id) is not surrounded by quotes, but I cannot figure out how to fix.

Thanks.

allan · November 1

I'm not sure to be honest! There is nothing that immediately stands out as wrong there.

Can you show me:

An SQL dump of the structure of that table?
The PHP you are using to drive that Editor instance?

Thanks,
Allan

menashe · November 1

Oh boy! The PHP Editor file is easy--but big.

My web site used to display Table A, which then had one or more children from Table B, which in turn had one or more from Table C... This went five or so levels!

I decided that was a ridiculous way to enter data (on five different Editor forms), so I JOINed everything together in the included PHP.

I am not sure what I would give you as a SQL dump.

By the way, the issue occurs for ADDing; EDITing and existing record works.

<?php

// DataTables PHP library
include("../../Editor-PHP-2.3.2/lib/DataTables.php");

use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate,
  DataTables\Editor\ValidateOptions;

$items = Editor::inst($db, 'items')
  ->fields(
    Field::inst('items.id')->set(false),
    Field::inst('items.item')
      ->validator(Validate::notEmpty(ValidateOptions::inst()
        ->message('An Item name is required'))),
    Field::inst('items.prefix')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('items.suffix')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('items.category_id')
      ->options(
        Options::inst()
          ->table('categories')
          ->value('id')
          ->label('category')
      )
      ->validator(Validate::dbValues(null, 'id', 'categories', $db))
      ->validator(Validate::notEmpty(ValidateOptions::inst()
        ->message('A Category is required'))),
    Field::inst('categories.category'),
    Field::inst('categories.sub_category1'),
    Field::inst('categories.sub_category2'),
    Field::inst('items.notes')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('ih2.parent')
      ->getFormatter(function ($val, $data) {
        return $val ? 1 : null;
      }),
    Field::inst('items.do_not_repurchase')
      ->getFormatter(function ($val, $data) {
        return !$val ? 0 : 1;
      })
      ->setFormatter(function ($val, $data, $opts) {
        return !$val ? null : 1;
      }),
    // Field::inst('search.search_fields'),
    Field::inst('hierarchy.count')
      ->getFormatter(function ($val, $data) {
        return !$val ? 0 : $val;
      }),
    Field::inst('pdim.outer_packages')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('pdim.inner_packages')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('pdim.inner_items')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('pdim.unit_quantity')
      ->validator(function ($val, $data, $field, $host) {
        return $val > 0 ?
          true :
          'Quantity must be greater than zero!';
      }),
    Field::inst('pdet.id')->set(false),
    Field::inst('pdet.manufacturer_id')
      ->options(
        Options::inst()
          ->table('manufacturers')
          ->value('id')
          ->label('manufacturer')
      )
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('manufacturers.manufacturer'),
    Field::inst('pdet.brand_id')
      ->options(
        Options::inst()
          ->table('brands')
          ->value('id')
          ->label('brand')
      )
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('brands.brand'),
    Field::inst('pdim.unit_id')
      ->options(
        Options::inst()
          ->table('units')
          ->value('id')
          ->label('unit')
      ),
    Field::inst('units.unit'),
    Field::inst('units.description'),
    Field::inst('pdet.upc_ean')
      ->setFormatter(Format::ifEmpty(null))
      ->validator(function ($val, $data, $field, $host) {
        $upc_len = strlen($val ?? '');
        if ($upc_len > 0) {
          if ($upc_len  != 8 && $upc_len  != 10 && $upc_len  != 12 && $upc_len  != 13) {
            return 'UPC/EAN must be exactly 8, 12 or 13 characters!';
          }

          // Validate UPC.
          switch ($upc_len) {
            case 12: // https://boxshot.com/barcode/tutorials/upc-a-calculator/
              $sum_odd = 0;
              for ($i = 0; $i < strlen($val); $i = $i + 2) {
                $sum_odd = $sum_odd + $val[$i];
              }
              $sum_even = 0;
              for ($i = 1; $i < strlen($val) - 1; $i = $i + 2) {
                $sum_even = $sum_even + $val[$i];
              }
              $upc_checksum = 10 - (($sum_odd * 3 + $sum_even) % 10);
              if ($upc_checksum % 10 != $val[11]) {
                return 'UPC failed validation';
              }

              break;

            case 13: // https://boxshot.com/barcode/tutorials/ean-13-calculator/
              $sum_odd = 0;
              for ($i = 0; $i < strlen($val) - 1; $i = $i + 2) {
                $sum_odd = $sum_odd + $val[$i];
              }
              $sum_even = 0;
              for ($i = 1; $i < strlen($val); $i = $i + 2) {
                $sum_even = $sum_even + $val[$i];
              }
              $upc_checksum = 10 - (($sum_even * 3 + $sum_odd) % 10);
              if ($upc_checksum % 10 != $val[12]) {
                return 'UPC failed validation';
              }

              break;

            default: // I do not know if other UPCs have validation
              break;
          }
        }

        return true;
      }),
    Field::inst('pdet.model_number'),
    Field::inst('pdet.plu_code'),
    Field::inst('pdet.itm_art'),
    Field::inst('pdet.web'),
    Field::inst('pdet.sku'),
    Field::inst('pdet.detail')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('pdet.images')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('images.image'),
    Field::inst('lowest.lowest_price'),
    Field::inst('highest.highest_price'),
    Field::inst('price_range.lowest_price'),
    Field::inst('price_range.highest_price'),
  )
  ->leftJoin('items_hierarchy ih1', 'ih1.child', '=', 'items.id')
  ->leftJoin('packaging_dimensions pdim', 'pdim.item_id', '=', 'items.id')
  ->leftJoin('packaging_details pdet', 'pdet.packaging_id', '=', 'pdim.id')
  ->leftJoin('(SELECT DISTINCT parent FROM items_hierarchy) ih2', 'ih2.parent', '=', 'items.id')
  ->leftJoin('brands', 'brands.id', '=', 'pdet.brand_id')
  ->leftJoin('categories', 'categories.id', '=', 'items.category_id')
  ->leftJoin('manufacturers', 'manufacturers.id', '=', 'pdet.manufacturer_id')
  ->leftJoin('images', 'images.packaging_id = pdet.id AND images.id IN (SELECT id FROM images WHERE packaging_id =  pdet.id ORDER BY id ASC LIMIT 1)')
  ->leftJoin('units', 'units.id', '=', 'pdim.unit_id')
  ->leftJoin('(SELECT packaging_id, min(price) AS lowest_price FROM prices WHERE prices.date > now() - INTERVAL \'15 years\' GROUP BY packaging_id) lowest', 'lowest.packaging_id', '=', 'pdim.id')
  ->leftJoin('(SELECT packaging_id, max(price) AS highest_price FROM prices WHERE prices.date > now() - INTERVAL \'15 years\' GROUP BY packaging_id) highest', 'highest.packaging_id', '=', 'pdim.id')
  ->leftJoin('(SELECT pdim_id, pdet_pkg_id, packaging_id, lowest_price, highest_price FROM price_range) price_range', 'price_range.pdet_pkg_id', '=', 'pdim.id')
  ->leftJoin('(SELECT ih.parent, count(ih.child) count FROM items_hierarchy ih WHERE ih.child IS NOT NULL GROUP BY ih.parent) hierarchy', 'hierarchy.parent', '=', 'items.id')
  ->leftJoin('(SELECT i.id, STRING_AGG (coalesce(pdim.unit_quantity, 0) || \' \' || coalesce(m.manufacturer, \' \') || \' \' || coalesce(b.brand, \' \') || \' \' || coalesce(pdet.detail, \' \') || \' \' || coalesce(pdet.upc_ean, \' \'), \' \') AS search_fields FROM items i 
              LEFT JOIN packaging_dimensions pdim on pdim.item_id = i.id 
              LEFT JOIN packaging_details pdet ON pdet.packaging_id = pdim.id 
              LEFT JOIN manufacturers m ON m.id = pdet.manufacturer_id LEFT JOIN brands b ON b.id = pdet.brand_id
              GROUP BY i.id) search', 'search.id', '=', 'items.id')

  // Exclude records that are children of other records.
  ->where('ih1.child', null, '=')
  ->debug(true)
  ->process($_POST)
  ->data();

header('Content-Type: application/json; charset=utf-8');
echo json_encode($items);

allan · November 1

Could you replace the contents of the DataTable/Driver/PostgresQuery.php file that you have with the below please?

It won't fix the issue - rather it adds some more debug - I'm wondering if it is the primary key query that is the issue.

With that change in place, could you execute an edit command and then copy / paste the full JSON from the response here please?

<?php

/**
 * DataTables PHP libraries.
 *
 * PHP libraries for DataTables and DataTables Editor.
 *
 * @author    SpryMedia
 * @copyright 2012 SpryMedia ( http://sprymedia.co.uk )
 * @license   http://editor.datatables.net/license DataTables Editor
 *
 * @see       http://editor.datatables.net
 */

namespace DataTables\Database\Driver;

use DataTables\Database\Query;

/**
 * Postgres driver for DataTables Database Query class.
 *
 * @internal
 */
class PostgresQuery extends Query
{
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Private properties
     */
    private $_stmt;

    protected $_identifier_limiter = ['"', '"'];

    protected $_field_quote = '"';

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Public methods
     */

    public static function connect($user, $pass = '', $host = '', $port = '', $db = '', $dsn = '')
    {
        if (is_array($user)) {
            $opts = $user;
            $user = $opts['user'];
            $pass = $opts['pass'];
            $port = $opts['port'];
            $host = $opts['host'];
            $db = $opts['db'];
            $dsn = isset($opts['dsn']) ? $opts['dsn'] : '';
            $pdoAttr = isset($opts['pdoAttr']) ? $opts['pdoAttr'] : [];
        }

        if ($port !== '') {
            $port = "port={$port};";
        }

        try {
            $pdoAttr[\PDO::ATTR_ERRMODE] = \PDO::ERRMODE_EXCEPTION;

            $pdo = @new \PDO(
                "pgsql:host={$host};{$port}dbname={$db}" . self::dsnPostfix($dsn),
                $user,
                $pass,
                $pdoAttr
            );
        } catch (\PDOException $e) {
            // If we can't establish a DB connection then we return a DataTables
            // error.
            echo json_encode([
                'error' => 'An error occurred while connecting to the database ' .
                    "'{$db}'. The error reported by the server was: " . $e->getMessage(),
            ]);

            exit(1);
        }

        return $pdo;
    }

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Protected methods
     */

    protected function _prepare($sql)
    {
        $this->database()->debugInfo($sql, $this->_bindings);

        $resource = $this->database()->resource();
        $pkey = $this->pkey();

        // Add a RETURNING command to postgres insert queries so we can get the
        // pkey value from the query reliably
        if ($this->_type === 'insert') {
            $table = explode(' as ', $this->_table[0]);

            // Get the pkey field name
            $pkSql = 'SELECT a.attname
                FROM   pg_index i
                JOIN   pg_attribute a ON a.attrelid = i.indrelid
                                    AND a.attnum = ANY(i.indkey)
                WHERE  i.indrelid = (:tableName)::regclass
                AND    i.indisprimary';

            $this->database()->debugInfo($pkSql, ['tableName' => $table[0]]);

            $pkRes = $resource->prepare($pkSql);
            $pkRes->bindValue('tableName', $table[0]);
            $pkRes->execute();
            $row = $pkRes->fetch();

            if ($row && isset($row['attname'])) {
                $sql .= ' RETURNING ' . $row['attname'] . ' as dt_pkey';
            }
        }

        $this->_stmt = $resource->prepare($sql);

        // bind values
        for ($i = 0; $i < count($this->_bindings); ++$i) {
            $binding = $this->_bindings[$i];

            $this->_stmt->bindValue(
                $binding['name'],
                $binding['value'],
                $binding['type'] ?: \PDO::PARAM_STR
            );
        }
    }

    protected function _exec()
    {
        try {
            $this->_stmt->execute();
        } catch (\PDOException $e) {
            throw new \Exception('An SQL error occurred: ' . $e->getMessage(), 0, $e);
        }

        $resource = $this->database()->resource();

        return new PostgresResult($resource, $this->_stmt);
    }
}

menashe · November 1

Here you go. I assume that you meant ADD, not EDIT; the Edit didn't produce this JSON.

(I could not figure out how to retain the JSON formatting.)

{fieldErrors: [],…}
cancelled
: 
[]
data
: 
[]
debug
: 
["Editor PHP libraries - version 2.3.2",…]
0
: 
"Editor PHP libraries - version 2.3.2"
1
: 
{query: "SELECT "id" as "id" FROM "categories" WHERE "id" = :where_0 ",…}
bindings
: 
[{name: ":where_0", value: "214", type: null}]
0
: 
{name: ":where_0", value: "214", type: null}
name
: 
":where_0"
type
: 
null
value
: 
"214"
query
: 
"SELECT  \"id\" as \"id\" FROM  \"categories\" WHERE \"id\" = :where_0 "
2
: 
{,…}
bindings
: 
[{name: ":item", value: "test", type: null}, {name: ":prefix", value: null, type: null},…]
0
: 
{name: ":item", value: "test", type: null}
name
: 
":item"
type
: 
null
value
: 
"test"
1
: 
{name: ":prefix", value: null, type: null}
name
: 
":prefix"
type
: 
null
value
: 
null
2
: 
{name: ":suffix", value: null, type: null}
name
: 
":suffix"
type
: 
null
value
: 
null
3
: 
{name: ":category_id", value: "214", type: null}
name
: 
":category_id"
type
: 
null
value
: 
"214"
4
: 
{name: ":notes", value: null, type: null}
name
: 
":notes"
type
: 
null
value
: 
null
query
: 
"INSERT INTO  \"items\"  ( \"item\", \"prefix\", \"suffix\", \"category_id\", \"notes\" ) VALUES (  :item,  :prefix,  :suffix,  :category_id,  :notes )"
3
: 
{,…}
bindings
: 
{tableName: ""items""}
tableName
: 
"\"items\""
query
: 
"SELECT a.attname\n                FROM   pg_index i\n                JOIN   pg_attribute a ON a.attrelid = i.indrelid\n                                    AND a.attnum = ANY(i.indkey)\n                WHERE  i.indrelid = (:tableName)::regclass\n                AND    i.indisprimary"
4
: 
{query: "SELECT * FROM "packaging_dimensions" pdim WHERE "item_id" = :where_0 ",…}
bindings
: 
[{name: ":where_0", value: 1386, type: null}]
0
: 
{name: ":where_0", value: 1386, type: null}
name
: 
":where_0"
type
: 
null
value
: 
1386
query
: 
"SELECT  * FROM  \"packaging_dimensions\" pdim WHERE \"item_id\" = :where_0 "
5
: 
{,…}
bindings
: 
[{name: ":outer_packages", value: null, type: null},…]
0
: 
{name: ":outer_packages", value: null, type: null}
name
: 
":outer_packages"
type
: 
null
value
: 
null
1
: 
{name: ":inner_packages", value: null, type: null}
name
: 
":inner_packages"
type
: 
null
value
: 
null
2
: 
{name: ":inner_items", value: null, type: null}
name
: 
":inner_items"
type
: 
null
value
: 
null
3
: 
{name: ":unit_quantity", value: "1", type: null}
name
: 
":unit_quantity"
type
: 
null
value
: 
"1"
4
: 
{name: ":unit_id", value: "26", type: null}
name
: 
":unit_id"
type
: 
null
value
: 
"26"
5
: 
{name: ":item_id", value: 1386, type: null}
name
: 
":item_id"
type
: 
null
value
: 
1386
query
: 
"INSERT INTO  \"packaging_dimensions\"  ( \"outer_packages\", \"inner_packages\", \"inner_items\", \"unit_quantity\", \"unit_id\", \"item_id\" ) VALUES (  :outer_packages,  :inner_packages,  :inner_items,  :unit_quantity,  :unit_id,  :item_id )"
6
: 
{,…}
bindings
: 
{tableName: ""packaging_dimensions" pdim"}
tableName
: 
"\"packaging_dimensions\" pdim"
query
: 
"SELECT a.attname\n                FROM   pg_index i\n                JOIN   pg_attribute a ON a.attrelid = i.indrelid\n                                    AND a.attnum = ANY(i.indkey)\n                WHERE  i.indrelid = (:tableName)::regclass\n                AND    i.indisprimary"
error
: 
"SQLSTATE[42602]: Invalid name: 7 ERROR:  invalid name syntax\nCONTEXT:  unnamed portal parameter $1 = '...'"
fieldErrors
: 
[]
ipOpts
: 
[]

allan · November 1

I think it is this:

->leftJoin('packaging_details pdet',

Could you replace with:

->leftJoin('packaging_details as pdet',

More correctly, I think the query to get the primary key name isn't correctly doing a split on the name if an alias is used, without the as. That is something I can look at fixing (assuming this addresses the issue!).

Allan

menashe · November 1

Same error.

menashe · November 1

I keep looking at the JSON. Every single value is surrounded by quotes, except for the "item_id".

Why is that? Isn't that the issue?

allan · November 2

I don't really see why an integer rather than a string would make a difference, but then I also don't see anything else that is causing an issue!

Could you try this varient please? I've converted the integer to be a string and corrected the debug order.

<?php
 
/**
 * DataTables PHP libraries.
 *
 * PHP libraries for DataTables and DataTables Editor.
 *
 * @author    SpryMedia
 * @copyright 2012 SpryMedia ( http://sprymedia.co.uk )
 * @license   http://editor.datatables.net/license DataTables Editor
 *
 * @see       http://editor.datatables.net
 */
 
namespace DataTables\Database\Driver;
 
use DataTables\Database\Query;
 
/**
 * Postgres driver for DataTables Database Query class.
 *
 * @internal
 */
class PostgresQuery extends Query
{
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Private properties
     */
    private $_stmt;
 
    protected $_identifier_limiter = ['"', '"'];
 
    protected $_field_quote = '"';
 
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Public methods
     */
 
    public static function connect($user, $pass = '', $host = '', $port = '', $db = '', $dsn = '')
    {
        if (is_array($user)) {
            $opts = $user;
            $user = $opts['user'];
            $pass = $opts['pass'];
            $port = $opts['port'];
            $host = $opts['host'];
            $db = $opts['db'];
            $dsn = isset($opts['dsn']) ? $opts['dsn'] : '';
            $pdoAttr = isset($opts['pdoAttr']) ? $opts['pdoAttr'] : [];
        }
 
        if ($port !== '') {
            $port = "port={$port};";
        }
 
        try {
            $pdoAttr[\PDO::ATTR_ERRMODE] = \PDO::ERRMODE_EXCEPTION;
 
            $pdo = @new \PDO(
                "pgsql:host={$host};{$port}dbname={$db}" . self::dsnPostfix($dsn),
                $user,
                $pass,
                $pdoAttr
            );
        } catch (\PDOException $e) {
            // If we can't establish a DB connection then we return a DataTables
            // error.
            echo json_encode([
                'error' => 'An error occurred while connecting to the database ' .
                    "'{$db}'. The error reported by the server was: " . $e->getMessage(),
            ]);
 
            exit(1);
        }
 
        return $pdo;
    }
 
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Protected methods
     */
 
    protected function _prepare($sql)
    {
        $resource = $this->database()->resource();
        $pkey = $this->pkey();
 
        // Add a RETURNING command to postgres insert queries so we can get the
        // pkey value from the query reliably
        if ($this->_type === 'insert') {
            $table = explode(' as ', $this->_table[0]);
 
            // Get the pkey field name
            $pkSql = 'SELECT a.attname
                FROM   pg_index i
                JOIN   pg_attribute a ON a.attrelid = i.indrelid
                                    AND a.attnum = ANY(i.indkey)
                WHERE  i.indrelid = (:tableName)::regclass
                AND    i.indisprimary';
 
            $this->database()->debugInfo($pkSql, ['tableName' => $table[0]]);
 
            $pkRes = $resource->prepare($pkSql);
            $pkRes->bindValue('tableName', $table[0]);
            $pkRes->execute();
            $row = $pkRes->fetch();
 
            if ($row && isset($row['attname'])) {
                $sql .= ' RETURNING ' . $row['attname'] . ' as dt_pkey';
            }
        }
        
        $this->database()->debugInfo($sql, $this->_bindings);
 
        $this->_stmt = $resource->prepare($sql);
 
        // bind values
        for ($i = 0; $i < count($this->_bindings); ++$i) {
            $binding = $this->_bindings[$i];

            if (is_int($binding['value'])) {
                $binding['value'] = strval($binding['value']);
            }
 
            $this->_stmt->bindValue(
                $binding['name'],
                $binding['value'],
                $binding['type'] ?: \PDO::PARAM_STR
            );
        }
    }
 
    protected function _exec()
    {
        try {
            $this->_stmt->execute();
        } catch (\PDOException $e) {
            throw new \Exception('An SQL error occurred: ' . $e->getMessage(), 0, $e);
        }
 
        $resource = $this->database()->resource();
 
        return new PostgresResult($resource, $this->_stmt);
    }
}

menashe · November 3

Hi Alan,

I have not (yet) tried the above--I trust your judgement that it seems unrelated.

Besides, I have trying out various scenarios and staring down the code. I did notice that even in EDIT mode (which I had said that it works), the PACKAGING_DETAILS table is not updated--it is not even included in the resultant JSON!

Does that tell you anything?

allan · November 4

I have not (yet) tried the above--I trust your judgement that it seems unrelated.

Well... I'd just be surprised, but I've been known to be wrong before (like on an hourly basis ).

Does that tell you anything?

The JSON structure for a row returned from the initial load action is different from after the create or edit commands? That is odd... And the load is definitely happening from the same script?

menashe · November 4

Hi Alan,

I did try it yesterday, and it did not help.

Exactly! The initial load contains all of the values from the PACKAGING_DETAILS table (I did not try putting a value in the database for each column, but the JSON record is there and has the existing values!

For the EDIT, there is simply no JSON structure for that table.

allan · November 4

Can you show me the initialisation Javascript for your DataTable and Editor instances please?

The PHP server-side code uses exactly the same methods for getting the row data after a create / edit as it does when loading there data. There is something odd going on if the data structure returned from each is different.

Are you able to PM me a link to the page? It might help if I can see it in action.

Thanks,
Allan

menashe · November 4

I currently run on localhost, albeit using Apache, so I'm not sure about a link.

So... let me first send the Javascript.

menashe · November 4

It won't let me send that much data!

allan · November 4

Can you use Pastebin or something similar?

Allan

menashe · November 4

I hope that this works!
https://pastebin.com/embed_js/k1F6TmBa

allan · November 4

They are both using server_side/scripts/items.php which is immediately what I was looking for. I don't really understand why the script would return a different structure for the rows to be honest.

If you are able to put it up on the web somewhere so I can debug it, that would be really useful.

Alternatively, a zip of the package and a dump of the database tables in question so I can easily run it locally, would be an option. allan at thisdomain.net will come to me.

Allan

menashe · November 5

Hi Alan,

Please don't ask what changed--I went through every line of code, PHP and Javascript, making changes here and there.

The Bad Parameter error is gone!

Still cannot save packaging_detail table--it's not in the JSON.

Is there any chance that this a bug, or is something that I have somehow (still) just missed?

allan · November 5

Its certainly possible. Can you show me the HTTP request parameters and the return JSON with the debug SQL statements in the current state?

Allan

menashe · November 5

HTTP Payload - Form Data tab: (You see the values 'test02' and 'test03')

2 requests
82.3 kB transferred
81.7 kB resources

data[0][items][item]: test01
data[0][items][notes]: 
data[0][items][prefix]: 
data[0][items][suffix]: 
data[0][items][category_id]: 214
data[0][packaging_details][manufacturer_id]: 
data[0][packaging_details][brand_id]: 
data[0][packaging_details][model_number]: 
data[0][packaging_details][plu_code]: 
data[0][packaging_details][sku]: 
data[0][packaging_details][upc_ean]: 
data[0][packaging_details][itm_art]: test02
data[0][packaging_details][web]: 
data[0][packaging_details][detail]: test03
data[0][packaging_dimensions][outer_packages]: 
data[0][packaging_dimensions][inner_packages]: 
data[0][packaging_dimensions][inner_items]: 
data[0][packaging_dimensions][unit_quantity]: 1
data[0][packaging_dimensions][unit_id]: 26
data[0][packaging_dimensions][is_net_weight]: 
data[0][packaging_dimensions][images]: 
data[0][packaging_dimensions][is_obsolete]: 
action: create

menashe · November 5

And this is the HTTP Response tab, which has all of the queries found under the Preview tab:

(Too big - last part follows in next comment.)

{
    "data": [
        {
            "DT_RowId": "row_1402",
            "items": {
                "id": 1402,
                "item": "test01",
                "prefix": null,
                "suffix": null,
                "category_id": 214,
                "notes": null,
                "do_not_repurchase": 0
            },
            "categories": {
                "category": "Automobile",
                "sub_category1": "Electrical",
                "sub_category2": "Fuses"
            },
            "ih2": {
                "parent": null
            },
            "hierarchy": {
                "count": 0
            },
            "packaging_dimensions": {
                "id": 1428,
                "item_id": 1402,
                "outer_packages": null,
                "inner_packages": null,
                "inner_items": null,
                "unit_quantity": "1.00",
                "unit_id": 26
            },
            "packaging_details": {
                "id": null,
                "packaging_dimension_id": null,
                "manufacturer_id": null,
                "brand_id": null,
                "upc_ean": null,
                "model_number": null,
                "plu_code": null,
                "itm_art": null,
                "web": null,
                "sku": null,
                "detail": null,
                "images": null
            },
            "manufacturers": {
                "manufacturer": null
            },
            "brands": {
                "brand": null
            },
            "units": {
                "unit": "L",
                "description": "liter"
            },
            "images": {
                "image": null
            },
            "lowest": {
                "lowest_price": "5.99"
            },
            "highest": {
                "highest_price": "5.99"
            },
            "price_range": {
                "lowest_price": null,
                "highest_price": null
            }
        }
    ],
    "debug": [
        "Editor PHP libraries - version 2.3.2",
        {
            "query": "SELECT  \"id\" as \"id\" FROM  \"categories\" WHERE \"id\" = :where_0 ",
            "bindings": [
                {
                    "name": ":where_0",
                    "value": "214",
                    "type": null
                }
            ]
        },
        {
            "query": "SELECT a.attname\n                FROM   pg_index i\n                JOIN   pg_attribute a ON a.attrelid = i.indrelid\n                                    AND a.attnum = ANY(i.indkey)\n                WHERE  i.indrelid = (:tableName)::regclass\n                AND    i.indisprimary",
            "bindings": {
                "tableName": "\"items\""
            }
        },
        {
            "query": "INSERT INTO  \"items\"  ( \"item\", \"prefix\", \"suffix\", \"category_id\", \"notes\" ) VALUES (  :item,  :prefix,  :suffix,  :category_id,  :notes ) RETURNING id as dt_pkey",
            "bindings": [
                {
                    "name": ":item",
                    "value": "test01",
                    "type": null
                },
                {
                    "name": ":prefix",
                    "value": null,
                    "type": null
                },
                {
                    "name": ":suffix",
                    "value": null,
                    "type": null
                },
                {
                    "name": ":category_id",
                    "value": "214",
                    "type": null
                },
                {
                    "name": ":notes",
                    "value": null,
                    "type": null
                }
            ]
        },
        {
            "query": "SELECT  * FROM  \"packaging_dimensions\" WHERE \"item_id\" = :where_0 ",
            "bindings": [
                {
                    "name": ":where_0",
                    "value": 1402,
                    "type": null
                }
            ]
        },
        {
            "query": "SELECT a.attname\n                FROM   pg_index i\n                JOIN   pg_attribute a ON a.attrelid = i.indrelid\n                                    AND a.attnum = ANY(i.indkey)\n                WHERE  i.indrelid = (:tableName)::regclass\n                AND    i.indisprimary",
            "bindings": {
                "tableName": "\"packaging_dimensions\""
            }
        },

menashe · November 5

        {
            "query": "INSERT INTO  \"packaging_dimensions\"  ( \"outer_packages\", \"inner_packages\", \"inner_items\", \"unit_quantity\", \"unit_id\", \"item_id\" ) VALUES (  :outer_packages,  :inner_packages,  :inner_items,  :unit_quantity,  :unit_id,  :item_id ) RETURNING id as dt_pkey",
            "bindings": [
                {
                    "name": ":outer_packages",
                    "value": null,
                    "type": null
                },
                {
                    "name": ":inner_packages",
                    "value": null,
                    "type": null
                },
                {
                    "name": ":inner_items",
                    "value": null,
                    "type": null
                },
                {
                    "name": ":unit_quantity",
                    "value": "1",
                    "type": null
                },
                {
                    "name": ":unit_id",
                    "value": "26",
                    "type": null
                },
                {
                    "name": ":item_id",
                    "value": 1402,
                    "type": null
                }
            ]
        },
        {
            "query": "SELECT  \"items\".\"id\" as \"items.id\", \"items\".\"item\" as \"items.item\", \"items\".\"prefix\" as \"items.prefix\", \"items\".\"suffix\" as \"items.suffix\", \"items\".\"category_id\" as \"items.category_id\", \"categories\".\"category\" as \"categories.category\", \"categories\".\"sub_category1\" as \"categories.sub_category1\", \"categories\".\"sub_category2\" as \"categories.sub_category2\", \"items\".\"notes\" as \"items.notes\", \"ih2\".\"parent\" as \"ih2.parent\", \"items\".\"do_not_repurchase\" as \"items.do_not_repurchase\", \"hierarchy\".\"count\" as \"hierarchy.count\", \"packaging_dimensions\".\"id\" as \"packaging_dimensions.id\", \"packaging_dimensions\".\"item_id\" as \"packaging_dimensions.item_id\", \"packaging_dimensions\".\"outer_packages\" as \"packaging_dimensions.outer_packages\", \"packaging_dimensions\".\"inner_packages\" as \"packaging_dimensions.inner_packages\", \"packaging_dimensions\".\"inner_items\" as \"packaging_dimensions.inner_items\", \"packaging_dimensions\".\"unit_quantity\" as \"packaging_dimensions.unit_quantity\", \"packaging_details\".\"id\" as \"packaging_details.id\", \"packaging_details\".\"packaging_dimension_id\" as \"packaging_details.packaging_dimension_id\", \"packaging_details\".\"manufacturer_id\" as \"packaging_details.manufacturer_id\", \"manufacturers\".\"manufacturer\" as \"manufacturers.manufacturer\", \"packaging_details\".\"brand_id\" as \"packaging_details.brand_id\", \"brands\".\"brand\" as \"brands.brand\", \"packaging_dimensions\".\"unit_id\" as \"packaging_dimensions.unit_id\", \"units\".\"unit\" as \"units.unit\", \"units\".\"description\" as \"units.description\", \"packaging_details\".\"upc_ean\" as \"packaging_details.upc_ean\", \"packaging_details\".\"model_number\" as \"packaging_details.model_number\", \"packaging_details\".\"plu_code\" as \"packaging_details.plu_code\", \"packaging_details\".\"itm_art\" as \"packaging_details.itm_art\", \"packaging_details\".\"web\" as \"packaging_details.web\", \"packaging_details\".\"sku\" as \"packaging_details.sku\", \"packaging_details\".\"detail\" as \"packaging_details.detail\", \"packaging_details\".\"images\" as \"packaging_details.images\", \"images\".\"image\" as \"images.image\", \"lowest\".\"lowest_price\" as \"lowest.lowest_price\", \"highest\".\"highest_price\" as \"highest.highest_price\", \"price_range\".\"lowest_price\" as \"price_range.lowest_price\", \"price_range\".\"highest_price\" as \"price_range.highest_price\" FROM  \"items\" LEFT JOIN \"items_hierarchy\" ON \"items_hierarchy\".\"child\" = \"items\".\"id\"  LEFT JOIN \"packaging_dimensions\" ON \"packaging_dimensions\".\"item_id\" = \"items\".\"id\"  LEFT JOIN \"packaging_details\" ON \"packaging_details\".\"packaging_dimension_id\" = \"packaging_dimensions\".\"id\"  LEFT JOIN (SELECT DISTINCT parent FROM items_hierarchy) AS ih2 ON \"ih2\".\"parent\" = \"items\".\"id\"  LEFT JOIN \"brands\" ON \"brands\".\"id\" = \"packaging_details\".\"brand_id\"  LEFT JOIN \"categories\" ON \"categories\".\"id\" = \"items\".\"category_id\"  LEFT JOIN \"manufacturers\" ON \"manufacturers\".\"id\" = \"packaging_details\".\"manufacturer_id\"  LEFT JOIN \"images\" ON images.packaging_detail_id = packaging_details.id AND images.id IN (SELECT id FROM images WHERE packaging_detail_id =  packaging_details.id ORDER BY id ASC LIMIT 1)  LEFT JOIN \"units\" ON \"units\".\"id\" = \"packaging_dimensions\".\"unit_id\"  LEFT JOIN (SELECT packaging_id, min(price) AS lowest_price FROM prices WHERE prices.date > now() - INTERVAL '15 years' GROUP BY packaging_id) AS lowest ON \"lowest\".\"packaging_id\" = \"packaging_dimensions\".\"id\"  LEFT JOIN (SELECT packaging_id, max(price) AS highest_price FROM prices WHERE prices.date > now() - INTERVAL '15 years' GROUP BY packaging_id) AS highest ON \"highest\".\"packaging_id\" = \"packaging_dimensions\".\"id\"  LEFT JOIN (SELECT pdim_id, pdet_pkg_id, packaging_id, lowest_price, highest_price FROM price_range) price_range ON \"price_range\".\"pdet_pkg_id\" = \"packaging_dimensions\".\"id\"  LEFT JOIN (SELECT items_hierarchy.parent, count(items_hierarchy.child) count FROM items_hierarchy WHERE items_hierarchy.child IS NOT NULL GROUP BY items_hierarchy.parent) AS hierarchy ON \"hierarchy\".\"parent\" = \"items\".\"id\" WHERE \"items_hierarchy\".\"child\" IS NULL AND  \"items\".\"id\" = :where_1 ",
            "bindings": [
                {
                    "name": ":where_1",
                    "value": "1402",
                    "type": null
                }
            ]
        }
    ]
}

allan · November 6

I believe it is because packaging_details is a join of packaging_dimensions. Whereas packaging_dimensions is a join of the host table (items) so it works, but because of the second level of nesting, unfortunately packaging_details does not work at this time.

This is the relevant code - it uses the host's primary key, and if that doesn't match the join it attempts to use one of the field values submitted. Neither case applies here, so unfortunately it can't match and doesn't run that nested insert.

I don't immediately have a solution for this I'm afraid - it might require a fair amount of reworking in that code to allow such a use case to work.

What you could consider doing is writing the packaging_details in manually using a postCreate event. You would need to look up the new packaging_dimensions primary key value from the new item id, but that would work.

Allan

menashe · November 6

Alan

Thank you for tracking it down. I'm tempted to look at the Editor.php code myself, but...

Let me ask: What would be involved if I wanted to write a "wrapper" (?) to use native SQL, so that I don't ever use Editor PHP?

I know that I've seen that the back-end database can be accessed in any fashion, but I do not (yet) have a handle on what that involves.

allan · November 6

If you want to write your own backend code, the Editor client / server data exchange protocol is documented here. It is quite possible, and depending on how dynamic you want to make it, it can be a viable option.

The goal with the Editor PHP libraries is not to cover every single use case - that simply wouldn't be feasible. It should however cover 80+% of them. So there will be some cases (possibly this one) where some code does need to be written.

Its up to you to decide if you want to go that route or explore using postCreate to add the row in.

Allan

menashe · November 6

Please clarify how I would manually enter the row in package_details in postCreate?

I am drawing a blank!

rf1234 · November 6

Its up to you to decide if you want to go that route or explore using postCreate to add the row in.

I would strongly recommend using "postCreate" or "writeCreate" in this situation. I mean dropping all of the benefits of Editor just because it doesn't work in one special use case seems to be "overdone" in my opinion.

For special cases I have been using "writeEdit", "writeCreate", "postEdit" and "postCreate" and many of the other events listed here https://editor.datatables.net/manual/php/events

I use them to make custom SQL updates, inserts and even deletes on many occasions. No issues with that. Makes Editor even more flexible to use.

menashe · November 6

I mean dropping all of the benefits of Editor just because it doesn't work in one special use case seems to be "overdone" in my opinion.

Agreed!

My question was--please refresh my memory or show a brief example of making a custom SQL update from postCreate?

(I might just be giddy from the election results! )

rf1234 · November 6

I am drawing a blank!

Here is a helper function from my own coding that uses Editor's db handler to process linked contracts on "writeCreate" and "writeEdit" (so BEFORE the data have been read back from the server which is what I want).

Of course this isn't exactly your use case but shows you how flexible this can be done. Here I need a table linking a contract to other contracts and vice versa. Those links need to be "double" to work when the links are displayed in an Mjoin. That of course is something Editor is not expected to do - and will never be able to do. At least it wouldn't make any sense ...

->on('writeCreate', function ( $editor, $id, $values ) use ( $db ) {
    processCtrLinkedContracts ( $values, $id, $db );
} )
->on( 'writeEdit', function ( $editor, $id, $values ) use ( $db ) {
    processCtrLinkedContracts ( $values, $id, $db );
} )

And the helper function with the additional processing:

function processCtrLinkedContracts ( $values, $id, $db ) {
    if ( ! isset($values['ctr_has_ctr-many-count']) ) {
        return;
    }
    $db->raw()
       ->bind( ':id', $id )
       ->exec( 'DELETE FROM ctr_has_ctr
                 WHERE ctr_id        = :id
                    OR linked_ctr_id = :id' );

    for ($i = 0; $i < $values['ctr_has_ctr-many-count']; $i++) {
        $linkedId = $values["ctr_has_ctr"][$i]["linked_ctr_id"];
        $res = $db->insert( 'ctr_has_ctr', array (
            'ctr_id'        => $id,
            'linked_ctr_id' => $linkedId
        ) );
        $res = $db->insert( 'ctr_has_ctr', array (
            'ctr_id'        => $linkedId,
            'linked_ctr_id' => $id
        ) );
    }
}

This uses Editor's "raw" and "insert" methods. But you can also use your own db-handler of course.

rf1234 · November 6

You will need the data from the (incomplete) INSERT or UPDATE, so "postCreate" is better for you. But that doesn't make a big difference. Just use the data from $row instead of $values.

PHP error

PHP error

Answers

Howdy, Stranger!

Categories

DataTables

PHP error

PHP error

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables