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
The idea is that you have a master list of substances (tbl_substances) then if you have entered any information about them in tbl_substances_lists or tbl_substances_info then that can be displayed too (as long as you are logged in with the right organisation ID)
这个想法是你有一个物质的主列表(tbl_substances)然后如果你在tbl_substances_lists或tbl_substances_info中输入了关于它们的任何信息,那么也可以显示(只要你使用正确的组织ID登录)
It's important to show all the substances even if they have no custom information which is why I'm using a LEFT JOIN.
显示所有物质非常重要,即使它们没有自定义信息,这也是我使用LEFT JOIN的原因。
This query works perfectly in phpMyAdmin but when I use it in my database script I get:
这个查询在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, list1, list2, list3, list4, list5 FROM tbl_substances_' at line 2
您的SQL语法有错误;查看与您的MySQL服务器版本相对应的手册,以便在''左侧使用正确的语法(在第2行,选择subid,list1,list2,list3,list4,list5 FROM tbl_substances_')
I'm not sure whether the problem is something obvious I'm missing or is it something to do with the fact that this bit of code uses mysql_query which I know is deprecated and old fashioned etc. etc.
我不确定问题是否是显而易见的我缺少的,或者是否与这个代码使用mysql_query这个事实有关,我知道这个问题已被弃用和老式等等。
I'm not a database expert so if this query looks very ugly to you then I apologise in advance!
我不是数据库专家,所以如果这个查询看起来非常难看,那么我提前道歉!
EDIT 2
编辑2
Here's the code for building this query (it gets built dynamically depending on what you're searching for but this is the basic form)
这是构建此查询的代码(它根据您要搜索的内容动态构建,但这是基本形式)
/*
* 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 ; $i<intval( $_POST['iSortingCols'] ) ; $i++ )
{
if ( $_POST[ 'bSortable_'.intval($_POST['iSortCol_'.$i]) ] == "true" )
{
$iColumnIndex = array_search( $_POST['mDataProp_'.$_POST['iSortCol_'.$i]], $aColumns );
$sOrder .= $aColumns[ $iColumnIndex ]."
".mysql_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, list1, list2, list3, list4, 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());
Imagine there is nothing in $sWhere and $sOrder at the moment - $sLimit is chosen by the user but in this case it will be LIMIT 0, 25 to get the first 25 records.
想象一下,$ sWhere和$ sOrder目前没有任何内容 - $ sLimit由用户选择,但在这种情况下,它将是LIMIT 0,25来获得前25个记录。
This all combines in this case to make the result of echoing out $sQuery:
在这种情况下,这一切都结合起来,以回显$ sQuery:
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
2 个解决方案
#1
2
Haven't analyzed the code, but querywise I see no syntax error. But I'd advise, that you write the query like this:
没有分析代码,但查询我没有看到语法错误。但我建议你写这样的查询:
SELECT SQL_CALC_FOUND_ROWS *
FROM tbl_substances s
LEFT JOIN tbl_substances_lists x ON s.subst_id = x.subid AND x.orgid = '1'
LEFT JOIN tbl_substances_info y ON s.subst_id = y.subid AND y.orgid = '1'
LIMIT 0, 25
Should give the same result.
应该给出相同的结果。
#2
2
In my case, I was working with "ISO 8859-1 Characters" (Ç,Ã,é....), doing: utf8_decode($query); solved this problem.
在我的情况下,我正在使用“ISO 8859-1 Characters”(Ç,Ã,é....),执行:utf8_decode($ query);解决了这个问题。
#1
2
Haven't analyzed the code, but querywise I see no syntax error. But I'd advise, that you write the query like this:
没有分析代码,但查询我没有看到语法错误。但我建议你写这样的查询:
SELECT SQL_CALC_FOUND_ROWS *
FROM tbl_substances s
LEFT JOIN tbl_substances_lists x ON s.subst_id = x.subid AND x.orgid = '1'
LEFT JOIN tbl_substances_info y ON s.subst_id = y.subid AND y.orgid = '1'
LIMIT 0, 25
Should give the same result.
应该给出相同的结果。
#2
2
In my case, I was working with "ISO 8859-1 Characters" (Ç,Ã,é....), doing: utf8_decode($query); solved this problem.
在我的情况下,我正在使用“ISO 8859-1 Characters”(Ç,Ã,é....),执行:utf8_decode($ query);解决了这个问题。