CODEWITHSUNDEEP
phpmysql
Mohd Fadli
Mohd Fadli

Reputation: 143

How can i show Data from mysql specific month

$query = mysql_query(
         "SELECT id, BuyerName,BuyerEmail,TransactionID,DateTime FROM `order`
         WHERE DateTime="2015 AND 08""; LIMIT $start, $per_page"
         ) 
or die(mysql_error());

I'm trying to make the query to show specific month and year.

Upvotes: 4

Views: 91

Answers (4)

viral
viral

Reputation: 3743

$time_you_want_to_choose = strtotime('previous month');

$ym = date('Y-m', $time_you_want_to_choose);

$query = mysql_query("
          SELECT `id`, `BuyerName`, `BuyerEmail` , `TransactionID`, `DateTime` 
          FROM `order` 
          WHERE `DateTime` like '$ym%'
          LIMIT $start, $per_page
         ");

Upvotes: 0

Luthando Ntsekwa
Luthando Ntsekwa

Reputation: 4218

You can use MySQL's Built-in YEAR and MONTH functions like: 

SELECT `id`, `BuyerName`,`BuyerEmail`,`TransactionID`,`DateTime`   
FROM `order` WHERE YEAR(DATE(`DateTime`))=2015 AND MONTH(DATE(`DateTime`)) = 8

Upvotes: 2

M0rtiis
M0rtiis

Reputation: 3774

if type of column is datetime then try this

$query = mysql_query("
    SELECT id, BuyerName, BuyerEmail, TransactionID, `DateTime` 
    FROM `order` 
    WHERE YEAR(DATE(`DateTime`)) = 2015 AND MONTH(DATE(`DateTime`)) = 8
    LIMIT $start, $per_page
") or die(mysql_error());

or simply

$query = mysql_query("
    SELECT id, BuyerName, BuyerEmail, TransactionID, `DateTime` 
    FROM `order` 
    WHERE `DateTime` LIKE '2015-08-%'
    LIMIT $start, $per_page
") or die(mysql_error());

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

If your column has a datatype of date you can use a range of dates for your criteria in a Sargable way

SELECT id, BuyerName,BuyerEmail,TransactionID,DateTime
 FROM `order` 
WHERE DateTime >='01-08-2015' 
AND  DateTime <= '31-08-2015'

For datetime you can write it as 

SELECT id, BuyerName,BuyerEmail,TransactionID,DateTime
 FROM `order` 
WHERE DateTime >='01-08-2015 00:00:00' 
AND  DateTime <= '31-08-2015 23:59:59'

Upvotes: 0

Related Questions