SQL statement in MySQL with CURDATE (), does not display API result with PHP

1

In this SQL what is wrong?

SELECT
        top.id_topatletas,
        top.idusuarios,
        top.posicao,
        top.workout,
        top.data_inicio,
        top.data_fim,
        user.idusuarios,
        user.nome
     FROM
        top_atletas top INNER JOIN usuarios user ON top.idusuarios = user.idusuarios
     WHERE 
        top.data_inicio >= CURDATE() AND
        top.data_fim <= CURDATE()

Since the fields: top.data top and top.data_fim are in date format in the table. And the dates in this form are stored:

2018-09-01
2018-09-30

Objective is an API where DAO is:

class BoxDAO {

    private $conexao;

    public function __construct() {
        $this->conexao = new Conexao();
    }

    public function consultarTop10() {
        $sql = "SELECT
        top.id_topatletas,
        top.idusuarios,
        top.posicao,
        top.workout,
        top.data_inicio,
        top.data_fim,
        user.idusuarios,
        user.nome
     FROM
        top_atletas top INNER JOIN usuarios user ON top.idusuarios = user.idusuarios
     WHERE 
        top.data_inicio >= CURDATE() AND
        top.data_fim <= CURDATE()";

        $resultado = mysqli_query($this->conexao->getCon(), $sql);

        if (mysqli_num_rows($resultado) > 0) {
            return $resultado;
        } else {
            return false;
        }
    }

And where I call is:

<?php

if (isset($_SERVER['HTTP_ORIGIN'])) {
    header("Access-Control-Allow-Origin: {$_SERVER['HTTP_ORIGIN']}");
    header("Access-Control-Allow-Origin: *");
    header('Access-Control-Allow-Credentials: true');
    header('Access-Control-Max-Age: 86400');    // cache for 1 day
}

// Access-Control headers are received during OPTIONS requests
if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') {
    if (isset($_SERVER['HTTP_ACCESS_CONTROL_REQUEST_METHOD'])) {
        header('Access-Control-Allow-Methods: GET, POST, OPTIONS');
    }

    if (isset($_SERVER['HTTP_ACCESS_CONTROL_REQUEST_HEADERS'])) {
        header("Access-Control-Allow-Headers: {$_SERVER['HTTP_ACCESS_CONTROL_REQUEST_HEADERS']}");
    }

    exit(0);
}

include "../Classes/Conexao.php";
include '../Classes/DAO/boxTopDAO.php';


$BoxDAO = new BoxDAO();

$consulta = $BoxDAO->consultarTop10();

if ($consulta == true) {
    for ($i = 0; $i < mysqli_num_rows($consulta); $i++) {
        $linha = mysqli_fetch_array($consulta);     

        $respostas [] = array(
            'top.id_topatletas' => $linha['top.id_topatletas'],
            'top.idusuarios' => $linha['top.idusuarios'],
            'top.posicao' => $linha['top.posicao'],
            'top.workout' => $linha['top.workout'],
            'user.nome' => $linha['user.nome']
        );
    }
}

echo json_encode($respostas);
?>

Return me null.

    
asked by anonymous 03.09.2018 / 16:25

1 answer

2

The issue was resolved in a comment by @Leite , I am only responding to that the question has a "correct answer."

The assembly logic of select has been reversed; the correct form should be as follows:

SELECT
    top.id_topatletas,
    top.idusuarios,
    top.posicao,
    top.workout,
    top.data_inicio,
    top.data_fim,
    user.idusuarios,
    user.nome
FROM top_atletas top INNER JOIN usuarios user ON top.idusuarios = user.idusuarios
WHERE top.data_inicio <= CURDATE() 
  AND top.data_fim >= CURDATE()";
    
03.09.2018 / 21:09