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.userid,C.cid,U.username,U.email FROM users U,conversation C, conversation_reply R WHERE CASE

WHEN C.userone = '$userid' THEN C.usertwo = U.userid WHEN C.usertwo = '$userid' THEN C.userone= U.userid END

AND C.cid=R.cidfk AND (C.userone ='$userid' OR C.usertwo ='$userid') ORDER BY C.cid DESC";

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

where is the my fault ?

thanks for all

(its working on phpmyadmin)



84.5k

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.userone ='$userid' OR C.usertwo ='$userid') ORDER BY C.c_id DESC



24.7k

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($userid) { // A raw SQL statement $sql = "SELECT U.userid,C.cid,U.username,U.email FROM users U,conversation C, conversationreply R WHERE CASE

WHEN C.userone = '$userid' THEN C.usertwo = U.userid WHEN C.usertwo = '$userid' THEN C.userone= U.userid END

AND C.cid=R.cidfk AND (C.userone ='$userid' OR C.usertwo ='$userid') ORDER BY C.cid 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($userid); echo "<pre>"; printr($conversations);