我在PHP代码中有以下查询(通过AJAX调用(。
$q = 'select distinct taxon,ord,fam,gen,sp from spp
join col on gensp = concat(gen,' ',sp)
where gen ilike $1 or sp ilike $1
order by taxon,ord,fam,gen,sp';
$res = pg_query_params($conn,$q,["%$gensp%"]);
if ($res) {
echo "Works!";
} else {
echo "Error!";
}
$gensp
是用户正在查找的值(一个物种或属名,或其一部分(。当我在浏览器上运行它时;内部服务器错误";(除非我擦除join
部分(。在pgAdmin中,它起作用,例如:
select distinct taxon,ord,fam,gen,sp from spp
join col on gensp = concat(gen,' ',sp)
where gen ilike '%Casiornis%' or sp ilike '%Casiornis%'
order by taxon,ord,fam,gen,sp
上面的句子也适用于psql。我注意到问题出在串联函数上。如果我使用join col on gensp = sp
,它会起作用。我尝试过不同的变体:
join col on gensp = cast(gen as text)||' '||cast(sp as text)
join col on gensp = coalesce(gen,'')||' '||coalesce(sp,'')
join col on gensp = gen::text||' '||sp::text
join col on gensp = gen||' '||sp
所有这些都会产生相同的错误。我做错了什么?
我在Debian 10上使用PostgreSQL 11.7和PHP 7.3。
此处需要转义引号:
$q = 'select distinct taxon,ord,fam,gen,sp from spp
join col on gensp = concat(gen,' ',sp)
where gen ilike $1 or sp ilike $1
order by taxon,ord,fam,gen,sp';
或者使用双引号,如:
$q = "select distinct taxon,ord,fam,gen,sp from spp
join col on gensp = concat(gen,' ',sp)
where gen ilike $1 or sp ilike $1
order by taxon,ord,fam,gen,sp";
但在这种情况下,您需要转义$
符号以防止php翻译