SELECT sql_CALC_FOUND_ROWS *
FROM (
SELECT *
FROM tbl_substances
LIMIT 0,25
) AS s
LEFT JOIN (
SELECT subid,list1,list2,list3,list4,list5
FROM tbl_substances_lists
WHERE orgid = '1'
) AS x ON s.subst_id = x.subid
LEFT JOIN (
SELECT subid,info
FROM tbl_substances_info
WHERE orgid = '1'
) AS y ON s.subst_id = y.subid
这个想法是你有一个物质的主列表(tbl_substances)然后如果你在tbl_substances_lists或tbl_substances_info中输入了关于它们的任何信息,那么也可以显示(只要你使用正确的组织ID登录)
显示所有物质非常重要,即使它们没有自定义信息,这就是我使用LEFT JOIN的原因.
此查询在PHPMyAdmin中完美运行,但当我在我的数据库脚本中使用它时,我得到:
You have an error in your sql Syntax; check the manual that corresponds to your MysqL server version for the right Syntax to use near ‘) AS s LEFT JOIN (SELECT subid,list5 FROM tbl_substances_’ at line 2
我不确定问题是否是显而易见的我错过了,或者是否与这个代码使用MysqL_query的事实有关,我知道这个问题已被弃用和老式等等.
我不是数据库专家,所以如果这个查询看起来很难看,那我就提前道歉!
编辑2
这是构建此查询的代码(它根据您要搜索的内容动态构建,但这是基本形式)
/*
* Length
*/
if ( isset( $_POST['iDisplayStart'] ) && $_POST['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".MysqL_real_escape_string( $_POST['iDisplayStart'] ).",".
MysqL_real_escape_string( $_POST['iDisplayLength'] );
}
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_POST['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $iMysqL_real_escape_string( $_POST['sSortDir_'.$i] ) .",";
}
}
$sOrder = substr_replace( $sOrder,"",-2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
/*
* Table info
*/
$sTable = "tbl_substances ".$sLimit.") AS s
LEFT JOIN (
SELECT subid,list5
FROM tbl_substances_lists
WHERE orgid = '".$orgid."'
) AS x
ON s.subst_id = x.subid
LEFT JOIN (
SELECT subid,info
FROM tbl_substances_info WHERE orgid = '".$orgid."'
) AS y
ON s.subst_id = y.subid";
$sWhere = "";
/*
* sql queries
* Get data to display
*/
$sQuery = "
SELECT sql_CALC_FOUND_ROWS * FROM (SELECT * FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = MysqL_query( $sQuery ) or die(MysqL_error());
想象一下,$sWhere和$sOrder目前没有任何内容 – $sLimit由用户选择,但在这种情况下,它将是LIMIT 0,25来获得前25个记录.
在这种情况下,这一切都结合起来,以回显$sQuery:
SELECT sql_CALC_FOUND_ROWS *
FROM (
SELECT *
FROM tbl_substances LIMIT 0,25
) AS s
LEFT JOIN (
SELECT subid,list5
FROM tbl_substances_lists
WHERE orgid = '1'
) AS x
ON s.subst_id = x.subid
LEFT JOIN (
SELECT subid,info
FROM tbl_substances_info
WHERE orgid = '1'
) AS y
ON s.subst_id = y.subid
最佳答案