使用 PHP 和 MySQL 的数据表：如何修改 mysql 查询？

DataTables using PHP with MySQL: How to modify the mysql query?

提问by theBookeyMan

So I'm trying to populate a html table using a table in my database. Lets call it 'business' and one of the columns is called 'id'. I am trying to use WHERE. I For example something like..

所以我试图用我的数据库中的一个表来填充一个 html 表。让我们称之为“业务”，其中一列称为“id”。我正在尝试使用 WHERE。我例如像..

SELECT * all FROM business WHERE id=4. 

Do I need to modify $sWhere?

我需要修改 $sWhere 吗？

Here is the code that I am using for server side processing.

这是我用于服务器端处理的代码。

/*
 * Script:    DataTables server-side script for PHP and MySQL
 * Copyright: 2010 - Allan Jardine, 2012 - Chris Wright
 * 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('bus_num', 'bus_phone', 'category_name', 'bus_status');

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "business";

/* Database connection information */
$gaSql['user']       = "dev";
$gaSql['password']   = "dev";
$gaSql['db']         = "dev";
$gaSql['server']     = "localhost";

/* REMOVE THIS LINE (it just includes my SQL connection user/pass) */
$db = mysql_connect('localhost', 'dev', 'dev');

if (!$db){
    die ("Could not connect to database" . mysql_error());
    exit();
}

$db_selected = mysql_select_db('dev', $db);

if(!$db_selected) {
    die ('Can\'t use:' . mysql_error());
}


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP server-side, there is
 * no need to edit below this line
 */

/*
 * Local functions
 */
function fatal_error ( $sErrorMessage = '' )
{
    header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
    die( $sErrorMessage );
}


/*
 * MySQL connection
 */
if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
{
    fatal_error( 'Could not open connection to server' );
}

if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
    fatal_error( 'Could not select database ' );
}


/*
 * Paging
 */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
    $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
        intval( $_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] ) ]."
                ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
        }
    }

    $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 fatal_error( 'MySQL Error: ' . mysql_errno() );

/* Data set length after filtering */
$sQuery = "
    SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$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 fatal_error( 'MySQL Error: ' . mysql_errno() );
$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 );

回答by KatrokNjember

/*
* 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 id='4' AND (";
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 .= ')';
} else {
$sWhere = "WHERE id = '4' ";
}

回答by Jess Stone

DataTablesis a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table.

DataTables是 jQuery Javascript 库的插件。它是一个高度灵活的工具，基于渐进增强的基础，可以为任何 HTML 表格添加高级交互控件。

you might want to use:

你可能想使用：

Server-side processing | PHP with MySQL This script serves as the basis for the testing and development that happens with DataTables, so it is always right up to date, and will always implement all of the features that are supported in DataTables with server-side processing (with the exception of regex filtering - for database access speed reasons).

To use the code on your own server, simply change the $aColumns array to list the columns you wish to include from your database, set $sIndexColumn to a column which is indexed (for speed), $sTable to the table name, and finally fill in your database connection parameters to $gaSql. Please note that this script uses json_encode which requires PHP 5.2 or newer. For a version of this script which is compatible with older versions please see the PHP 4 compatible

服务器端处理 | PHP with MySQL 此脚本作为 DataTables 测试和开发的基础，因此它始终是最新的，并且将始终通过服务器端处理实现 DataTables 中支持的所有功能（例外正则表达式过滤 - 出于数据库访问速度的原因）。

要在您自己的服务器上使用代码，只需更改 $aColumns 数组以列出您希望从数据库中包含的列，将 $sIndexColumn 设置为已建立索引的列（为了速度），将 $sTable 设置为表名，最后将您的数据库连接参数填入 $gaSql。请注意，此脚本使用需要 PHP 5.2 或更高版本的 json_encode。有关与旧版本兼容的此脚本的版本，请参阅 PHP 4 compatible

http://datatables.net/development/server-side/php_mysql

http://datatables.net/development/server-side/php_mysql