[FIXED] Relational mysql Table search without primary key in yii (calling a non integer field)

Issue

Table One:

ar_codes
----------
ar_id (primary key),
act_id,
ar_code,
status

Table Two:

act_req_recv
----------
rcv_id (primary key),
act_id,
rcv_person,
status

Now I need to find the ar_codefield value from ar_codes table for the rcv_person field of act_req_recv table. Both of the table has one common field which is act_id and is not a primary key for both of the table.

Now I can find it by normal mysql scriptlike bellow command, where $actId is carrying the value. How can I find this value in Yii?

SELECT ar_code FROM ar_codes WHERE act_id=$actId

I tried to find that with a function call from model. But as the field is not PK so the result is not coming.

public static function getAR_code($actId) {
    $ActCode = Yii::app()->db->createCommand()
            ->select('ar_code')
            ->from('{{ar_codes}}')
            ->where('act_id=' . (int) $actId)
            ->queryAll();
    return $ActCode;
}

Getting this error when the function run:

[error] [php] Array to string conversion (D:\Xampp\htdocs\framework\yii1.1.19\zii\widgets\CDetailView.php:240)

The cDetail View Code is:

array(
    'name' => 'actId',
    'type'=> 'raw',
    'value' => ActivityRecord::getAR_code($model->actId), 
    'htmlOptions' => array('style' => "text-align:left;"),
),

Solution

queryAll() returns array of rows, so it is actually array of arrays. Something like:

[
    ['ar_code' => 123],
]

If you want to query single value (for example 123), you should use queryScalar() - it will return value from first column of first row:

public static function getAR_code($actId) {
    return Yii::app()->db->createCommand()
            ->select('ar_code')
            ->from('{{ar_codes}}')
            ->where('act_id=' . (int) $actId)
            ->queryScalar();
}

Answered By - rob006