CODEWITHSUNDEEP
phpmysql
rahul kapoor
rahul kapoor

Reputation: 51

How to find all the data that match a particular month from mysql database?

I am currently having problem getting all the data from the database that match a particular month and show all the data from that month only. here's what I tried.

<?php 
require('./connect.php'); 

?>
<?php
$name1 = $conn->prepare("SELECT * FROM garden WHERE MONTH(Apr)");
$name1->execute();
 while($row = $name1->fetch()) {
    $t = $row["temp"];
    echo $t;
    echo "sdasds";
    }
    ?>

The database has time column that stores date as 12-Apr-2017 and I want to find all the details of april month or may month.

Upvotes: 0

Views: 74

Answers (2)

John Conde
John Conde

Reputation: 219794

You're not using MONTH() correctly. You need to pass the column identifier as the parameter and compare its value to the value you want which is 4 as April is the fourth month:

SELECT * FROM garden WHERE MONTH(STR_TO_DATE(dateColumn, '%e-%b-%Y')) = 4

If you want a more readable query use MONTHNAME()

SELECT * FROM garden WHERE MONTHNAME(STR_TO_DATE(dateColumn, '%e-%b-%Y')) = 'April'

You will also notice I use STR_TO_DATE() as you stored your dates as strings instead of dates. In the future you should consider storing dates as date values as converting them into a different format is easy and it makes doing date work a lot easier.

Upvotes: 6

rahulb
rahulb

Reputation: 63

Instead of providing Apr as parameter, pass 4 for april month. Then

Your query would look like SELECT * FROM garden WHERE EXTRACT(MONTH FROM time) = 4;

Hope this helps :)

Upvotes: 1

Related Questions