PHP MYSQL query to delete all values that are not in loop

Question

I am trying to think of a query that will do what I am wanting, however it has got me stumped so figured I would pick some brains for ideas.

I have a excel file that is read and converted to a loop for each row and for each column.

There are about 5000 values on the excel file so there are a lot of loops happening, I am wanting it to query the database inside the second loop for the column code, and if there is a value inside my database that is not any of the codes it is looping it will delete it. And any codes that are in the loop but not on the database will be echo'd on the page.

I have the script working fine however its just structuring the query that I am confused about.

Any advice greatly appreicated, Simon

my code:

fileName    = '2_1_2013_UPC_customer.xlsx';
$objPHPExcel = PHPExcel_IOFactory::load("2_1_2013_UPC_customer.xlsx");
include_once dirname(__FILE__) . ('/PHPExcel/Writer/Excel2007.php');
require_once dirname(__FILE__) . '/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("2_1_2013_UPC_customer.xlsx");
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns          = ord($highestColumn) - 64;
    for ($row = 2; $row <= $highestRow; ++$row) {
        for ($col = 0; $col < $highestColumnIndex; ++$col) {
            $cell     = $worksheet->getCellByColumnAndRow($col, $row);
            $val      = $cell->getValue();
            $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);

            if ($val == null) {
            } else {

                if ($col == 0) {
                $name = $val;
                }
                if ($col == 2) {
                $description = $val;
            }
            if ($col == 3) {
                $reference = $val;
            }
            if ($col == 4) {
                $weight = $val;
            }
        }
}

}

nathan hayfield · Accepted Answer · 2013-03-29 00:16:47Z

1

Build an array of all the codes in the excel file as you loop over it. Once you have all the codes in the excel file, query the database and get an array of all the codes there. Then you can easily compare them. It should be easier to do once you don't have to worry about all the php_excel code. Once you have the two arrays separate you can use php array_search function to check if the code exists in both arrays and then handle it.

edited Mar 29, 2013 at 0:16

answered Mar 29, 2013 at 0:01

nathan hayfield

2,7051 gold badge20 silver badges29 bronze badges

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

2 Comments

Simon Staton Over a year ago

hmm good idea, I could select all the references, get them as an array or something then what function could I use to identify those that dont exsist and those that are new?

nathan hayfield Over a year ago

hmm i misread what you wanted. yes you would need to grab a list of everything in the database to compare. editing my answer.

Collectives™ on Stack Overflow

PHP MYSQL query to delete all values that are not in loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related