phpmyadmin查询mysql_MySQL查询在phpMyAdmin中工作,在PHP中失败

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

@H_502_42@

最佳答案@H_502_42@

没有分析代码,但查询我没有看到语法错误.但我建议你写这样的查询：

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

应该给出相同的结果.@H_502_42@

相关文章

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

如您喜欢交流学习经验，点击链接加入交流1群：1065694478(已满)交流2群：163560250