Thank you! Your feedback has been delivered
Thank you! Your feedback has been sent

PHP skript for importing array into mySQL database

Hi codersclan, this time I need your support for a PHP job.

On http://kundenwunder.com/kunden/_test/index1.php is a result of this api-call http://api.cleverreach.com/soap/doc/5.0/CleverReach/Receiver/_complex.receiver.php.html#functionreceiverGetPage

The Code for this is here:

    <?php echo "<pre>";
$apiKey = "###it's secret###";
$wsdl_url="http://api.cleverreach.com/soap/interface_v5.1.php?wsdl";
$listId = "36885";

$api = new SoapClient($wsdl_url);

     $page = 0;
     do{
        $filter = array( "page"=>$page++,
                         "filter"=>"all"
                     );
        $return = $api->receiverGetPage($apiKey, $listId, $filter);
        if($return->status=="SUCCESS"){        //getting results
            var_dump($return->data);
        }else{                                //call failed
            var_dump($return->message);                //show error
        }
        if($page>=1000) break;    //demo ... skip after 5 pages
     }while($return->data);
?>

Where I'll need your support:

1) This data should be added (UPDATE) to a mysql database. The table has the name 'weask', the fields have the same names like the ?variables? in the array...

I really appreciate your help :-)

User Gravatar

Christian

Posted Jun 8 2014 8:22 UTC

$50



21 Replies


you wrote added/update. Do you like to insert all the data you get from API call to MySQL DB?

Your DB field name is in Uppercase as the response or lowercase?

User Gravatar

rashidul04

Posted Jun 8 2014 9:50 UTC

Here is full code to insert data into DB.

<?php 
    echo "<pre>";
    $apiKey = "###it's secret###";
    $wsdl_url="http://api.cleverreach.com/soap/interface_v5.1.php?wsdl";
    $listId = "36885";

    $api = new SoapClient($wsdl_url);

         $page = 0;
         do{
            $filter = array( "page"=>$page++,
                             "filter"=>"all"
                         );
            $return = $api->receiverGetPage($apiKey, $listId, $filter);
            if($return->status=="SUCCESS"){        //getting results
                insertToDb($return->data);
            }else{                                //call failed
                var_dump($return->message);                //show error
            }
            if($page>=1000) break;    //demo ... skip after 5 pages
         }while($return->data);

   function insertToDb($data){
        // replace HOSTNAME, USERNAME, PASSWORD, DBNAME with your creadentials
        $mysqli = new mysqli("HOSTNAME", "USERNAME", "PASSWORD", "DBNAME");

        /* check connection */
        if ($mysqli->connect_errno) {
            printf("Connect failed: %sn", $mysqli->connect_error);
            exit();
        }

        foreach($data as $user){
            $fields = $values = '';
            $attrs = $user->attributes;
            $i = 0;
            foreach($attrs as $attr){
                $i++;
                if($i != 1){
                    $fields .= ',';
                    $values .= ',';
                }
                $fields .= strtolower(str_replace(array('{', '}'), '', $attr->variable)); //or strtolower($attr->key)
                $values .= strtolower($attr->value);
            }

            /* insert the user data into DB */
            if ($mysqli->query("INSERT INTO weask ($fields) VALUES($values)") === TRUE) {
                printf("Data successfully inserted.n");
            }
        }

        $mysqli->close();
    }
?>
User Gravatar

rashidul04

Posted Jun 8 2014 10:25 UTC

or you can use as below (portion of above's code can be replace with the below code)

foreach($data as $user){
        $fields = $values = array();
        $attrs = $user->attributes;

        foreach($attrs as $attr){

            $fields[] = strtolower(str_replace(array('{', '}'), '', $attr->variable));
            $values[] = strtolower($attr->value);
        }

        $fields = implode(',', $fields);
        $values = implode(',', $values);

        /* insert the user data into DB */
        if ($mysqli->query("INSERT INTO weask ($fields) VALUES($values)") === TRUE) {
            printf("Data successfully inserted.n");
        }
    }
User Gravatar

rashidul04

Posted Jun 8 2014 10:34 UTC

Hi rashidul04, thank you for your quick reply. I'll test now... Best regards.

User Gravatar

Christian

Posted Jun 9 2014 2:55 UTC

Hi rashidul04, obviously there is an error - either I did it - or the script is buggy. I get the following error:

string(14) "data not found"

On this page: https://kundenwunder.com/kunden/weask/import.php

My script now is as follows:

<?php 
    echo "<pre>";
    $apiKey = "b30d1b6c4d97ee032c3aeac0866f39ca-1";
    $wsdl_url="http://api.cleverreach.com/soap/interface_v5.1.php?wsdl";
    $listId = "36885";

    $api = new SoapClient($wsdl_url);

         $page = 0;
         do{
            $filter = array( "page"=>$page++,
                             "filter"=>"all"
                         );
            $return = $api->receiverGetPage($apiKey, $listId, $filter);
            if($return->status=="SUCCESS"){        //getting results
                insertToDb($return->data);
            }else{                                //call failed
                var_dump($return->message);                //show error
            }
            if($page>=1000) break;    //demo ... skip after 5 pages
         }while($return->data);

   function insertToDb($data){
        // replace HOSTNAME, USERNAME, PASSWORD, DBNAME with your creadentials
        $mysqli = new mysqli("secret", "secret", "secret", "secret");

        /* check connection */
        if ($mysqli->connect_errno) {
            printf("Connect failed: %sn", $mysqli->connect_error);
            exit();
        }

        foreach($data as $user){
            $fields = $values = '';
            $attrs = $user->attributes;
            $i = 0;
            foreach($attrs as $attr){
                $i++;
                if($i != 1){
                    $fields .= ',';
                    $values .= ',';
                }
                $fields .= strtolower(str_replace(array('{', '}'), '', $attr->variable)); //or strtolower($attr->key)
                $values .= strtolower($attr->value);
            }

            /* insert the user data into DB */
            if ($mysqli->query("INSERT INTO weask ($fields) VALUES($values)") === TRUE) {
                printf("Data successfully inserted.n");
            }
        }

        $mysqli->close();
    }
?>

My database is as follows:

CREATE TABLE `weask` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` text,
  `registered` text,
  `activated` text,
  `deactivated` text,
  `active` text,
  `source` text,
  `quality` text,
  `city` text,
  `company` text,
  `country` text,
  `firstname` text,
  `lastname` text,
  `nachricht` text,
  `salutation` text,
  `street` text,
  `telefon` text,
  `title` text,
  `zip` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I can access the DB, as well i get all data from the api by typing var_dump instead of isertToDb on line 16 of your php script.

Could you please give me a hint?

Best regards, Chris.

User Gravatar

Christian

Posted Jun 9 2014 3:23 UTC

Thanks for the details. I'll get back to you shortly.

User Gravatar

rashidul04

Posted Jun 9 2014 3:49 UTC

Would you please change $values .= strtolower($attr->value); to

$values .= ($attr->value ? $attr->value : '');

because some don't have any value.

User Gravatar

rashidul04

Posted Jun 9 2014 3:58 UTC

Hi rashidul04, unfortunately it's still not yet fixed...

User Gravatar

Christian

Posted Jun 9 2014 4:06 UTC

I'll do a real test and will notify you

User Gravatar

rashidul04

Posted Jun 9 2014 4:10 UTC

sorry my bad.

Please use this $values .= ($attr->value ? "'".$mysqli->real_escape_string($attr->value)."'" : 'NULL');

User Gravatar

rashidul04

Posted Jun 9 2014 5:04 UTC

You probably changed list id or api key, so i didn't had oppertunity to test it on real data, but this should work just fine. This is made for your DB table structure.

Just copy-past, change configuration values and run.

Let me know if you have any problems.

<?php
/**
 * Configuration variables
 */

// API
$apiKey     = "b30d1b6c4d97ee032c3aeac0866f39ca-1";
$wsdl_url   = "http://api.cleverreach.com/soap/interface_v5.1.php?wsdl";
$listId     = "36885";
$api        = new SoapClient($wsdl_url);
$page       = 0;

// DB
$host       = 'HOSTNAME';
$user       = 'USERNAME';
$password   = 'PASSWORD';
$db         = 'DBNAME'; 

/***************************************/

// Get data
do
{
    $filter = array(
                    "page"      => $page++, 
                    "filter"    => "all"
                    );

    $return = $api->receiverGetPage($apiKey, $listId, $filter);

    if($return->status == "SUCCESS")
    {
        // Getting results
        insertToDb($return->data);
    }
    else
    {   
        // Call failed
        // Show error
        var_dump($return->message);
    }

    // Demo ... skip after 5 pages. Uncomment if not in demo
    if($page>=5) break;
}while($return->data);


/**
 * Function to add users to database
 */

function insertToDb($data)
{
    // Replace HOSTNAME, USERNAME, PASSWORD, DBNAME with your creadentials
    $mysqli = new mysqli($host, $user, $password, $db);

    // Check connection
    if ($mysqli->connect_errno) 
    {
        printf("Connect failed: %sn", $mysqli->connect_error);
        exit();
    }

    // If we have data
    if(count($data)>0)
    {
        foreach($data as $user)
        {
            $fields = array();
            $values = array();

            if(count($user)>0)
            {
                // Regular fields
                foreach ($user as $fieldName => $fieldValue)
                {
                    if($fieldName !== 'attributes')
                    {
                        $fields[] = $fieldName;
                        $values[] = $fieldValue;            
                    }
                }

                // Attributes
                $attrs  = $user->attributes;
                if(count($attrs)>0)
                {
                    foreach($attrs as $attr)
                    {
                        $fields[] = $attr->key;
                        $values[] = $attr->value;
                    }
                }

                // Insert the user data into DB
                if($mysqli->query("INSERT INTO weask (`".join('`, `', $fields)."`) VALUES(`".join('`, `', $values)."`)") === TRUE) 
                {
                    echo 'User with ID: <strong>'.$user->id.'</strong> added to DB.<br />';
                }
            }
        }
    }
    else
    {
        echo 'No users found.';
    }

    // Close DB connection
    $mysqli->close();
}
?>
User Gravatar

renekorss

Posted Jun 9 2014 5:43 UTC

Hi renekorss, your skript outputs the same failure as before... Sorry...

User Gravatar

Christian

Posted Jun 9 2014 6:18 UTC

Hi rashidul04, your fix almost works :-) Import runs. And all data from the sub-array attributes are imported now, but the main data like email are imported as NULL

Could you please fix this again - then the task should be done. Thanks & Cheers, Christian.

User Gravatar

Christian

Posted Jun 9 2014 6:21 UTC

Hi,

Did you check mine? Here is the updated version. I extracted mysql connection as it doesn't connect every time, take care of some sort of SQL injection and add the regular fields.

    <?php
    $apiKey = "b30d1b6c4d97ee032c3aeac0866f39ca-1";
    $wsdl_url="http://api.cleverreach.com/soap/interface_v5.1.php?wsdl";
    $listId = "36885";
    $api = new SoapClient($wsdl_url);

    // replace HOSTNAME, USERNAME, PASSWORD, DBNAME with your creadentials
    $mysqli = new mysqli("HOSTNAME", "USERNAME", "PASSWORD", "DBNAME");
    /* check connection */
    if ($mysqli->connect_errno) {
        printf("Connect failed: %sn", $mysqli->connect_error);
        exit();
    }

    $page = 0;
    do{
        $filter = array( "page"=>$page++,
                        "filter"=>"all"
                    );
        $return = $api->receiverGetPage($apiKey, $listId, $filter);
                if($return->status=="SUCCESS"){        //getting results
            insertToDb($return->data, $mysqli);
                    // echo "<pre>";
                    // print_r($return->data);
                                        }else{                                //call failed
                            // var_dump($return->message);                //show error
        }
            if($page>=1000) break;    //demo ... skip after 5 pages
    }while($return->data);

    $mysqli->close();

    function insertToDb($data, $mysqli){
        foreach($data as $user){
            $fields = $values = array();
            $attrs = $user->attributes;

            foreach ($user as $fieldName => $fieldValue){
                if($fieldName !== 'attributes')
                {
                    $fields[] = $fieldName;
                    $values[] = ($fieldValue ? "'".$mysqli->real_escape_string($fieldValue)."'" : 'NULL');
                }
            }

            foreach($attrs as $attr){

                $fields[] = strtolower(str_replace(array('{', '}'), '', $attr->variable));
                $values[] = ($attr->value ? "'".$mysqli->real_escape_string($attr->value)."'" : 'NULL');
            }

            $fields = implode(',', $fields);
            $values = implode(',', $values);

            if ($mysqli->query("INSERT INTO weask ($fields) VALUES($values)") === TRUE) {
                printf("Data successfully inserted.n");
            }
            else{
                printf("Data unsuccessfull.n");
            }
        }

    }
?>
User Gravatar

rashidul04

Posted Jun 9 2014 6:21 UTC

Solution

This didn't solve your task? Get your own custom solution.

You mean this error?

string(14) "data not found"

Or some other error?

This is from API, check for API key and list id. Maybe you could give me some example API credentials?

User Gravatar

renekorss

Posted Jun 9 2014 6:23 UTC

Hi Christian,

I added that support. Please use the latest version above and I checked it works fine.

User Gravatar

rashidul04

Posted Jun 9 2014 6:24 UTC

@renekorss, yes it was still this error - thank your for your support.

But rashidul04 has fixed it first. Thank you so much rashidul04. I really like your awesome work... You've fixed my issue fast and in very good quality!

Thank you for all your support codersclanners (y) Will be back soon with some new tasks.

Cheers :-)

User Gravatar

Christian

Posted Jun 9 2014 6:36 UTC

Thank you Christian. I appreciate your comment.

User Gravatar

rashidul04

Posted Jun 9 2014 6:39 UTC

Yes, I got that error too first time. But tested exact same code again and it worked fine now. Can you test my code again, just to know if it really worked.

User Gravatar

renekorss

Posted Jun 9 2014 6:53 UTC

Hi renekorss, unfortunately I have to set the database connection direct in the code - if set in the Settings on top they don't take effect. And then the error string(14) "data not found" still exists. I'm sorry.

User Gravatar

Christian

Posted Jun 9 2014 7:10 UTC

Oh. Right. Stupid mistake, thank you for answering.

User Gravatar

renekorss

Posted Jun 9 2014 7:17 UTC

Add a reply

By posting a reply on CodersClan you agree to our Terms & Conditions