How to check if a select is null

Question

Guys, I have this code that pulls info from the server

try {
    $conecta  = new PDO("mysql:host=$servidor;dbname=$banco", $usuario, $senha);
    $consulta = $conecta->prepare('SELECT * FROM tb02_cardapiosem WHERE tb02_diasemana=' . $data);
    $consulta->execute(array());
    $resultadoDaConsulta = $consulta->fetchAll();

    $StringJson = "[";

    if (count($resultadoDaConsulta)) {
        foreach ($resultadoDaConsulta as $registro) {

            if ($StringJson != "[") {
                $StringJson .= ",";
            }
            $StringJson .= '{"tb02_lanchedia":"' . $registro[tb02_lanchedia] . '",';
            $StringJson .= '"tb02_almoco":"' . $registro[tb02_almoco] . '",';
            $StringJson .= '"tb02_cafe":"' . $registro[tb02_cafe] . '",';
            $StringJson .= '"tb02_lanchenoite":"' . $registro[tb02_lanchenoite] . '",';
            $StringJson .= '"tb02_diasemana":"' . $registro[tb02_diasemana] . '"}';

        }
        echo $StringJson . "]"; // Exibe o vettor JSON
    }   
}   
catch (PDOException $e) {
    echo 'ERROR: ' . $e->getMessage(); // opcional, apenas para teste
}

Then I have this code that plays the information found in a div

function ConectaServidor(response) {
    var dados = JSON.parse(response); //faz a conversão do texto da WEB para JSON
    var i;
    var conteudo = "";
    var conteudo2 = "";
    var conteudo3 = "";
    var conteudo4 = "";
    for (i = 0; i < dados.length; i++) //dados.length retorna o tamanho do vetor.
    {
        conteudo += dados[i].tb02_cafe;
        conteudo2 += dados[i].tb02_lanchedia;
        conteudo3 += dados[i].tb02_almoco;
        conteudo4 += dados[i].tb02_lanchenoite;
    }

    document.getElementById("cafe1").innerHTML = conteudo;
    document.getElementById("lanche1").innerHTML = conteudo2;
    document.getElementById("almoco1").innerHTML = conteudo3;
    document.getElementById("lanchen1").innerHTML = conteudo4;
}

However it is not all the dates that are registered and would like when a date is not found, he put the Div’s innerHTML as "There is no record!". How to do?

Answer 1

Good morning!

First, I suggest you use some Javascript library like Jquery, for example. I believe it will really facilitate your work on the front end and the way you will handle the data.

The second point that in my opinion should be improved is the way you are handling the data in the back end. It would be much simpler and more correct for you to return the result of your SQL query and turn it into a true json format (not mounting a String), using the json_encode() function that is native to PHP.

To answer your question, come on. In front-end, when you receive the data and manipulate it seems to me a little confused because the snacks/cafes are all connected in a single variable that will then be displayed in an HTML element. I believe that each of these values should be assigned to a list, for example, it would make more sense. Thus, the only thing you should accomplish to assign the desired content to dates that have no value would be something like:

conteudoX = (dados[i].tb02_dia_semana) ? dados[i].tb02_dia_semana: 'Data não informada';

After the execution of this condition your variable conteudoX will have the expected value and this way you can assign to the element you want in HTML.

I hope I’ve helped :)

Hugs!

Answer 2

You can use the SQL query itself to return "No record!" as a field value if it is null in the tb02, using coalesce(). This function returns the first non-null value found in the list:

SELECT 
    tb02_diasemana,
    coalesce(tb02_lanchedia,   'Não há registro!') as tb02_lanchedia,
    coalesce(tb02_almoco,      'Não há registro!') as tb02_almoco,
    coalesce(tb02_cafe,        'Não há registro!') as tb02_cafe,
    coalesce(tb02_lanchenoite, 'Não há registro!') as tb02_lanchenoite
FROM tb02_cardapiosem 
WHERE tb02_diasemana=' . $data

The advantage is that the rest of your code does not change. Breaking prevents the use of select *, that can cause headaches in the future if new columns are added to the table.

Answer 3

Guys, I solved with a simple Else. Check and compare with the previous code:

    try {
$conecta  = new PDO("mysql:host=$servidor;dbname=$banco", $usuario, $senha);
$consulta = $conecta->prepare('SELECT * FROM tb02_cardapiosem WHERE tb02_diasemana=' . $data);
$consulta->execute(array());
$resultadoDaConsulta = $consulta->fetchAll();

$StringJson = "[";

if (count($resultadoDaConsulta)) {
    foreach ($resultadoDaConsulta as $registro) {

        if ($StringJson != "[") {
            $StringJson .= ",";
        }
        $StringJson .= '{"tb02_lanchedia":"' . $registro[tb02_lanchedia] . '",';
        $StringJson .= '"tb02_almoco":"' . $registro[tb02_almoco] . '",';
        $StringJson .= '"tb02_cafe":"' . $registro[tb02_cafe] . '",';
        $StringJson .= '"tb02_lanchenoite":"' . $registro[tb02_lanchenoite] . '",';
        $StringJson .= '"tb02_diasemana":"' . $registro[tb02_diasemana] . '"}';

    }
    echo $StringJson . "]"; // Exibe o vettor JSON
}else{
        echo $StringJson = '["Não há dados!"]';
    }
}       
   catch (PDOException $e) {
       echo 'ERROR: ' . $e->getMessage(); // opcional, apenas para teste
     }

There in my javascript:

    function ConectaServidor(response) {
    var dados = JSON.parse(response);
    if(dados=="Não há dados!"){
        document.getElementById("cafe1").innerHTML = dados;
        document.getElementById("lanche1").innerHTML = dados;
        document.getElementById("almoco1").innerHTML = dados;
        document.getElementById("lanchen1").innerHTML = dados;
    }else{
    var i;
    var conteudo = "";
    var conteudo2 = "";
    var conteudo3 = "";
    var conteudo4 = "";
    for (i = 0; i < dados.length; i++) //dados.length retorna o tamanho do vetor.
    {
        conteudo += dados[i].tb02_cafe;
        conteudo2 += dados[i].tb02_lanchedia;
        conteudo3 += dados[i].tb02_almoco;
        conteudo4 += dados[i].tb02_lanchenoite;
    }

    document.getElementById("cafe1").innerHTML = conteudo;
    document.getElementById("lanche1").innerHTML = conteudo2;
    document.getElementById("almoco1").innerHTML = conteudo3;
    document.getElementById("lanchen1").innerHTML = conteudo4;
    }}}

Anyway, thank you all! ;)