when casse problem on phql

hi all,

im making a conversation script but iam facing a problem on when expr.

my code:

$phql = "SELECT U.user_id,C.c_id,U.username,U.email FROM users U,conversation C, conversation_reply R WHERE CASE

WHEN C.user_one = '$user_id' THEN C.user_two = U.user_id WHEN C.user_two = '$user_id' THEN C.user_one= U.user_id END

AND C.c_id=R.c_id_fk AND (C.user_one ='$user_id' OR C.user_two ='$user_id') ORDER BY C.c_id DESC";

$conversations = $this->modelsManager->executeQuery($phql);

where is the my fault ?

thanks for all

(its working on phpmyadmin)



90.6k

PHQL does not support case/when/then clauses, you can use a Raw Query:

http://docs.phalconphp.com/en/latest/reference/phql.html#using-raw-sql

Also, passing the query parameters as part of the SQL statement could lead to SQL injections: (C.user_one ='$user_id' OR C.user_two ='$user_id') ORDER BY C.c_id DESC



30.5k

Phalcon\Mvc\Model\Resultset\Simple Object ( [_type:protected] => 0 [_result:protected] => Phalcon\Db\Result\Pdo Object ( [_connection:protected] => Phalcon\Db\Adapter\Pdo\Mysql Object ( [_eventsManager:protected] => [_descriptor:protected] => Array ( [host] => 127.0.0.1 [username] => root [password] => [dbname] => popsbuy [charset] => utf8 )

                [_dialectType:protected] => mysql
                [_type:protected] => mysql
                [_dialect:protected] => Phalcon\Db\Dialect\Mysql Object
                    (
                        [_escapeChar:protected] => `
                    )

                [_connectionId:protected] => 1
                [_sqlStatement:protected] => 
                [_sqlVariables:protected] => 
                [_sqlBindTypes:protected] => 
                [_pdo:protected] => PDO 

etc after that code

public static function getUserMessage($user_id) { // A raw SQL statement $sql = "SELECT U.user_id,C.c_id,U.username,U.email FROM users U,conversation C, conversation_reply R WHERE CASE

WHEN C.user_one = '$user_id' THEN C.user_two = U.user_id WHEN C.user_two = '$user_id' THEN C.user_one= U.user_id END

AND C.c_id=R.c_id_fk AND (C.user_one ='$user_id' OR C.user_two ='$user_id') ORDER BY C.c_id DESC LIMIT 10";

    // Base model
    $conversation = new Conversation();

    // Execute the query
    return new Resultset(null, $conversation, $conversation->getReadConnection()->query($sql));
}

and on the page:

$conversations = Conversation::getUserMessage($user_id); echo "<pre>"; print_r($conversations);