CODEWITHSUNDEEP
phpmysqli
Nicolás Riderelli
Nicolás Riderelli

Reputation: 11

Query returns empty result in PHP,but works fine on phpmyadmin

Can you help me with something? This is my code, that should return values from an MYSQL DB. The problem is that when I print the result (array) it's empty. 

$query = 'call test("Ingreso","Ventas Exportación",2015,1,6,"Ventas Expo")';
echo $query;
$query = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($query);

(
[sum_ppto] => 
[sum_real] => 
[sum_real_ytd_pasado] => 
[ppto_anual] => 
[ppto_meses_restantes] => 
)

But when I do the exact same query, without the parameters, the array is filled with the information.

$query = 'call test2()';
echo $query;
$query = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($query);
(
    [sum_ppto] => 2987676043
    [sum_real] => 3115640330
    [sum_real_ytd_pasado] => 2843994399
    [ppto_anual] => 5217552938
    [ppto_meses_restantes] => 2101912608
)

Where is a picture when I run the routine (test) in phpmyadmin Click Here

Here is a picture of the other routine (test2) Click here

The procedure is this

select 
(select 
sum(Valor) 
from 
registro
where 
Anio_idAnio=year and
Mes_idMes between mes_inicio and mes_termino and
idReal_Ppto = (select idReal_Ppto from real_ppto where Real_Ppto = "Ppto") 
and 
idArea_Negocio=(select idArea_Negocio from area_negocio where Area_Negocio = 
area_negocio_input) and 
idNom_CC = (select idNom_CC from nom_cc where Nom_CC=nom_cc_input) and
idG_Cta = (select idG_Cta from g_cta where G_Cta=g_cta_input)) 
    as sum_ppto,
(select 
sum(Valor) 
from 
registro
where 
Anio_idAnio=year and
Mes_idMes between mes_inicio and mes_termino and
idReal_Ppto = (select idReal_Ppto from real_ppto where Real_Ppto = "Real") 
and 
idArea_Negocio=(select idArea_Negocio from area_negocio where Area_Negocio = 
area_negocio_input) and 
idNom_CC = (select idNom_CC from nom_cc where Nom_CC=nom_cc_input) and
idG_Cta = (select idG_Cta from g_cta where G_Cta=g_cta_input)) 
    as sum_real,
(select 
sum(Valor) 
from 
registro
where 
Anio_idAnio=year-1 and
Mes_idMes between mes_inicio and mes_termino and
idReal_Ppto = (select idReal_Ppto from real_ppto where Real_Ppto = "Real") 
and 
idArea_Negocio=(select idArea_Negocio from area_negocio where Area_Negocio = 
area_negocio_input) and 
idNom_CC = (select idNom_CC from nom_cc where Nom_CC=nom_cc_input) and
idG_Cta = (select idG_Cta from g_cta where G_Cta=g_cta_input))
    as sum_real_ytd_pasado,
(select 
sum(Valor) 
from 
registro
where 
Anio_idAnio=year and
Mes_idMes between 1 and 12 and
idReal_Ppto = (select idReal_Ppto from real_ppto where Real_Ppto = "Real") 
and 
idArea_Negocio=(select idArea_Negocio from area_negocio where Area_Negocio = 
area_negocio_input) and 
idNom_CC = (select idNom_CC from nom_cc where Nom_CC=nom_cc_input) and
idG_Cta = (select idG_Cta from g_cta where G_Cta=g_cta_input))
    as ppto_anual,
(select 
sum(Valor) 
from 
registro
where 
Anio_idAnio=year and
Mes_idMes not between 1 and mes_termino and
idReal_Ppto = (select idReal_Ppto from real_ppto where Real_Ppto = "Real") 
and 
idArea_Negocio=(select idArea_Negocio from area_negocio where Area_Negocio = 
area_negocio_input) and 
idNom_CC = (select idNom_CC from nom_cc where Nom_CC=nom_cc_input) and
idG_Cta = (select idG_Cta from g_cta where G_Cta=g_cta_input))
    as ppto_meses_restantes

Please help!

Thanks!

Upvotes: 1

Views: 752

Answers (1)

LSerni
LSerni

Reputation: 57408

The query is working, or you would not get the key names when you run the CALL.

So the problem must be in the selection clause.

The only plausible cause I see is that one of the parameters, "Ventas Exportación", gets mangled by a wrong charset mapping due to that "o" in Esportacion, and makes the SELECT return nothing.

As a quick fix you can try and replace the = clause with a LIKE, and pass "Ventas Esportac%" as a parameter. Or see whether some other values may be used that do not contain accented characters. If this way it does work, try then checking which charsets come into play.

The fix suggested by user @Rainmx93:

try adding mysqli_set_charset($conn, "utf8") before $query

I've met all kinds of troubles with mismatched Latin1 (the MySQL default in my installations) aka ISO-8859-1(5), CP-1282, and UTF8. Rows not being correctly selected is pretty common. You might need to change the collation of your database (do a backup first).

Upvotes: 2

Related Questions