Filter PHP and Mysql year and month

Question

How to bring record regarding month and year which user typed (the way below did not work)?

function filtrarData($datap) {
    $sql = "SELECT * FROM despesas WHERE YEAR(data_pago) = ? MONTH(data_pago) = ?";

    try {
    $com = $this->db->prepare($sql);
    $com->bindValue(1, $datap->getDataPago());
    $com->bindValue(2, $datap->getDataPago());
    $com->execute();
    $resultado = $com->fetchAll(PDO::FETCH_OBJ);
    $this->db = null;
    return $resultado;
    }
    catch (PDOException $e) {
        die( $e->getMessage());
    }
}

I’d like to receive that amount from a input date and filter by year and month entered.

Answer 1

By default the bindValue passes the parameter as string, then you need to convert the received parameter in the query date; in addition, you are comparing year and month (year() and month()) with full dates, which will not work. Try the following:

function filtrarData($datap) {
    $sql = "SELECT * FROM despesas WHERE YEAR(data_pago) = YEAR(DATE(?)) MONTH(data_pago) = MONTH(DATE(?))";

    try {
    $com = $this->db->prepare($sql);
    $com->bindValue(1, $datap->getDataPago());
    $com->bindValue(2, $datap->getDataPago());
    $com->execute();
    $resultado = $com->fetchAll(PDO::FETCH_OBJ);
    $this->db = null;
    return $resultado;
    }
    catch (PDOException $e) {
        die( $e->getMessage());
    }
}

YEAR(DATE(?)) takes only the year of a new date created with the sent parameter.

---

An alternative is to control via php:

function filtrarData($datap) {
    $sql = "SELECT * FROM despesas WHERE YEAR(data_pago) = ? MONTH(data_pago) = ?";

    try {
    $formatador = DateTime::createFromFormat("Y-m-d", $datap->getDataPago());

    $com = $this->db->prepare($sql);
    $com->bindValue(1, $formatador->format("Y"));
    $com->bindValue(2, $formatador->format("m"));
    $com->execute();
    $resultado = $com->fetchAll(PDO::FETCH_OBJ);
    $this->db = null;
    return $resultado;
    }
    catch (PDOException $e) {
        die( $e->getMessage());
    }
}

---

Based on tips given by @Bacchus, would be valid for query performance to remove use of functions (year() and month()) and add this validation as query restriction:

function filtrarData($datap) {
    $sql = "SELECT * FROM despesas WHERE data_pago >= ? AND data_pago <= ?";

    try {
    $formatador = DateTime::createFromFormat("Y-m-d", $datap->getDataPago());

    $com = $this->db->prepare($sql);
    $com->bindValue(1, $formatador->format("Y-m-")."01"); //primeiro dia do mês
    $com->bindValue(2, date("Y-m-t", strtotime($datep))); //último dia do mês
    $com->execute();
    $resultado = $com->fetchAll(PDO::FETCH_OBJ);
    $this->db = null;
    return $resultado;
    }
    catch (PDOException $e) {
        die( $e->getMessage());
    }
}

For a better understanding of this amendment, it is worth understanding this question!!