Get data from two columns in database

I’m currently trying to run an API call that grabs all the emails + names and fire them off back into the system. I pass through the list_id through a button and it’ll insert all database values that match into the database

I can succesfully get this working with single values but when I try to insert all the values by grabbing the data and putting into an array it comes back NULL.

The line I think is causing an issue is the following




$emailArr[] = CMSubscribers::model()->findAll('email_address');

$nameArr[] = CMSubscribers::model()->findAll('forename');

The full code can be found below if any other suggestions want to be made


public function actionImportSubscribers($cm_list_id){


        //need to pass through cm_list_id instead 

        $cm_list_id = Yii::app()->getRequest()->getQuery('cm_list_id');


        $model =$this->loadModelList($cm_list_id);


        $listID = $model->cm_list->cm_list_id;


        //$email[] = array($model->email_address);

        $emailArr[] = CMSubscribers::model()->findAll('email_address');

        $nameArr[] = CMSubscribers::model()->findAll('forename');

        //$name[] = array($model->forename . ' ' . $model->surname);


        $customFieldArray = array();


        $addFieldsToList = array();

        foreach (array_combine($nameArr, $emailArr) as $name => $email) {

            $addFieldsToList[] = array('Name' => $names,'Email Address' => $emails,'CustomFields' => $customFieldArray);

        } 


        require_once 'protected/extensions/createsend-php-5.0.1/csrest_subscribers.php';


        $auth = array('api_key' => '');




        $wrap = new CS_REST_Subscribers($listID, $auth);

        $result = $wrap->import(array($addFieldsToList), false);


            if($result->was_successful()) {

                echo "Subscribed with results <pre>";

                var_dump($result->response);

            } else {

                echo 'Failed with code '.$result->http_status_code."\n<br /><pre>";

                var_dump($result->response);

                echo '</pre>';


                if($result->response->ResultData->TotalExistingSubscribers > 0) {

                    echo 'Updated '.$result->response->ResultData->TotalExistingSubscribers.' existing subscribers in the list';        

                } else if($result->response->ResultData->TotalNewSubscribers > 0) {

                    echo 'Added '.$result->response->ResultData->TotalNewSubscribers.' to the list';

                } else if(count($result->response->ResultData->DuplicateEmailsInSubmission) > 0) { 

                    echo $result->response->ResultData->DuplicateEmailsInSubmission.' were duplicated in the provided array.';

                }


                echo 'The following emails failed to import correctly.<pre>';

                var_dump($result->response->ResultData->FailureDetails);

            }

            echo '</pre>';

        // }


    }

FindAll expects to be given primary keys, not column names:

http://www.yiiframework.com/doc-2.0/yii-db-baseactiverecord.html#findAll()-detail

Does " or a set of column values. " not include any column not included in the PK?

Maybe it’s different in Yii 2.0 but I’m still on 1.1

I’m still not sure how else I Would grab everything else out of two columns, even while using DAO, but using $model->value I can always get the first value out.

I tried to use


	// an SQL with two placeholders ":username" and ":email"

	  	$sql="SELECT forename, email_address FROM tbl_cm_subscribers";

	  	$dataReader=$connection->createCommand($sql)->query();

	  	// bind the 1st column (forename) with the $username variable

	  	$dataReader->bindColumn(1,$names);

	  	// bind the 2nd column (email) with the $email variable

	  	$dataReader->bindColumn(2,$emails);

but that errors out too

Do you want to retrieve ALL emails and names you have in the database?

Yes. I’m currently limiting it by the cm_list_id and I want to grab all of them by that ID (which should be happening as I had it working as it was grabbing the first result and working correctly)

I need it to grab all the names / emails and pass them through.

Then something along those lines should do the trick:




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

    ->select('email_address, forename')

    ->from('CMSubscribers')

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

    ->queryAll();



So just checking

to bind these to variables it would be (assuming it works the same as bindcolumn)


->bindParam(':forename',$names)

->bindParam(':email_address',$emails)

I’ve done all this and it’s still coming out as ‘array_combine() expects parameter 1 to be array, null given’




$customFieldArray = array();

	  	

$addFieldsToList = array();

	foreach (array_combine($emails, $names) as $name => $email) {

	   	$addFieldsToList[] = array('EmailAddress' => $email,'Name' => $name,'CustomFields' => $customFieldArray);

	 } 

Yet we’re definitely grabbing the data correctly

I think you have your understanding of parameters in a database query mixed up.

Parameters are what goes IN to the query, NOT what’s coming OUT.

In the example I posted, "$rows" will contain all the emails and forenames. You have to iterate over that array to read the data.

Got it working after your last post,

Code can be found below, thanks for your help.




public function actionImportSubscribers($cm_list_id){


        //need to pass through cm_list_id instead 

        $cm_list_id = Yii::app()->getRequest()->getQuery('cm_list_id');


        $model =$this->loadModelList($cm_list_id);


        $listID = $model->cm_list->cm_list_id;


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

                             ->select('email_address, forename')

                             ->from('tbl_cm_subscribers')

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

                             ->queryAll();

        $emails=array();

        $names=array();

        foreach ($result as $row) {

            $emails[] = $row['email_address'];

            $names[] = $row['forename'];

        }


        require_once 'protected/extensions/createsend-php-5.0.1/csrest_subscribers.php';


        $auth = array('api_key' => '');


        foreach (array_combine($emails, $names) as $email => $name) {


            $wrap = new CS_REST_Subscribers($listID, $auth);


                $result = $wrap->import(array(

                    array(

                        'EmailAddress' => $email,

                        'Name' => $name,

                    ),

                ), false);

        }


            echo "Result of POST /api/v3.1/subscribers/{list id}/import.{format}\n<br />";

            if($result->was_successful()) {

                echo "Subscribed with results <pre>";

                var_dump($result->response);

            } else {

                echo 'Failed with code '.$result->http_status_code."\n<br /><pre>";

                var_dump($result->response);

                echo '</pre>';


                if($result->response->ResultData->TotalExistingSubscribers > 0) {

                    echo 'Updated '.$result->response->ResultData->TotalExistingSubscribers.' existing subscribers in the list';        

                } else if($result->response->ResultData->TotalNewSubscribers > 0) {

                    echo 'Added '.$result->response->ResultData->TotalNewSubscribers.' to the list';

                } else if(count($result->response->ResultData->DuplicateEmailsInSubmission) > 0) { 

                    echo $result->response->ResultData->DuplicateEmailsInSubmission.' were duplicated in the provided array.';

                }


                echo 'The following emails failed to import correctly.<pre>';

                var_dump($result->response->ResultData->FailureDetails);

            }

            echo '</pre>';

        // }


    }

Why do you make the detour with the "emails" and "names" arrays? Why not iterate over "result" straight-away?