[SOLVED]how to convert to active record?

Yii 1.1 General Discussion
1

hi, i need help, how to convert this to active record ?




$sql = "SELECT CompanyName FROM Wsmembersdetails WHERE MemberShipID =

                   (SELECT IntroducingMemberID FROM Wsmembers WHERE MemberShipID ='".Yii::app()->user->id."')

                   AND CompanyID = (SELECT compid FROM Wsmembers WHERE MemberShipID='".Yii::app()->user->id."')";

and it should show the result the query at the view file ?

2

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#findAllBySql-detail

3

I did this




 $sql = "SELECT CompanyName FROM Wsmembersdetails WHERE MemberShipID =

                   (SELECT IntroducingMemberID FROM Wsmembers WHERE MemberShipID =':id')

                   AND CompanyID = (SELECT compid FROM Wsmembers WHERE MemberShipID='id')";


print_r(Wsmembersdetails::model()->findAllBySql($sql,array(':id'=>Yii::app()->user->id)));

it returns

[code]

Array()

why?

when I echoed out Yii::app()->user->id, it prints the correct userid

4

isn’t it a typo? ::)

5

I already corrected before you posted a new reply, but still I get a null, why?

but when I tried that sql query in my sql client manually, it does return the exact value am asking for

this is the exact sql query




SELECT CompanyName FROM wsmembersdetails WHERE MemberShipID = (SELECT IntroducingMemberID FROM wsmembers WHERE MemberShipID ='3') AND CompanyID = (SELECT compid FROM wsmembers WHERE MemberShipID='3');

I just replaced the number 3 with Yii::app()->user->id

6

Does it work correctly without params binding? Also there is no need in quotes in this statement:




MemberShipID = :id
7

when i do this




            $sql = "SELECT CompanyName FROM Wsmembersdetails WHERE MemberShipID =

                   (SELECT IntroducingMemberID FROM Wsmembers WHERE MemberShipID ='".Yii::app()->user->id."')

                   AND CompanyID = (SELECT compid FROM Wsmembers WHERE MemberShipID='".Yii::app()->user->id."')";

    echo "<pre>",print_r(Wsmembersdetails::model()->findAllBySql($sql)),"</pre>";

it prints all the crap out of mysql, but I can see the value am asking for, and suggestions/tips how to fix

this? , all i need is a single value not an array of mysql intestines XD

8

Hi Sasori,

The problem is that you are using findAllBySql because, as it states on the API, it returns an array. Use findBySql instead and tell us what happens.

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#findBySql-detail

Nevertheless, if you wish a full AR, shouldnt you SELECT all values of that model?

9

solved, additional table was asked to be added, change of routine

10