我在php中执行此查询时出现错误:
SELECT hit.timestamp,
hit.id,
config.Name,
hit.meter_id,
levels.LevelName,
pos.sm_pos,
hit.hit_value
FROM hit
INNER JOIN config
ON hit.id = config.id
INNER JOIN levels
ON hit.meter_id = levels.id
INNER JOIN POS
ON pos.id = hit.id
INNER JOIN controllers
ON pos.controller_id = controllers.id;
问题是我在 select 语句中的每一列上都获得了未定义的索引,除了第一列。第一拳没有给我错误,它正常地给了我数据。其他人则给出错误。
知道出了什么问题吗?我需要改变什么?
编辑:使用数据表脚本时使用此查询。所以完全 php 代码是 当使用此脚本时:
<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'hit.timestamp','hit.id', 'config.Name', 'hit.meter_id','levels.LevelName','pos.sm_pos','hit.hit_value' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "hit.id";
/* DB table to use */
$sTable = "hit
INNER JOIN config
ON hit.id = config.id
INNER JOIN levels
ON hit.meter_id = levels.id
INNER JOIN POS
ON pos.id = hit.id
INNER JOIN controllers
ON pos.controller_id = controllers.id";
/* Database connection information */
$gaSql['user'] = "";
$gaSql['password'] = "";
$gaSql['db'] = "";
$gaSql['server'] = "localhost";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );
mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
/*
* Filtering
* 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, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
{
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
}
}
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "version" )
{
/* Special output formatting for 'version' column */
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}
echo json_encode( $output );
?>
输出时的最后一部分使用的是$row[] = $aRow[ $aColumns[$i] ];
,我认为这就是问题所在,因为我在 select 语句中使用 table.column 类型。有趣的是,我从 select 语句中的第一列正在进入低谷,我正在获取值。其他人给了我未定义的索引。
此问题
已通过使用 DataTables 示例脚本的修改版本和对 SO 聊天的进一步讨论得到解决。
一旦有时间,我会以正确答案的形式为未来的访客提供完整的文章。
<小时 />简而言之:
DataTables 示例 PHP 脚本不允许在其字段列表机制中使用列别名或 MySQL 函数调用。这使得任何执行 JOIN 或对结果应用某种形式的数据转换的查询都无法在不进行某些修改的情况下完成。
上面链接的修改版本使用正则表达式来安全地使用字段别名,但仍然不允许函数调用,这是在这种情况下产生所需结果真正需要的。通过将一些转换工作从订阅到 PHP 中,并进行一些额外的修改以将用户定义的日期范围应用于返回的结果,已经解决了这个问题。
通过查看上面的链接,可以看到确切完成的工作和原因的完整详细信息。
<小时 />如果我从流程中学到的一件事可能对未来的访问者有用,那就是:
如果您需要的不仅仅是最简单的查询来与DataTables一起使用,那么您最好确保了解后端代码的每一行以及它如何与前端交互。
提供的示例脚本确实是一个很好的起点,但它们的功能绝不是详尽无遗的,您需要准备好修改甚至完全重写它们才能获得所需的结果。