Why isn’t my consultation working?

Question

I have a query in a table that should return the amount of records found that meet a certain 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, great, returns the correct value, however, if when I try to print the value in my php page, printa NULL.

Also, I want to take this value and put as a list item, see:

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

Does anyone have any idea what might be going on?

Answer 1

The mysqli_query returns an object and not the result 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, thus:

$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 that I passed the $connection in the mysqli_query

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

---

[Editing]

After editing the question, the explanation of two querys is simple, fetch_array or fetch_assoc should preferably stay with your queries, in order and the variable row should not be reused, because we are using to take the results inside 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 on the chart will probably do so:

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

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