How to write MySQL query in CakePHP format?

Question

i have faced in a problem. Can you please write this query in cakephp format

Table

id   from_id  to_id  is_active  message
---  -------  -----  ---------  -------
 1     1        8       1       Hello
 2     8        1       1       Yes
 3     1        8       1       How are you?
 4     1        8       1       Are you with me?

Code :

<?php
    $qry = mysql_query("SELECT * FROM messages WHERE (from_id='8' OR  to_id='1') AND (from_id='1' OR  to_id='8') AND id_active='1'"));
    $res = mysql_fetch_array($qry);
?>

Edit

I have written but getting null value

my code:

$condition = array('OR'=>array('Message.from_id'=>1,'Message.to_id'=>8),'OR'=>array('Message.from_id'=>8,'Message.to_id'=>1),'Message.is_active'=>1);
$message=$this->Message->find("all",array('conditions'=>$condition));
pr($message);

Output:

array
(

)

Update your question with some sample input in messages and what you expect to see as output. — Cylindric
– Cylindric, Commented May 22, 2014 at 10:25
Which records should your sample query return? Because if I run your sample SQL, it returns nothing too. Your base SQL is perhaps wrong? None of your samples have a to_id of from_id of 1, so your sample reduces down to from_id=8 AND to_id=8 sqlfiddle.com/#!2/faa1b0/2 — Cylindric
– Cylindric, Commented May 22, 2014 at 11:12

lamasgergo · Accepted Answer · 2014-05-24 16:01:02Z

1

Hi if you would like to list the conversation try out this:

 $this->Message->find('all' ,
            array(
                'conditions'=>array(
                    'AND'=>array(
                        'OR'=>array(
                        array('AND'=>array('from_id'=>1, 'to_id'=>'8')),
                        array('AND'=>array('from_id'=>8, 'to_id'=>'1'))
                        ),
                        'is_active'=>1
                    )
                )
            )

        );

The good query is:

SELECT Message.id, Message.from_id, Message.to_id, Message.is_active, Message.message FROM c23dev1.messagess AS Message WHERE ((((((from_id = 1) AND (to_id = 8))) OR (((from_id = 8) AND (to_id = 1))))) AND (is_active = '1'))

answered May 24, 2014 at 16:01

lamasgergo

888 bronze badges

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

Comments

Cylindric · Accepted Answer · 2014-05-22 11:44:27Z

0

I can recommend using Dogmatic69's converter. That gave this result for your query:

'conditions' => array(
    array(
        'or' => array(
            'from_id' => '8',
            'to_id' => '1',
        ),
    ),
    'id_active' => '1',
),

I'm not sure what your original SQL query is actually trying to return, but if you want all messages between user 7 and user 8, your query should be something like:

SELECT * 
FROM messages 
WHERE ((from_id='7' AND to_id='8') OR (from_id='8' AND to_id='7'))
AND is_active='1';

Here's a SQLFiddle example.

edited May 22, 2014 at 11:44

answered May 22, 2014 at 10:16

Cylindric

5,8945 gold badges48 silver badges70 bronze badges

5 Comments

Developer Over a year ago

It is showing that record which from_id=8 and to_id=1 but i need also from_id=1 and to_id=8

Cylindric Over a year ago

I don't understand. Your query specifically says to include "from_id=8 and to_id=1".

Cylindric Over a year ago

So does that mean my examples now work? Basically, are you trying to show all messages between users 7 and 8, or all messages sent to/from user 7 and all messages to/from user 8, regardless of whether they were sent to each other?

Developer Over a year ago

Yes @Cylindric i want to show the conversation between each other.

Cylindric Over a year ago

Then your original SQL is wrong. Use my example from my Answer. That gives all messages "FROM A TO B" and all messages "FROM B TO A".

Collectives™ on Stack Overflow

How to write MySQL query in CakePHP format?

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related