jad jad - 5 months ago 11
MySQL Question

select month date from database

i have this code to select distinct value and give me the number of values
i want to select only the value in this month
ex:give me all value of month 06 only in the same way distinct value number only in the month 06 or 05

Remarque: the date type in the database is varchar not date.

this is the code

$sql=mysqli_query($conn,"select db_shopname, COUNT(db_shopname) as num FROM
(select tbl_order.db_shopname from tbl_order union all select tbl_item.db_shopname from tbl_item)t
GROUP BY db_shopname ")or die(mysqli_error($conn));

while($res=mysqli_fetch_array($sql)){


echo $res['db_shopname'];echo"&nbsp;";echo"(";echo $res['num'];echo")";echo"<br/>";

}

Answer

To pull the month out of the varchar field use:

MONTH(STR_TO_DATE(db_date ,'%Y/%m%d'))=MONTH(NOW())

Add this into where condition

EDIT:

I don't know its work for what you expecting but try it .

    select db_shopname, COUNT(db_shopname) as num 
    FROM
    (select tbl_order.db_shopname 
    from 
    tbl_order 
    where MONTH(STR_TO_DATE(`db_date` ,'%Y/%m%d'))=MONTH(NOW())
    union 
    all 
    select tbl_item.db_shopname 
    from tbl_item
    where MONTH(STR_TO_DATE(`db_date` ,'%Y/%m%d'))=MONTH(NOW()))t
    GROUP BY db_shopname