Why does not my query work?

Question

Why does not my query work?

Navigation

#1 by (2 votes)

0

I have a query in a table that should return the value of the number of records found that meet a given requirement.

The code:

<?php

    require('conn.php');

    $prevJan= "SELECT COUNT(ID) AS PrevJan FROM participantes WHERE PREVISTO = 'Previsto' and FORMACAO = 'Revues Technique' and MES = 'jan' and AREA = 'R&D'";
    $realJan= "SELECT COUNT(ID) AS RealJan FROM participantes WHERE REALIZADO = 'Realizado' and FORMACAO = 'Revues Technique' and MES = 'jan' and AREA = 'R&D'";

    $consultapj = mysqli_query($prevJan);
    $consultarj = mysqli_query($realJan);

    var_dump($consultapj);
?>

The connection:

<?php

    $connection = mysqli_connect("localhost", "root", "", "db_formacao");

    if (mysqli_connect_errno())
    {
       echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
?>

If I make a query like this by phpMyAdmin, fine, it returns the right value, however, if when I try to print the value on my page php, printa NULL.

Also, I want to get this value and put it as an item in a list, see:

data: [<?php echo($consultarj);?>]

Does anyone have an idea of what might be happening?

php select mysqli

asked by anonymous 14.09.2017 / 19:35

1 answer

Best way to capture the progress of an upload Add array inside array

score 2 · Accepted Answer

The mysqli_query returns a object and not the result of the query, to get the SELECT COUNT(*) first of a nickname uses AS (alias) :

$prevJan= "SELECT COUNT(*) as TOTAL FROM participantes WHERE PREVISTO = 'Previsto' and FORMACAO = 'Revues Techniques' and MES = 'jan' and AREA = 'R&D'";
$consultapj = mysqli_query($prevJan);

And then use fetch_assoc , like this:

$consultapj = mysqli_query($prevJan);

if ($consultapj = mysqli_query($connection, $prevJan)) {

    /* fetch associative array */
    while ($row = mysqli_fetch_assoc($consultapj)) {
        echo 'Total:', $row['TOTAL'];
    }

    /* free result set */
    mysqli_free_result($consultapj);
} else {
    die(mysqli_error($connection));
}

Note I passed $connection on mysqli_query

If you return zero ( 0 ) either because TABLE is case-sensitive or you missed something in WHERE

[Edit]

After editing the question, the explanation of two queries is simple, fetch_array or fetch_assoc should preferably stick with your queries, in order and the row variable should not be reused because we are using to get the results within the while / if, mixing with other variables may confuse the results:

//Variaveis para pegar os resultados
$totaPJ = 0;
$totaRJ = 0;

//qUERYS
$prevJan = "SELECT COUNT(*) AS TOTAL FROM participantes WHERE PREVISTO = 'Previsto' and FORMACAO = 'Revues Technique' and MES = 'jan' and AREA = 'R&D'";

$realJan = "SELECT COUNT(*) AS TOTAL FROM participantes WHERE REALIZADO = 'Realizado' and FORMACAO = 'Revues Technique' and MES = 'jan' and AREA = 'R&D'";

//Pega o resultado de prevJan
if ($resultado = mysqli_query($connection, $prevJan)) {

    if ($row = mysqli_fetch_assoc($resultado)) {
        $totaPJ = $row['TOTAL']; //Seta o total
    }

    mysqli_free_result($resultado);
} else {
    die(mysqli_error($connection));
}

//Pega o resultado de realJan
if ($resultado = mysqli_query($connection, $realJan)) {

    if ($row = mysqli_fetch_assoc($resultado)) {
        $totaRJ = $row['TOTAL']; //Seta o total
    }

    mysqli_free_result($resultado);
} else {
    die(mysqli_error($connection));
}

And in the chart you'll probably do this:

{
    name: 'Realizado',
    type: 'column',
    yAxis: 1,
    data: [<?php echo $totalRJ; ?>],
    tooltip: {
        valueSuffix: ''
    }

}, {
    name: 'Previsto',
    type: 'spline',
    data: [<?php echo $totalPJ; ?>],
    tooltip: {
        valueSuffix: ''
    }
}