Assign Variables to SQL command result

Question

Succesfully solved: Working code is noted at the bottom of this Post.

I'm currently trying to run a SQL command to grab all the data out of a database table and send it over a API call using the variable names.

The problem I'm having is assigning the values of the fields under "$row" to separate variables so I can then place them in my foreach loop and send them all over to the API call. Can anyone enlighten me to what I'm doing wrong

I'm sure the line that I'm going wrong is my commandbuilder and then assigning the variables to the data inside row.

I feel like the problem line could be

while ($row = mysql_fetch_assoc()) {
    $emails = $row['email_address'];
    $names = $row['forename'];  
}

the full code is below.

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;
    $row = Yii::app()->db->createCommand()
    ->select('email_address, forename')
    ->from('tbl_cm_subscribers')
    ->where('cm_list_id=:id', array(':id' => $cm_list_id))
    ->queryAll();
    while ($row = mysql_fetch_assoc()) {
        $emails = $row['email_address'];
        $names = $row['forename'];  
    }
    $customFieldArray = array();
    $addFieldsToList = array();
    foreach (array_combine($emails, $names) as $name => $email) {
        $addFieldsToList[] = array('EmailAddress' => $email,'Name' => $name,'CustomFields' => $customFieldArray);
    } 
    $auth = array('api_key' => '');
    $wrap = new CS_REST_Subscribers($listID, $auth);
$result = $wrap->import(array($addFieldsToList), false);

Working code is below

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>';
        // }

    }

Try change array(':id' => $cm_list_id) to array('id' => $cm_list_id) — amow
– amow, Commented Sep 29, 2016 at 9:41
That part works fine I think, I believe the problem is while ($row = mysql_fetch_assoc()) { $emails = $row['email_address']; $names = $row['forename']; } but I can't find any other way to pass a result through — Alex
– Alex, Commented Sep 29, 2016 at 9:51
$emails []= $row['email_address'];$names []= $row['forename']; ? — amow
– amow, Commented Sep 29, 2016 at 10:12
mysql_query() expects parameter 1 to be string, array given think I must be missing something with the way the result is being passed from row but can't see anything in the documentation. — Alex
– Alex, Commented Sep 29, 2016 at 10:18

ban17 · Accepted Answer · 2016-09-29 11:45:31Z

1

I don't know if this solving your problem but you have few errors there.
mysql_fetch_assoc() requires param , resource returned from mysql_query function.

In part where you creating $emails and $names variables you doing that like if you trying to create array but you will get always single value in that way how you have done it. This is example if you will use mysql_fetch_assoc, you can't combine queryAll() with mysql_fetch_assoc

$emails=array();
$names=array();
$result=mysql_query("SELECT email_address, forename FROM tbl_cm_subscribers where cm_list_id='$cm_list_id'");
while ($row = mysql_fetch_assoc($result)) {
    $emails[] = $row['email_address'];
    $names[]  = $row['forename'];
}

queryAll() method returns array, I don't know Yii but I suppose this is what you need to do

$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'];
}

Or if you don't need array of results then use $emails and $names without []

answered Sep 29, 2016 at 11:45

ban17

6348 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

Awesome cheers, I had to change it upa little and add a loop around my API send foreach (array_combine($emails, $names) as $email => $name) { } Updating OP with full answer.

Collectives™ on Stack Overflow

Assign Variables to SQL command result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related