Filter year and month PHP and MySql

Question

Filter year and month PHP and MySql

Navigation

#1 by (1 votes)

1

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 would like to receive this value from an input date and filter by the year and month typed.

php mysql

asked by anonymous 31.10.2018 / 14:19

1 answer

How to click on link open corresponding panel Warning: "No local application has been provided. You can define it in the Run menu, Parameters "Dynamic DLL Creation problem in C ++ in DEV ++

score 1 · Accepted Answer

By default the bindValue passes the parameter to string , then you need to convert the received parameter to date in the query; in addition, you're comparing year and month (% with% and% with%) with whole 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() takes only the year of a new date created with the parameter sent.

An alternative is to make the 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 the tips given by @Bacco , it would be valid for query performance to remove the use of functions ( month() and YEAR(DATE(?)) ) and add this validation as a query constraint:

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 change, please understand this question !!