Selecting data from database in Cakephp doesn't work

Question

I want to select data like this SQL query:

SELECT discounts.product_id, products.product_name,
sum(products.product_price - discounts.product_discount) as total_Amount,
count(orders.order_id) as total_Number
FROM products 
inner join discounts on products.product_id = discounts.product_id
inner join orders on discounts.discount_id = orders.discount_id

where discounts.start_time >= @from and discounts.end_time <= @to 

group by discounts.product_id,products.product_name

@from and @to are values that I will pass to.

Here is what I did in Controller:

$from='27 November 2012';
        //$this->layout = 'customer-backend';
        $this->Order->recursive=-1;
        $this->Order->virtualFields['benefit']='SUM(Product.product_price - Discount.product_discount)';
        $this->Order->virtualFields['number']='COUNT(Order.order_id)';
        $option['joins'] = array(
            array('table'=>'discounts',
                'alias'=>'Discount',
                'type'=>'INNER',
                'conditions'=>array(
                    'Order.discount_id = Discount.discount_id',
                )
            ),
            array('table'=>'products',
                'alias'=>'Product',
                'type'=>'INNER',
                'conditions'=>array(
                    'Discount.product_id = Product.product_id'
                )
            )
        );
        $option['fields']= array('Discount.product_id','Product.product_name','benefit','number');
        $option['conditions']=array('Discount.start_time >='=>$from, 'Discount.end_time <= ' => $to);   //I guess it's wrong here
        $option['group'] = array('Discount.product_id','Product.product_name');
        //$option['limit']=20;
        $products = $this->Order->find('all',$option);
        $this->set('products',$products);

I think this line is wrong:

$option['conditions']=array('Discount.start_time >='=>$from, 'Discount.end_time <= ' => $to);

When I deleted it, it worked. But I dont know how to fix it. Please help me.

On this one too - if you're going to be using MySQL, learn how to debug it. What query is CakePHP generating? If you show that, it will be easy to solve. — Dave
– Dave, Commented Dec 3, 2013 at 2:53
I can't, but my good buddy Google would be glad to help. He has lots of friends who have asked that same question many many many times. ;) (not trying to be an ass - just... it's a very common question, and this isn't the place for that) — Dave
– Dave, Commented Dec 3, 2013 at 3:52

Guillermo Mansilla · Accepted Answer · 2013-12-03 04:00:23Z

1

Not CakePHP's problem but they way MYSQL is receiving your query. Try formatting the dates.

$from = date('Y-m-d', strtotime($from));

answered Dec 3, 2013 at 4:00

Guillermo Mansilla

3,8842 gold badges32 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tung Pham Over a year ago

thank you for your answer. The date is in format 'dd month yyyy'. Eg 08 November 2013. When I type it directly in mySQL it worked.

Guillermo Mansilla Over a year ago

what is the column type? datetime? date?

Collectives™ on Stack Overflow

Selecting data from database in Cakephp doesn't work

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related