CODEWITHSUNDEEP
yii
user1334653
user1334653

Reputation:

Getting specific data from database

I have a table called Category with a few columns and I'm trying to get only a few out of my database.

So I've tried this:

$sql = 'SELECT uppercat AS id, COUNT(uppercat) AS uppercat FROM category GROUP BY uppercat;';
$d = Yii::app()->db->createCommand($sql)->query();

But I find the output strange. I was trying to do an array_shift but I get an error that this isn't an array. When I do a var_dump on $d:

object(CDbDataReader)[38]
  private '_statement' => 
    object(PDOStatement)[37]
      public 'queryString' => string 'SELECT uppercat AS id, COUNT(uppercat) AS uppercat FROM category GROUP BY uppercat;' (length=100)
  private '_closed' => boolean false
  private '_row' => null
  private '_index' => int -1
  private '_e' (CComponent) => null
  private '_m' (CComponent) => null

Ok.. then I did a foreach on $id:

array
  'id' => string '0' (length=1)
  'uppercat' => string '6' (length=1)
array
  'id' => string '3' (length=1)
  'uppercat' => string '2' (length=1)
array
  'id' => string '6' (length=1)
  'uppercat' => string '1' (length=1)
array
  'id' => string '7' (length=1)
  'uppercat' => string '2' (length=1)
array
  'id' => string '9' (length=1)
  'uppercat' => string '2' (length=1)

Then why do I get the message that $id isn't an array while it contains arrays?

Is there any other way on how to get some specific data out of my database and that I can then do an array_shift on them? I've also tried doing this with findAllBySql but then I can't reach my attribute for COUNT(uppercat) which is not in my model. I guess I'd have to add it to my model but I wouldn't like that because I need it just once.

Upvotes: 2

Views: 7680

Answers (2)

Owais Iqbal
Owais Iqbal

Reputation: 549

Try this.. its closed which you approched used...

$sql = 'SELECT uppercat AS id, COUNT(uppercat) AS uppercat FROM categorie GROUP BY uppercat;';
$d = Yii::app()->db->createCommand($sql)->queryAll();

and after that when you want to see your array so use,

print_r($d);

so you get the array..

it will work for you..

Upvotes: 3

adamors
adamors

Reputation: 2656

CDbCommand's query returns a CDbDataReader object for fetching the query result. Use queryAll instead, that returns an array of rows.

However, it is nicer if you do it with CDbCriteria (you need a model property for that, you're right).

It would look something like this, assuming the property is called countUppercat.

$criteria = new CDbCriteria;
$criteria->select = 'uppercat, COUNT(uppercat) AS countUppercat';
$criteria->group = 'countUppercat';
$models = CategorieModel::model()->findAll($criteria);

Upvotes: 4

Related Questions