SQL statement in Mysql with KURDATE(), does not display API result with PHP

Asked

Viewed 87 times

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_start and top.data_end are in date format in the table. And have the dates this way stored:

2018-09-01
2018-09-30

The Goal is an API where the 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:

<?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);
?>

returns me null.

php mysql sql api restful
  • 2

    It should not be the other way round compared to the date? top.data_inicio <= CURDATE() AND top.data_fim >= CURDATE()";?

  • Hi @Leite worked. And in the API I need to remove the nickname from the fields. Thank you very much.

1 answer

2


The issue was resolved in a comment by @Milk, I’m answering just so the question has a "correct answer".

The logic in assembling the select has been reversed; the correct form must 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()";

Browser other questions tagged php mysql sql api restful

You are not signed in. Login or sign up in order to post.