datatables警告：table id=seo\u editor\u product-json响应无效

byqmnocz  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(412)

我在mariadb 10.2.7中使用xampp，我安装了一个seo模块，然后使用json，它显示了以下错误。

Error: Uncaught Exception: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows' at line 1<br />Error No: 1064<br />SELECT FOUND_ROWS() as rows in C:\xampp\htdocs\store\system\library\db\mysqli.php:40
mysqlJSONmariadbxamppopencart

1条答案

按热度按时间
mwg9r5ms

mwg9r5ms1#

<?php

类modeltoolseopackageeditor扩展模型{
公共函数uu构造（$registry）{父函数：：u构造（$registry）；

if (version_compare(VERSION, '3', '>=')) {
  $this->url_alias = 'seo_url';
} else {
  $this->url_alias = 'url_alias';
}

}
/为datatables行创建数据输出数组@param array$columns column information array@param array$data data来自sql get*@return array formatted data in a row-based format*/public function data_output（$columns，$data，$type=''）{$out=array（）；

for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
        $row = array();

        for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
            $column = $columns[$j];

            // Is there a formatter?

    if($column['db'] == 'related') {
        $related = $this->db->query("SELECT pr.related_id, pd.name FROM " . DB_PREFIX . "product_related pr LEFT JOIN " . DB_PREFIX . "product_description pd ON pd.product_id = pr.related_id WHERE pr.product_id='" . $data[$i]['product_id'] . "' AND pd.language_id=" . $this->config->get('config_language_id') . " ORDER BY pd.name")->rows;

        $old_related = $old_related_id = array();
        foreach ($related as $rel) {
          $old_related[] = $rel['name'];
          $old_related_id[] = $rel['related_id'];
        }

        $data[$i]['related']['text'] = implode(', ', $old_related);
        $data[$i]['related']['rows'] = $related;
    }

            if ( isset( $column['formatter'] ) ) {
                $row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i], $type, $this );
            }
            else {
                $row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
            }
        }

        $out[] = $row;
    }

    return $out;
}

/**
 * Paging
 *
 * Construct the LIMIT clause for server-side processing SQL query
 *
 *  @param  array $request Data sent to server by DataTables
 *  @param  array $columns Column information array
 *  @return string SQL limit clause
 */
public function limit ( $request, $columns )
{
    $limit = '';

    if ( isset($request['start']) && $request['length'] != -1 ) {
        $limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
    }

    return $limit;
}

/**
 * Ordering
 *
 * Construct the ORDER BY clause for server-side processing SQL query
 *
 *  @param  array $request Data sent to server by DataTables
 *  @param  array $columns Column information array
 *  @return string SQL order by clause
 */
public function order ( $request, $columns, $default )
{
    $order = '';

    if ( isset($request['order']) && count($request['order']) ) {
        $orderBy = array();
        $dtColumns = self::pluck( $columns, 'dt' );

        for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
            // Convert the column index into the column data property
            $columnIdx = intval($request['order'][$i]['column']);
            $requestColumn = $request['columns'][$columnIdx];

            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];

            if ( $requestColumn['orderable'] == 'true' ) {
                $dir = $request['order'][$i]['dir'] === 'asc' ?
                    'ASC' :
                    'DESC';

                $orderBy[] = '`'.$column['db'].'` '.$dir;
            }
        }

        if(!implode(', ', $orderBy)) return 'ORDER BY ' . $default;

        $order = 'ORDER BY '.implode(', ', $orderBy);
    }

    return $order;
}

/**
 * Searching / Filtering
 *
 * Construct the WHERE clause for server-side processing SQL query.
 *
 * NOTE this does not match the built-in DataTables filtering which does it
 * word by word on any field. It's possible to do here performance on large
 * databases would be very poor
 *
 *  @param  array $request Data sent to server by DataTables
 *  @param  array $columns Column information array
 *  @param  array $bindings Array of values for PDO bindings, used in the
 *    sql_exec() function
 *  @return string SQL where clause
 */
public function filter ( $request, $columns, &$bindings )
{
    $globalSearch = array();
    $columnSearch = array();
    $dtColumns = self::pluck( $columns, 'dt' );

    if ( isset($request['search']) && $request['search']['value'] != '' ) {
        $str = $request['search']['value'];

        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];

            if ( $requestColumn['searchable'] == 'true' ) {
                $binding =  '\'%'. $this->db->escape($str) .'%\'';
                $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }
    }

    // Individual column filtering
    for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
        $requestColumn = $request['columns'][$i];
        $columnIdx = array_search( $requestColumn['data'], $dtColumns );
        $column = $columns[ $columnIdx ];

        $str = $requestColumn['search']['value'];

        if ( $requestColumn['searchable'] == 'true' &&
         $str != '' ) {
            $binding =  '\'%'. $this->db->escape($str) .'%\'';
            $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
        }
    }

    // Combine the filters into a single string
    $where = '';

    if ( count( $globalSearch ) ) {
        $where = '('.implode(' OR ', $globalSearch).')';
    }

    if ( count( $columnSearch ) ) {
        $where = $where === '' ?
            implode(' AND ', $columnSearch) :
            $where .' AND '. implode(' AND ', $columnSearch);
    }

    if ( $where !== '' ) {
        $where = 'WHERE ' . $where;

  if($this->filter_language !== false) {
    $where .= " AND ";
  }
    } else if($this->filter_language !== false) {
  $where .= " WHERE ";
}

    if($this->filter_language !== false) {
  $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "language` WHERE status = '1'");

  foreach ($query->rows as $result) {
    if ($result['code'] == $this->config->get('config_language')) $default_lang = $result['language_id'];
  }

  if ($this->filter_language == $default_lang) {
    $where .= " ( language_id = '" . $this->filter_language . "' OR language_id = '0' )";
  } else {
    $where .= " language_id = '" . $this->filter_language . "'";
  }
}

    return $where;
}

/**
 * Perform the SQL queries needed for an server-side processing requested,
 * utilising the helper functions of this class, limit(), order() and
 * filter() among others. The returned array is ready to be encoded as JSON
 * in response to an SSP request, or can be modified if needed before
 * sending back to the client.
 *
 *  @param  array $request Data sent to server by DataTables
 *  @param  array $sql_details SQL connection details - see sql_connect()
 *  @param  string $table SQL table to query
 *  @param  string $primaryKey Primary key of the table
 *  @param  array $columns Column information array
 *  @return array          Server-side processing response array
 */
public function simple ( $request, $type, $lang, $store, $columns )
{
    $bindings = array();

    $extra_select = '';
    $extra_where = '';
    $primaryKey = $type . '_id';

    if (in_array($type, array('product', 'category', 'information'))) {
        $select_cols = "`".implode("`, `", self::pluck_fields($columns, 'db'))."`";
        $table = '`'.DB_PREFIX . $type . "_description` INNER JOIN `" . DB_PREFIX . $type . "` USING(" . $primaryKey . ")";
        $this->filter_language = $lang;
        $default_order = 'name';

  // @perf: high impact
  if (version_compare(VERSION, '3', '>=') || ($this->config->get('mlseo_multistore') && $this->config->get('mlseo_ml_mode'))) {
    $extra_select .= ",(SELECT keyword FROM " . DB_PREFIX . $this->url_alias . " u WHERE query = CONCAT('".$type."_id=' , ".$type."_id) AND (language_id = '".(int) $lang."' OR language_id = 0) AND store_id = ".$store." LIMIT 1) AS seo_keyword";
  } else if ($this->config->get('mlseo_multistore')) {
    $extra_select .= ",(SELECT keyword FROM " . DB_PREFIX . $this->url_alias . " u WHERE query = CONCAT('".$type."_id=' , ".$type."_id) AND store_id = ".$store." LIMIT 1) AS seo_keyword";
  } else if ($this->config->get('mlseo_ml_mode')) {
    $extra_select .= ",(SELECT keyword FROM " . DB_PREFIX . $this->url_alias . " u WHERE query = CONCAT('".$type."_id=' , ".$type."_id) AND (language_id = '".(int) $lang."' OR language_id = 0) LIMIT 1) AS seo_keyword";
  } else {
    $extra_select .= ",(SELECT keyword FROM " . DB_PREFIX . $this->url_alias . " WHERE query = CONCAT('".$type."_id=' , ".$type."_id) LIMIT 1) AS seo_keyword";
  }

        if($type == 'information') {
            $default_order = 'title';
        }
    } elseif (in_array($type, array('manufacturer'))) {
        $select_cols = "`".implode("`, `", self::pluck_fields($columns, 'db'))."`";
        $this->filter_language = false;
        $table = DB_PREFIX . $type . ' m';
        $default_order = 'name';
        $extra_select = ",(SELECT keyword FROM " . DB_PREFIX . $this->url_alias . " WHERE query = CONCAT('".$type."_id=' , m.".$type."_id) LIMIT 1) AS keyword";
    } elseif (in_array($type, array('common', 'special'))) {
        $select_cols = "`query`, `keyword`, `".$this->url_alias."_id`";
        $primaryKey = $this->url_alias.'_id';
        $table = DB_PREFIX . $this->url_alias;
        $default_order = 'query';
  if ($this->config->get('mlseo_ml_mode')) {
    $this->filter_language = $lang;
    if ($type == 'common') {
      $extra_where = "AND query LIKE 'route=%'";
    } elseif ($type == 'special') {
      $extra_where = "AND query NOT LIKE 'route=%'
                   AND query NOT LIKE 'product_id=%'
                   AND query NOT LIKE 'category_id=%'
                   AND query NOT LIKE 'information_id=%'
                   AND query NOT LIKE 'manufacturer_id=%'";
    }
  } else {
    $this->filter_language = false;
    if ($type == 'common') {
      $extra_where = "AND query LIKE 'route=%'";
    } elseif ($type == 'special') {
      $extra_where = "AND query NOT LIKE 'route=%'
                   AND query NOT LIKE 'product_id=%'
                   AND query NOT LIKE 'category_id=%'
                   AND query NOT LIKE 'information_id=%'
                   AND query NOT LIKE 'manufacturer_id=%'";
    }
        }
        $type = 'url_alias';
} elseif ($type == 'absolute') {
        $select_cols = "`query`, `redirect`, `url_absolute_id`";
        $primaryKey = 'url_absolute_id';
        $table = DB_PREFIX . 'url_absolute';
        $default_order = 'query';
        $type = 'url_absolute';

  if ($this->config->get('mlseo_ml_mode')) {
    $this->filter_language = $lang;
  } else {
    $this->filter_language = false;
        }
    } elseif ($type == 'redirect') {
        $select_cols = "`query`, `redirect`, `url_redirect_id`";
  $this->filter_language = false;
        $primaryKey = 'url_redirect_id';
        $table = DB_PREFIX . 'url_redirect';
        $default_order = 'query';
        $type = 'url_redirect';
    } elseif ($type == '404') {
        $select_cols = "u.`query`, u.`count`, u.`url_404_id`, (r.query IS NOT NULL) AS has_redirect";
  $this->filter_language = false;
        $primaryKey = 'url_404_id';
        $table = DB_PREFIX . "url_404 u LEFT JOIN " . DB_PREFIX . "url_redirect r ON (u.query = r.query OR REPLACE(u.query, '".HTTP_CATALOG."', '/') = r.query)";
        $default_order = 'query';
        $type = 'url_404';
    } else {
        $this->filter_language = false;
        $table = DB_PREFIX . $type;
    }

    // Build the SQL query string from the request
    $limit = self::limit( $request, $columns );
    $order = self::order( $request, $columns, $default_order );

if ($type == 'url_404') {
  $where = self::filter( $request, array(array('db' => 'u`.`query', 'dt' => 0)), $bindings );
} else {
  $where = self::filter( $request, $columns, $bindings );
}

if (!$where) {
  $where = 'WHERE 1';
}

    // Main query to actually get the data
    $data = $this->db->query("SELECT SQL_CALC_FOUND_ROWS ". $select_cols . "
        " . $extra_select  ."
         FROM " . $table . ' '
         . $where . ' '
         . $extra_where . ' '
         . $order . ' '
         . $limit)->rows;

    // Data set length after filtering
    $resFilterLength =  $this->db->query(
        "SELECT FOUND_ROWS() as rows"
    )->row;

    $recordsFiltered = $resFilterLength['rows'];

    // Total data set length
    $resTotalLength =  $this->db->query(
        "SELECT COUNT(`{$primaryKey}`) AS total
         FROM $table"
    )->row;

    $recordsTotal = $resTotalLength['total'];

    /*
     * Output
     */
    return array(
        "draw"            => intval( $request['draw'] ),
        "recordsTotal"    => intval( $recordsTotal ),
        "recordsFiltered" => intval( $recordsFiltered ),
        "data"            => self::data_output( $columns, $data, $type )
    );
}

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Internal methods
 */

/**
 * Throw a fatal error.
 *
 * This writes out an error message in a JSON string which DataTables will
 * see and show to the user in the browser.
 *
 * @param  string $msg Message to send to the client
 */
public function fatal ( $msg )
{
    echo json_encode( array( 
        "error" => $msg
    ) );

    exit(0);
}

/**
 * Create a PDO binding key which can be used for escaping variables safely
 * when executing a query with sql_exec()
 *
 * @param  array &$a    Array of bindings
 * @param  *      $val  Value to bind
 * @param  int    $type PDO field type
 * @return string       Bound key to be used in the SQL where this parameter
 *   would be used.
 */
public function bind ( &$a, $val, $type )
{
    $key = ':binding_'.count( $a );

    $a[] = array(
        'key' => $key,
        'val' => $val,
        'type' => $type
    );

    return $key;
}

/**
 * Pull a particular property from each assoc. array in a numeric array, 
 * returning and array of the property values from each item.
 *
 *  @param  array  $a    Array to get data from
 *  @param  string $prop Property to read
 *  @return array        Array of property values
 */
public function pluck ( $a, $prop )
{
    $out = array();

    for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
            $out[] = $a[$i][$prop];
    }

    return $out;
}

公共函数pick_fields（$a，$prop）{$out=array（）；

for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
        if($a[$i][$prop] != 'keyword' && $a[$i][$prop] != 'related')
            $out[] = $a[$i][$prop];
    }

    return $out;
}

}

赞(0）分享  回复(0）举报  2021-06-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com