PHP Foreach large data

Question

There is MySQL table with 24500 rows of data, and there is text file with 26000 string of data needed to be inserted into MySQL, problem is what this 26000 strings duplicates data in MySQL table, so we need to compare them, and insert only new/unique.

cadastreArray - array from text file

districtArray - mysql array

When i try to do

foreach ($cadastreArray as $cadastreValue) {
    $districtExist = false;
    foreach ($districtArray as $districtData) {
        if ($cadastreValue[0] == $districtData['1']) {
            $districtExist = true;
            break;
        }
    }
}

if(!$districtExist) { MySQL INSERT ... }

i am getting execution time error, and even 3 minutes are not enought. Maybe you can offer better/faster way?

Compare on database level. Or separate the process. When first 10k rows are executed, try new 10k and then new (till the end) — Ivan Yonkov
– Ivan Yonkov, Commented Mar 20, 2014 at 11:45

prasoon · Accepted Answer · 2014-03-20 11:47:38Z

2

May be you can set the mysql field as unique so when you will be going to insert it will not insert and will generate error number and will continue execution. So you need not to compare.

One more thing that you can do is you can increase max_execution_time in php.ini

answered Mar 20, 2014 at 11:47

prasoon

9218 silver badges25 bronze badges

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

1 Comment

Kickstart Over a year ago

Further to this, look up ON DUPLICATE KEY UPDATE if you need to update any other fields when the unique value is found (eg, a date last checked).

Kickstart · Accepted Answer · 2014-03-20 14:25:14Z

Another option. Load your 26k text file into a temp table (LOAD DATA INFILE... will do this quickly).

Then you can do an insert based on a query that takes your temp table and LEFT JOINs that against your full table, checking that a field on the full table is NULL.

Simple example script here:-

<?php

$file = "SomeTextFile.txt";

$sql = "CREATE TEMPORARY TABLE cadastre
(
    field1 INT,
    field2 VARCHAR(255),
    etc...
)";

if(!($db->query($sql)))
{
    die($db->error());// if error, stop script
}

if(!($db->query("LOAD DATA INFILE '$file' INTO TABLE cadastre")))
{
    die($db->error());// if error, stop script
}

$sql = "INSERT INTO district (field1, field2, field3, ......)
        SELECT a.field1, a.field2, a.field3
        FROM cadastre a
        LEFT OUTER JOIN district b
        ON a.field1 = b.field1
        WHERE b.field1 IS NULL";

if(!($db->query($sql)))
{
    die($db->error());// if error, stop script
}

?>

Make sure the temp table and the table you are inserting into have useful indexes added.

Collectives™ on Stack Overflow

PHP Foreach large data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related