0

0I have an array ($dataArray) in the following format which is created from a csv file:

The CSV is processed as follows:

function csvstring_to_array($string, $separatorChar = ',', $enclosureChar = '"', $newlineChar = "\n") {
    // @author: Klemen Nagode
    $array = array();
    $size = strlen($string);
    $columnIndex = 0;
    $rowIndex = 0;
    $fieldValue="";
    $isEnclosured = false;
    for($i=0; $i<$size;$i++) {

        $char = $string{$i};
        $addChar = "";

        if($isEnclosured) {
            if($char==$enclosureChar) {

                if($i+1<$size && $string{$i+1}==$enclosureChar){
                    // escaped char
                    $addChar=$char;
                    $i++; // dont check next char
                }else{
                    $isEnclosured = false;
                }
            }else {
                $addChar=$char;
            }
        }else {
            if($char==$enclosureChar) {
                $isEnclosured = true;
            }else {

                if($char==$separatorChar) {

                    $array[$rowIndex][$columnIndex] = $fieldValue;
                    $fieldValue="";

                    $columnIndex++;
                }elseif($char==$newlineChar) {
                    echo $char;
                    $array[$rowIndex][$columnIndex] = $fieldValue;
                    $fieldValue="";
                    $columnIndex=0;
                    $rowIndex++;
                }else {
                    $addChar=$char;
                }
            }
        }
        if($addChar!=""){
            $fieldValue.=$addChar;

        }
    }

    if($fieldValue) { // save last field
        $array[$rowIndex][$columnIndex] = $fieldValue;
    }
    return $array;
}

$dataArray = csvstring_to_array( file_get_contents("ACS_MONTHLY_DUES - Jun 2020.csv"));
echo '<pre>';
print_r($dataArray);
echo '</pre>';


    Array
    (
        [0] => Array
            (
                [0] =>  REGION
                [1] =>  CENTER
                [2] =>  SUBSCRIBER CODE
                [3] =>  SUBSCRIBER_ID
                [4] =>  SUBSCRIBERNAME
                [5] =>  SUBSCRIBERPHONE
                [6] =>  BOUQ
                [7] =>  STB_NOS
                [8] => LAST MONTH PAYMENT
                [9] =>  AMOUNT_DUE
                [10] =>  MINIMUM_RECHARGE
                [11] =>  TOTAL_PAYABLE
                [12] =>  EXPIRY_DATE
                [13] => PAY_AMOUNT
                [14] => CURRENT BALANCE
            )

        [1] => Array
            (
                [0] => XXX
                [1] => XXX
                [2] => UK91002970
                [3] => 61610070
                [4] => XXXXX
                [5] => XXXXXX
                [6] => UK FTA-1
                [7] => 1
                [8] => 0
                [9] => 0
                [10] => 221
                [11] => 221
                [12] => 08-06-2020
                [13] => 0
                [14] => 0
            )


  [2] => Array
    (
        [0] => XXXX
        [1] => XXXXX
        [2] => UK91002971
        [3] => 61610217
        [4] => XXXXXXX
        [5] => XXXXXXX
        [6] => UK FTA-1
        [7] => 1
        [8] => 0
        [9] => 8
        [10] => 243
        [11] => 251
        [12] => 06-06-2020
        [13] => 0
        [14] => 0
    )

)

I need to insert it in my mysql DB using PDO. I tried the following and it is not working:

include_once('inc/connstring.inc.php');

$stmt = $conn->prepare("INSERT INTO customer(subscriber_code, subscriber_id, region,center,subscriber_name,subscriber_phone,package,stb_nos,last_month_payment,amount_due,minimum_recharge,total_payable,expiry_date,pay_amount,current_balance)
VALUES(:subscribercode, :subscriberid, :region,:center,:subscribername,:subscriberphone,:package,:stbnos,:lastmonthpayment,:amountdue,:minimumrecharge,:totalpayable,:expirydate,:payamount,:currentbalance)");


foreach($dataArray as $row) {
$stmt->execute(array(
':subscribercode' =>  $row['SUBSCRIBER CODE'], 
':subscriberid' => $row['SUBSCRIBER_ID'],
':region' => $row['REGION'],
':center' => $row['CENTER'],
':subscribername' => $row['SUBSCRIBERNAME'],
':subscriberphone' => $row['SUBSCRIBERPHONE'],
':package' => $row['BOUQ'],
':stbnos' => $row['STB_NOS'],
':lastmonthpayment' => $row['LAST MONTH PAYMENT'],
':amountdue' => $row['AMOUNT_DUE'],
':minimumrecharge' =>$row['MINIMUM_RECHARGE'],
':totalpayable' => $row['TOTAL_PAYABLE'],
':expirydate' => $row['EXPIRY_DATE'],
':payamount' => $row['PAY_AMOUNT'],
':currentbalance' => $row['CURRENT BALANCE']));
}

It is not working. Invalid argument supplied for foreach()

What am i doing wrong?? Requesting help from php experts...

Thanks in advance.

update: Solved myself. Changed code to convert CSV to an associative array and now everything works fine.

Array
(
    [0] => Array
        (
            [REGION] => XXX
            [CENTER] => XXXXX
            [SUBSCRIBER CODE] =>UK91002970
            [SUBSCRIBER_ID] => 61610070
            [SUBSCRIBERNAME] => XXXXX
            [SUBSCRIBERPHONE] => XXXXXXX
            [BOUQ] => UK FTA-1
            [STB_NOS] => 1
            [LAST MONTH PAYMENT] => 0
            [AMOUNT_DUE] => 0
            [MINIMUM_RECHARGE] => 221
            [TOTAL_PAYABLE] => 221
            [EXPIRY_DATE] => 08-06-2020
            [PAY_AMOUNT] => 0
            [CURRENT BALANCE] => 0
        )
Pamela
  • 684
  • 1
  • 7
  • 21
  • 3
    this array doesn't seem to be having any indices like CURRENT BALANCE. You better off with positional placeholders in your query and then just execute($row). – Your Common Sense Jun 13 '20 at 11:53
  • `it is not working` means you get empty values in the DB or you get some error? `08-06-2020` is not a valid format for a `date` in mysql. – user3783243 Jun 13 '20 at 11:58
  • `$row['SUBSCRIBER CODE']` should be `$row[2]` (and same for others), you probably get `undefined index` NOTICE. – endo64 Jun 13 '20 at 12:08
  • I receive error Invalid argument supplied for foreach() – Pamela Jun 13 '20 at 12:10
  • 2
    Invalid argument supplied for foreach() means there is a problem with your data, not pdo – Your Common Sense Jun 13 '20 at 12:13
  • There is no issue with data. – Pamela Jun 13 '20 at 12:22
  • `Invalid argument` means `$dataArray` is not iterable. Please add code where that is built. – user3783243 Jun 13 '20 at 12:25
  • I am using a csv file to pull out the data to array . Code added to question – Pamela Jun 13 '20 at 12:27
  • You need to show how `$dataArray` is being created, as the above comments requested. – ADyson Jun 13 '20 at 12:29
  • Added to original question. – Pamela Jun 13 '20 at 12:34
  • Do you define the `$dataArray` in the same file/scope as your foreach? – M. Eriksson Jun 13 '20 at 12:42
  • @MagnusEriksson Yes. Same file – Pamela Jun 13 '20 at 12:43
  • 1
    But are they in the same scope as well? Or is one of those snippets in a function//method or similar? Because when you do `foreach($dataArray as $row)`, that variable doesn't seem to be an array anymore. What happens in between those two posted code snippets? – M. Eriksson Jun 13 '20 at 12:44
  • Same File and scope. It is exactly the same code i have in 1 file. – Pamela Jun 13 '20 at 12:46
  • I don't see how that's possible. Variables don't just change values without something telling them to. There must be something in between you defining it and trying to iterate over it. – M. Eriksson Jun 13 '20 at 12:48
  • 2
    That's completely unrelated to the error in your question. The error in your question _"Invalid argument supplied for foreach()"_ would stop your code from even entering the foreach loop, which means that `$row['SUBSCRIBER CODE']` would never be executed at all. There's something you're not telling us. I'm out... – M. Eriksson Jun 13 '20 at 12:54
  • Just an aside, you can really simplify your load csv to something like https://stackoverflow.com/a/5674215/1213708 – Nigel Ren Jun 13 '20 at 13:26
  • Removed the function and added code to convert csv into an associative array. Now everything works fine. Added update in question. – Pamela Jun 13 '20 at 13:27
  • 1
    Oh what a shame you did exactly when I wrote some code to solve your issue. But I'm glad you solved it though. In this case you should answer your question so noone has to go in here and think that they have to solve an issue that does not exist anymore :-) – bestprogrammerintheworld Jun 13 '20 at 17:33

1 Answers1

0

Your array $dataArray contains this:

Array
    (
        [0] => Array
            (
                [0] =>  REGION
                [1] =>  CENTER
                [2] =>  SUBSCRIBER CODE
                [3] =>  SUBSCRIBER_ID
                [4] =>  SUBSCRIBERNAME
                [5] =>  SUBSCRIBERPHONE
                [6] =>  BOUQ
                [7] =>  STB_NOS
                [8] => LAST MONTH PAYMENT
                [9] =>  AMOUNT_DUE
                [10] =>  MINIMUM_RECHARGE
                [11] =>  TOTAL_PAYABLE
                [12] =>  EXPIRY_DATE
                [13] => PAY_AMOUNT
                [14] => CURRENT BALANCE
            )

[1] => Array
            (
                [0] => XXX
                [1] => XXX
                [2] => UK91002970
                [3] => 61610070
                [4] => XXXXX
                [5] => XXXXXX
                [6] => UK FTA-1
                [7] => 1
                [8] => 0
                [9] => 0
                [10] => 221
                [11] => 221
                [12] => 08-06-2020
                [13] => 0
                [14] => 0
            )

You try to apply values values into your SQL by using REGION, CENTER, SUBSRICBER CODE etc. but you're mixing up keys with values

This array does not include $row['REGION'], does not include $row['CENTER'] etc...

If you're doing like this:

foreach($dataArray as $row) {
    $stmt->execute(array(
    ':subscribercode' =>  $row['SUBSCRIBER CODE'], 
    ':subscriberid' => $row['SUBSCRIBER_ID'],
    etc...
}

then the array $dataArray would have to look like this:

Array
(
    ['SUBSCRIBER CODE'] => {value}
    ['SUBSCRIBER_ID'] => {value}
    ['SUBSCRIBERNAME'] => {value}
    etc
)

One solution would be if (if I understand the array structure correctly) to apply keys from second and further based on first elements values

Lets say we have this:

Array
(
    [0] => Array
        (
            [0] => REGION
            [1] => CENTER
            [2] => SUBSCRIBER CODE
            [3] => SUBSCRIBER_ID
        )

    [1] => Array
        (
            [0] => XXX
            [1] => XXX
            [2] => UK91002970
            [3] => 61610070
        )

    [2] => Array
        (
            [0] => XXX
            [1] => XXX
            [2] => UK91002971
            [3] => 61610217
        )

    [3] => Array
        (
            [0] => XXX
            [1] => XXX
            [2] => UK91002972
            [3] => 61610218
        )

)

then first element contains: ['REGION', 'CENTER', 'SUBSCRIBER CODE','SUBSCRIBER_ID']

and second to fourth element contains the actual values "mapped to region, center , subscriber code, subscriber id".

A solution would be:

$dataArray = [];
$dataArray[] = ['REGION', 'CENTER', 'SUBSCRIBER CODE','SUBSCRIBER_ID'];
$dataArray[] = ['XXX', 'XXX', 'UK91002970', '61610070'];
$dataArray[] = ['XXX', 'XXX', 'UK91002971', '61610217'];
$dataArray[] = ['XXX', 'XXX', 'UK91002972', '61610218'];

$keys_apply = array_values($dataArray[0]); //get all values of first item in array
$i = 0;
foreach($dataArray as $key=>$item) {
    if ($i>0) { //skip first item
        $index = 0; //We know that index are 0 and above we start with zero here and
        //and add one for each iteration
        foreach($item as &$ik) {
            //create new item with mapped key-value from first item in array but with 
            //the same value ($ik)
            $dataArray[$key][$keys_apply[$index]] = $ik;

            //Unset removes $dataArray[1][0], [1][1], [1][2] etc.
            unset($dataArray[$key][$index]);

            //Next element in this inner array
            $index++;
        }
    }
    $i++;
}

You would then have:

Array
(
    [0] => Array
        (
            [0] => REGION
            [1] => CENTER
            [2] => SUBSCRIBER CODE
            [3] => SUBSCRIBER_ID
        )

    [1] => Array
        (
            [REGION] => XXX
            [CENTER] => XXX
            [SUBSCRIBER CODE] => UK91002970
            [SUBSCRIBER_ID] => 61610070
        )

    [2] => Array
        (
            [REGION] => XXX
            [CENTER] => XXX
            [SUBSCRIBER CODE] => UK91002971
            [SUBSCRIBER_ID] => 61610217
        )

    [3] => Array
        (
            [REGION] => XXX
            [CENTER] => XXX
            [SUBSCRIBER CODE] => UK91002972
            [SUBSCRIBER_ID] => 61610218
        )

)

And then your original 

foreach($dataArray as $row) {
    $stmt->execute(array(
    ':subscribercode' =>  $row['SUBSCRIBER CODE'], 
    etc...

}

would work.

bestprogrammerintheworld
  • 5,417
  • 7
  • 43
  • 72