当前位置: 动力学知识库 > 问答 > 编程问答 >

php - Prepared-statement not working while SQL query gets results

问题描述:

I've come up with this problem and I don't have a clue about the reason it isn't working properly.

Basically I have this query where I join two different tables looking for precise information, pretty long but I think here is not the problem (but I will post it just in case):

SELECT * FROM (

SELECT p.idplan, p.idautor,p.nombre,p.descripcion,p.privacidad,p.modified,a.nombre AS autor,

IF(p.idautor IN (SELECT idamigo FROM pintiplan_amigos as am WHERE p.idautor=idamigo and am.idautor= ? ), 2,

IF (p.idautor = ? ,1, 3)) AS flagRelacion

FROM pintiplan_planes AS p

LEFT JOIN pintiplan_autores AS a

ON p.idautor=a.idautor

HAVING ((p.privacidad=0) OR (p.idautor=?))

AND p.idplan IN (SELECT idplan FROM pintiplan_planes_sitios GROUP BY idplan HAVING count(1) > 1)

AND (

UPPER(p.nombre) LIKE UPPER (CONCAT('%',?,'%')) OR

UPPER(p.descripcion) LIKE UPPER (CONCAT('%',?,'%')))

) as plan

JOIN (

SELECT s.idsitio,s.latitud,s.longitud,s.imagen,ps.idplan,ps.idautor,

MIN(ACOS (

SIN(RADIANS(?)) *

SIN(RADIANS(s.latitud))+

COS(RADIANS(?)) *

COS(RADIANS(s.latitud))*

COS(RADIANS(?-s.longitud)))*6378.137) AS distancia

FROM pintiplan_planes_sitios AS ps

RIGHT JOIN pintiplan_sitios AS s

ON (ps.idautor=s.idautor) AND (ps.idsitio=s.idsitio)

GROUP BY ps.idplan,ps.idautor

) as sitio

ON sitio.idplan=plan.idplan AND sitio.idautor=plan.idautor

HAVING sitio.distancia < ?

LIMIT ?,?

My issue here I think is with my php:

if (checkPostParameters(array("cadena","radio","idautor","lat","lon","pag"))){

$cadena = $_POST["cadena"];

$radio = $_POST["radio"];

$idautor = $_POST["idautor"];

$lat = $_POST["lat"];

$lon = $_POST["lon"];

$pag = $_POST["pag"];

} else reportError("Parametros POST no correctos o estan incompletos. Linea: ".__LINE__,$db);

$db = new Db();

$response = array();

$longPag = 50;

$curPag = $longPag * intval($pag);

$db->connect()->autocommit(FALSE);

$stmt = $db->connect()->prepare(

"/* QUERY HERE */")

or reportError("El statement ha fallado. Error en la BD. Linea: ".__LINE__,$db);

$stmt->bind_param("sssssssssii",$idautor,$idautor,$idautor,$cadena,$cadena,$latitud,$latitud,$longitud,$distancia,$curPag, $longPag)

or reportError("Fallo bindeando los parametros. Linea: ".__LINE__,$db);

$stmt->execute()

or reportError("No se ha podido ejecutar la consulta. Fallo del servidor. Linea: ".__LINE__,$db);

$stmt->bind_result($idplan,$idautor,$nombre,$descripcion,$privacidad,$modified,$autor,$flagRelacion,$idsitio,$latitud,$longitud,$imagen,$idplan,$idautor,$distancia)

or reportError("No se han podido asignar valores Linea: ".__LINE__,$db);

$response["planes"]=array();

while($stmt->fetch()){

$plan=

array(

"idplan"=>$idplan,

"idautor"=>$idautor,

"nombre"=>$nombre,

"descripcion"=>$descripcion,

"privacidad"=>$privacidad,

"fecha"=>$modified,

"autor"=>$autor,

"flagRelacion"=>$flagRelacion,

"idsitio"=>$idsitio,

"latitud"=>$latitud,

"longitud"=>$longitud,

"imagen"=>$imagen,

"distancia"=>$distancia

);

$response["planes"][]=$plan;

}

$db->connect()->commit()

or reportError("Fallo en la consignación de la transacción. Linea: ".__LINE__,$db);

$stmt->close();

I don't know why but the query works perfectly when I launch it directly in my DB but when I try to do it using php the prepare() function seems to stop working.

I have tried this:

$stmt->store_result();

var_dump($stmt->num_rows);

But the result is NULL. The program isn't crashing (die is not called)

function reportError ($message,$db){

$response = array();

$response["success"]=0;

$response["message"]=$message;

return die(print_r(json_encode($response),true));

}

function checkPostParameters ($requiredParameters){

$checked = true;

foreach ($requiredParameters as $parameter){

if(!array_key_exists($parameter, $_POST) OR is_null($_POST[$parameter])) {

echo $parameter;

$checked = false;

}

}

return $checked;

}

But the results are empty when they shouldn't be according to the query results. Do you have any idea of what is going on?

分享给朋友:
您可能感兴趣的文章:
随机阅读: