DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

PHP error

PHP error

menashemenashe Posts: 183Questions: 43Answers: 2
in DataTables 2

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.

Answers

  • allanallan Posts: 63,368Questions: 1Answers: 10,449 Site admin

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

    Can you show me:

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

    Thanks,
    Allan

  • menashemenashe Posts: 183Questions: 43Answers: 2

    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);
  • allanallan Posts: 63,368Questions: 1Answers: 10,449 Site admin

    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);
    }
}
  • menashemenashe Posts: 183Questions: 43Answers: 2

    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
: 
[]
  • allanallan Posts: 63,368Questions: 1Answers: 10,449 Site admin
    edited 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

  • menashemenashe Posts: 183Questions: 43Answers: 2

    Same error. :'(

  • menashemenashe Posts: 183Questions: 43Answers: 2

    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?

  • allanallan Posts: 63,368Questions: 1Answers: 10,449 Site admin

    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);
    }
}
Sign In or Register to comment.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.