CODEWITHSUNDEEP
phparraysexcelforeach
Junnel
Junnel

Reputation: 107

Foreach loop from array and how to contain it

I've been struggling with this problem for a week now and i don't have any more option but to ask a question here in stackoverflow.

I'm currently using PHP Spout for reading rows of a xlsx file and the array looks like this 

Array
(
  [Name] => abcd1234
  [Address] => abcd1234
  [Number] => abcd1234
)
Array
(
  [Name] => abcd1234
  [Address] => abcd1234
  [Number] => abcd1234
)
Array
(
  [Name] => abcd1234
  [Address] => abcd1234
  [Number] => abcd1234
)

The foreach loop looks like this

// Number of sheet in excel file
foreach ($reader->getSheetIterator() as $sheet) {

    // Number of Rows in Excel sheet
    foreach ($sheet->getRowIterator() as $row) {

        // It reads data after header. In the my excel sheet, 
        // header is in the first row. 
        if ($count1 > 1) { 

            // Data of excel sheet
            $data['Name'] = $row[0];
            $data['Address'] = $row[1];
            $data['Number'] = $row[2];

            //Here, You can insert data into database. 
            //print_r($data);
            //$sql = mysqli_query($connection, "INSERT INTO address(Name, Address, Number) VALUES ('$row[0]', '$row[1]', '$row[2]')");
                foreach ($data as $key => $value) {
                    $value = trim($value); 
                        if (empty($value)){
                        echo $output = "Row ".$count1." Column ".$key." is empty <br/>";
                        }
                }
        }
        $count1++;
    }
}

I have a problem where i need to loop all arrays and find the missing rows on each columns which i manage to do and now i need to ignore or count the duplicate values on rows. I tried doing array_unique but it did not work on the array i was using.

I also tried to insert my array into another array but i also had problems inserting into. I probably have issues with declaring my array but i'm still having some problems. How do i ignore the duplicates on multiple different arrays or how do i merge all the separate arrays into one array inside a foreach loop? Sorry i'm kinda new to arrays in php and thank you if someone is reading this.

Edit: The output that i was aiming for is to count the number of missing values inside an array/row from the xlsx file. Which is just like on the foreach loop in the example code. Example:

Row 213 Column Name is empty 
Row 214 Column Name is empty 
Row 214 Column Address is empty

This is what it looks like when i echo the missing values on rows from a xlsx file.

Now i need to validate if the xlsx file has duplicate rows. My first initial idea was to either echo rows with duplicate values i.e

Row 213 Column Name is has duplicates
Row 214 Column Name is has duplicates
Row 214 Column Address is empty

But everything got complicated which led me to try and use array_unique but also had problems because the way the array is being presented by the xlsx file.

Upvotes: 2

Views: 1874

Answers (2)

Tomaso Albinoni
Tomaso Albinoni

Reputation: 1013

I hope this will get you going:

$data = array();
foreach ($sheet->getRowIterator($startRow = 2) as $row) {
    $data[] = array('Name' => $row[0], 'Address' => $row[1], 'Number' => $row[2])
}
$names = array_column($data, 'Name');
$addresses = array_column($data, 'Address');
$numbers = array_column($data, 'Number');

$unique_names = array_unique($names);
$unique_addresses = array_unique($addresses);
$unique_numbers = array_unique($numbers);

$dup_names = array_diff($names, $unique_names);
$dup_addresses = array_diff($names, $unique_addresses);
$dup_numbers = array_diff($names, $unique_numbers);

And once you get to inserting into your database, here's how you use a prepared statement:

$stmt = $connection->prepare("INSERT INTO address(Name, Address, Number) VALUES (?, ?, ?)");
foreach ($data as $row) {
    $stmt->bind_param('sss', $row['Name'], $row['Address'], $row['Number']);
    $stmt->execute();
}

Upvotes: 1

tdoggy
tdoggy

Reputation: 196

As suggested in a comment on your question, use prepared statements.

As for a solution to your problem, when the SQL query executed and inserts the data successfully, add the unique identifier to an array. Before inserting new data into the database, check to see if the unique identifier is already in the array of inserted values. I'm on my phone, otherwise I'd provide an example. But that should help.

Upvotes: 2

Related Questions