Yii Query Not Working+Array Result

Yii 1.1 General Discussion
1

I have 3 models: Person , Event , Person_Event (which is a connection table , thus has fields like person_id and event_id)

I’m writing a mail action which gets the ids of selected events via ajax , and for each event , goes to the connection table , finds all the people who are participating , and sends them the info about their event.

I’v tested the code below in parts , and all is working except 2 things:

  1. The sql command is not working , though the query itself is right (checked in phpmyadmin)

  2. Are the rows being put into an array? because I’m doing a second “foreach” for people


public function actionSender()

	{

		 if(Yii::app()->request->getIsAjaxRequest())

        { 

                $checkedIds=$_GET['checked'];            //the selected events ids

                foreach($checkedIds as $id)

				{					

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

						->select('person_id')

						->from('tbl_person_event')					

						->where('event_id = :id', array(':id'=>$id))

						->queryRow();

						

						foreach($people as $pid)

						{

							$event=Event::model()->findByPk($id);           //for putting event's attributes in the thml table

							$address=Person::model()->findByPk($pid)->email;							

								$subject='not set yet';

							$message = '

								<html>

						                                      //the html table I'm sending to the people

								</html>

								';

								$headers  = 'MIME-Version: 1.0' . "\r\n";

								$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

			

							mail($address, $subject, $message,$headers);

						}

				}


        }

	}

Now , all the "people" in the app have my email address , but I get none.

What is wrong with the query? And how can I save people ids into an array?

Thanks , Mark.

2

I try to understand what you are doing with the second foreach. You will only fetch one row with queryRow. I guess it will return an associative array of table fields (not rows).

3

In the first foreach I take an Event’s id so hat I’ll be able to send its info to the people.

In second foreach I find all the people who are participating that specific event , and for each id I get the email address.

4

Yes but queryRow will return the last row from tbl_person_event. Maybe queryAll is what you’re looking for.




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

    ->select('person_id')

    ->from('tbl_person_event')                                      

     ->where('event_id = :id', array(':id'=>$id))

     ->queryAll();


  foreach($people as $row)

  {

       $event=Event::model()->findByPk($id);  //for putting event's attributes in the thml table

       $address=Person::model()->findByPk($row['person_id'])->email;                                                        

       $subject='not set yet';

     ...

  }

Not tested. Hope it leads you in the right direction :)

5 

Maybe queryAll is what you're looking for.

Tried that as well but with no success.

I re-wrote this function with the use of criteria instead of query builder. Not as elegant as this , but working.

6

Looking at your code in the first example you showed. It looked to me like your ’ or " might not have been in the right place because the part of the code where the “mail()” function is, is in green and that means its commented out right or contained in a string or something. Either way it is not the correct color the function call should be.

7

Also I would to mention, it was not just a case of the queryAll method, I think you missed the array part in the example Pecos showed you …




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

    ->select('person_id')

    ->from('tbl_person_event')                                      

    ->where('event_id = :id', array(':id'=>$id))

    ->queryAll();

I am sure $people in this instance will be a multi dimensional array and not a single dimensional array and you using it in your foreach loop like this …




foreach($people as $pid)

But it needs to be …




foreach($people as $row)

And then to get the ID you need to specify $row["person_id"] etc.

James.

8

I see. I’ll have to use it again in the future so I take this in mind.

Thanks, Mark.

9

Ok thanks.