1

I have table in magento database

 CREATE TABLE `topup` (
  `topup_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `msisdn` varchar(20) NOT NULL,
  `amount` int(11) NOT NULL,
  `status` varchar(256) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `ipn_response` text NOT NULL,
  PRIMARY KEY (`topup_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want magento collection SELECT count(*) FROM topup AS main_table

I want total count base on created_at in 1 day

and

total count in week

week start from Monday.

  • you want a magento collection or a simple select that groups everything by date? – Marius Dec 5 '16 at 13:10
  • just want count create_at whole day e.g. WHERE (created_at >= '2016-12-05 00:00:00' AND created_at <= '2016-12-05 18:08:39') and in total count in week e.g. form monday to now week of day – Arshad Muhammad Dec 5 '16 at 13:12
  • @ArshadMuhammad you can get count WHERE created_at LIKE '2016-12-05 %' for current day – Qaisar Satti Dec 5 '16 at 13:15
  • @QaisarSatti got and for whole week how can i do week start from like monday – Arshad Muhammad Dec 5 '16 at 13:19
  • @ArshadMuhammad added the answer check it. – Qaisar Satti Dec 5 '16 at 13:24
2

It is rough idea hope work for you

For current day

WHERE created_at LIKE '2016-12-05 %'

For weekly

$lastmonday=date('Y-m-d', ("last Monday")).' 00:00:00';
$currentday=date('Y-m-d').' 23:59:00';

 WHERE (created_at >= $lastmonday AND created_at <= $currentday)
2

Try this for grouping my day.

SELECT 
    count(*)
FROM topup 
WHERE 
    DATE(created_at) = '2016-12-05'

Or for today you can replace '2016-12-05' with DATE(NOW())

so you get:

SELECT 
    count(*)
FROM topup 
WHERE 
    DATE(created_at) = DATE(NOW())

and for records this week

SELECT 
    count(*) 
FROM topup 
WHERE 
    WEEKOFYEAR(created_at)=WEEKOFYEAR(NOW());
  • e.g. week start from today monday how can i do – Arshad Muhammad Dec 5 '16 at 13:23
  • I don't understand. Can't you just calculate in php when the last monday was and run a query with Where date(created_at) >= 'date you calculate in php' – Marius Dec 5 '16 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.