I have a query that does not run correctly when I enter it in PHP:
SELECT id_continente,
id_pais,
id_localidad,
visitas,
posicion_pais,
posicion_continente
FROM (SELECT *,
@posicion := IF(@continente_actual = id_continente, @posicion + 1,
1) AS
posicion_continente,
@continente_actual := id_continente
FROM (SELECT *,
@puesto := IF(@pais_actual = id_pais, @puesto + 1, 1) AS
posicion_pais,
@pais_actual := id_pais
FROM lugar_localidades_top_site
WHERE id_site = :site
AND id_pais <> :pais
AND categoria >= :categoria
ORDER BY id_pais,
visitas DESC) rank_limitado_pais
WHERE posicion_pais <= :loc_por_pais
ORDER BY id_continente,
visitas DESC) rank_limitado_continente
WHERE posicion_continente <= :loc_por_continente
ORDER BY visitas DESC
LIMIT 0, 10
It turns out that PDO interprets the sign: = as if it were a variable to declare and by not assigning it a value simply the obvious one and the query is executed as if it were the following:
SELECT id_continente,
id_pais,
id_localidad,
visitas,
posicion_pais,
posicion_continente
FROM (SELECT *,
@posicion = IF(@continente_actual = id_continente, @posicion + 1,
1) AS
posicion_continente,
@continente_actual = id_continente
FROM (SELECT *,
@puesto = IF(@pais_actual = id_pais, @puesto + 1, 1) AS
posicion_pais,
@pais_actual = id_pais
FROM lugar_localidades_top_site
WHERE id_site = :site
AND id_pais <> :pais
AND categoria >= :categoria
ORDER BY id_pais,
visitas DESC) rank_limitado_pais
WHERE posicion_pais <= :loc_por_pais
ORDER BY id_continente,
visitas DESC) rank_limitado_continente
WHERE posicion_continente <= :loc_por_continente
ORDER BY visitas DESC
LIMIT 0, 10
So it does not cause an error but it does not generate the indexes I need for the query to work properly, any ideas to solve it?