CODEWITHSUNDEEP
phpsqlmysqli
Felipe Marcolin
Felipe Marcolin

Reputation: 38

Php query don't work with mysqli_query() but work on php my admin

Im trying to use this query on php with mysqli_query():

$query = 
    ("
                SELECT
                    COALESCE(
                        (
                            SELECT
                                indicadores_respostas.resposta_text
                            FROM
                                indicadores_respostas
                                LEFT JOIN indicadores_secoes_itens ON indicadores_secoes_itens.id = indicadores_respostas.id_secao_item
                            WHERE
                                indicadores_respostas.id_indicador = indicadores.id
                                AND indicadores_secoes_itens.titulo = 'Longitude'
                        ),
                        (
                            SELECT
                                cidades.longitude
                            FROM
                                cidades
                                INNER JOIN indicadores_respostas
                                LEFT JOIN indicadores_secoes_itens ON indicadores_secoes_itens.id = indicadores_respostas.id_secao_item
                            WHERE
                                indicadores.id = indicadores_respostas.id_indicador
                                AND cidades.cidades_id = CAST(indicadores_respostas.resposta_text AS SIGNED)
                                AND indicadores_secoes_itens.titulo = 'Cidade'
                        )
                    ) AS Longitude
                FROM
                    indicadores
                    LEFT JOIN indicadores_respostas ON indicadores_respostas.id_indicador = indicadores.id
                    LEFT JOIN indicadores_secoes_itens ON indicadores_secoes_itens.id = indicadores_respostas.id_secao_item
                WHERE
                    indicadores_secoes_itens.titulo = 'Nome da capacitação' OR indicadores_secoes_itens.titulo = 'Instituição'
    ");

    $result = mysqli_query($connection, $query);

But in Php its returning me 0 rows, but the exactly same query on PHP my admin, in the same database, return 2 rows

enter image description here

someone know what am I doing wrong?

Here is my connection string:

mysqli_report(MYSQLI_REPORT_STRICT);
try {
    $connection = mysqli_connect("localhost", "root", "", "test_db", "3308");

OBS: $Result is returning true and if I made some more "gereric" query, like

SELECT * FROM Cidades

that works normally

MORE INFORMATION:

with or without try catch, my return is

enter image description here

Upvotes: 0

Views: 107

Answers (1)

Dharman
Dharman

Reputation: 33315

The correct way to connect using mysqli involves 3 steps: enabling error reporting, creating an instance of mysqli class, and setting the correct charset.

The example taken from PHP.net demonstrates these steps:

<?php

/* You should enable error reporting for mysqli before attempting to make a connection */
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$mysqli = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');

/* Set the desired charset after establishing a connection */
mysqli_set_charset($mysqli, 'utf8mb4');

Most of the time the correct charset is utf8mb4, but you might be using a different one. If you are still using the deprecated one called utf8 I recommend you change it as soon as possible.

The proper charset ensures that MySQL understands the character encoding used by the client. Phrases like 'Nome da capacitação' might be encoded differently when using different charsets and MySQL will return no matching results if you use incorrect charset. You have to make sure that the connection charset matches the one used in the table collation. As a best practice, I strongly recommend you stick to using utf8mb4 everywhere.

Upvotes: 2

Related Questions