Cdbcommand Returns Wrong Results

Yii 1.1 General Discussion
1

Ok, this is probably again something I’m messing up, but I can’t figure this one out. For some reason this code doesn’t function as expected:




$sql = "SELECT id, class_id, lang FROM tbl_top_class WHERE class_name = :string";

$cmd = Yii::app()->db->createCommand($sql);

$data[':string'] = "sad";

$result = $cmd->queryAll($data);

$db = new PDO(...);

$stmt = $db->prepare($sql);

$stmt->execute($data);

$result2 = $stmt->fetchAll();

print_r($result);

echo "<br>";

print_r($result2);

I would expect that $result and $result2 are identical, but they are not. This is what the script outputs:




Array ( [0] => Array ( [id] => 1 [class_id] => 0 [lang] => en-us ) ) 

Array ( )

And here’s the db structure & data:




--

-- Table structure for table `tbl_top_class`

--


CREATE TABLE `tbl_top_class` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `lang` varchar(5) NOT NULL,

  `class_name` varchar(100) NOT NULL,

  `class_id` bigint(20) unsigned NOT NULL,

  `description` text,

  PRIMARY KEY (`id`),

  KEY `lang` (`lang`),

  KEY `class_name` (`class_name`),

  KEY `class_id` (`class_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


--

-- Dumping data for table `tbl_top_class`

--


INSERT INTO `tbl_top_class` (`id`, `lang`, `class_name`, `class_id`, `description`) VALUES

(1, 'en-us', 'discard', 0, 'Non-classifiable');

Both connections are to the exact same db and table, with exact same credentials. Why am I getting different results?

2 



$result= Yii::app()->db->createCommand()

    ->select('id, class_id, lang')

    ->from('tbl_top_class')

    ->where('class_name=:cn', array(':cn'=>'sad'))

    ->queryAll();


print_r($result);
3

You’re right, that works! And the same solution worked too when I ran into the same problem with another query. But why? Why wont a perfectly valid query work, until it’s built with the query builder?

4

You must be connecting to some other database when creating your own PDO instance.

5

Nope, there’s no other database to connect to. Everything is the exact same, db, table, user, password, query. CDbCommand must be doing something to the query, and I’d really like to know where it is and where it isn’t safe to use manually built queries.

6

You call queryAll() wrong. The first argument should be boolean and params go into the second argument. I caught myself doing that a few times too.