# Intermediate Question: use Count Function()

**URL:** https://forum.kirupa.com/t/intermediate-question-use-count-function/263662
**Category:** programming
**Created:** [June 18, 2008, 3:13am UTC](https://forum.kirupa.com/t/intermediate-question-use-count-function/263662 "2008-06-18T03:13:53Z")
**Posts on this page:** 8
**Page:** 1

<div class="post-metadata">

### Author: ![benghee](https://avatars.discourse-cdn.com/v4/letter/b/9dc877/32.png) [@benghee](https://forum.kirupa.com/u/benghee)
#### Post date: [June 18, 2008, 3:13am UTC](https://forum.kirupa.com/t/intermediate-question-use-count-function/263662/1 "2008-06-18T03:13:53Z")

</div>

Hi all,

I wrote an update database code but since my csv file is too large for server handle, it exceeded maximum time limit. My question is, how am i going to count every 1000 lines in file then post to server and repeat same step again by using function in order not to exceed the default time. For example, my file has 20,000 records. I know there is some other methods like set time limit function, change time limit in php.ini…but i’m not going to do that as it will mess up the server. Any expert here can help me out? Thanks for looking this up for me 😃

Here is my original code tat result Max Time Limit fatal error:

$handle = fopen(“test.csv”, “r”);  
fgetcsv($handle, 20000, “,”);

while (($data = fgetcsv($handle, 20000, “,”)) !== FALSE){  
$user = str\_replace(",", “”, $data[0]);  
$userid = str\_replace(",", “”, $data[1]);  
$pwd = str\_replace(",", “”, $data[2]);  
$email = str\_replace(",", “”, $data[3]);

$query =“UPDATE testdb SET user =’”.$user."’,userid=’".$userid."’,pwd=’".$pwd."’,  
email=’".$email."’ WHERE user=’".$user."’ LIMIT 1";  
echo $query."\<hr\>";  
mysql\_query($query) or die(mysql\_error());

}  
fclose($handle);

---

<div class="post-metadata">

### Author: ![borrob](https://avatars.discourse-cdn.com/v4/letter/b/a87d85/32.png) [@borrob](https://forum.kirupa.com/u/borrob)
#### Post date: [June 18, 2008, 6:16am UTC](https://forum.kirupa.com/t/intermediate-question-use-count-function/263662/2 "2008-06-18T06:16:43Z")

</div>

The only way off splitting this proces up i could think off is by using multiple ajax calls. But if somebody has a better idea i sure would like to know because i need something similar…

---

<div class="post-metadata">

### Author: ![agnus](https://avatars.discourse-cdn.com/v4/letter/a/4bbf92/32.png) [@agnus](https://forum.kirupa.com/u/agnus)
#### Post date: [June 18, 2008, 1:29pm UTC](https://forum.kirupa.com/t/intermediate-question-use-count-function/263662/3 "2008-06-18T13:29:15Z")

</div>

Try this. This script will take your CSV data and read it and on every 100 rows will make a redirect to itself sending the row it reached so you won’t get timed out. It’s not fully tested, it’s just an idea. Try it and tell me if it works.

```php

<?php
    set_time_limit(1);
    
    $handle = fopen("test.csv", "r");
    fgetcsv($handle, 200000, ",");
    
    $row = !isset($_GET['row']) ? 1 : $_GET['row'];
    $maximum_results_in_DB = 1419; // the number of rows you exported into your CSV
    
    $file = fopen("file.txt","a");
    
    while (($data = fgetcsv($handle, 20000, ",")) !== FALSE)
    {
        $row++;        
        
        
        fwrite($file,$data[0]."
");
                
        if( $row%10 == 0 && $row <= $maximum_results_in_DB )
        {
            fwrite($file,"end of the line baby 
");
            header("Location:index.php?row=".$row);
        }        
    }
    
    fclose($file);
    fclose($handle);    
?>

```

---

<div class="post-metadata">

### Author: ![benghee](https://avatars.discourse-cdn.com/v4/letter/b/9dc877/32.png) [@benghee](https://forum.kirupa.com/u/benghee)
#### Post date: [June 20, 2008, 9:47am UTC](https://forum.kirupa.com/t/intermediate-question-use-count-function/263662/4 "2008-06-20T09:47:01Z")

</div>

Hi Agnus,

I tried to implement your code into mine, but it doesnt work out as the result i want. What i really wanna do is a code that have loop every 1000 entries and update the database then continue the second 1000 entries again for the rest of the records. It should be read from csv file then do UPDATE to MYSQL. Any idea? Thanks.

Here is my code:

```php

<?php
set_time_limit(1);
$handle = fopen("test.csv", "r");
fgetcsv($handle, 20000, ","); 
 
$row = !isset($_GET['row']) ? 1 : $_GET['row'];
$maximum_results_in_DB = 10000; // the number of rows you exported into your CSV
 
while (($data = fgetcsv($handle, 20000, ",")) !== FALSE){ 
        $serial = str_replace(",", "", $data[0]); 
$uid = str_replace(",", "", $data[1]); 
$reloadDate = str_replace(",", "", $data[2]); 
$status = str_replace(",", "", $data[3]);
        $row++; 
 
         if( $row%10 == 0 && $row <= $maximum_results_in_DB )
{
        
         $query ="UPDATE testdb SET user ='".$user."',userid='".$userid."',pwd='".$pwd."' , 
email='".$email."' WHERE user='".$user."' LIMIT 1";
echo $import."<hr>"; 
       
 mysql_query($query) or die(mysql_error()); 
        }        
 
 
    } 
 
fclose($handle); 
 
?>

```

---

<div class="post-metadata">

### Author: ![actionAction](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/actionaction/32/3987_2.png) [@actionAction](https://forum.kirupa.com/u/actionAction)
#### Post date: [June 22, 2008, 3:49am UTC](https://forum.kirupa.com/t/intermediate-question-use-count-function/263662/5 "2008-06-22T03:49:03Z")

</div>

If you choose to go the PHP loop route, this should help:

[http://blog.thinkphp.de/archives/131-Handling-large-files-without-PHP.html](http://blog.thinkphp.de/archives/131-Handling-large-files-without-PHP.html)

It breaks up the CSV into smaller chunks, so you can process less data and process it sequentially.

Honestly though, I think **LOAD DATA INFILE** is what you actually need. It is the correct way to import large amounts of data into mysql. [http://dev.mysql.com/doc/refman/5.0/en/load-data.html](http://dev.mysql.com/doc/refman/5.0/en/load-data.html)

---

<div class="post-metadata">

### Author: ![benghee](https://avatars.discourse-cdn.com/v4/letter/b/9dc877/32.png) [@benghee](https://forum.kirupa.com/u/benghee)
#### Post date: [June 23, 2008, 6:15am UTC](https://forum.kirupa.com/t/intermediate-question-use-count-function/263662/6 "2008-06-23T06:15:26Z")

</div>

Thank you, actionAction for your suggestion. Its hard for me to break up large csv into smaller chunks, so I have done some modification on my previous code by setting the time limit to 1000 instead of 1 and it can update partial records to database. For example:

set\_time\_limit(1000);

Can anyone here briefly explain to me that what are these command means:

$row = !isset($\_GET[‘row’]) ? 1 : $\_GET[‘row’];

and also

$row%10 == 0 && $row \<= $maximum\_results\_in\_DB

Thanks. If there is any example code, that would be really helpful 😃

---

<div class="post-metadata">

### Author: ![actionAction](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/actionaction/32/3987_2.png) [@actionAction](https://forum.kirupa.com/u/actionAction)
#### Post date: [June 23, 2008, 5:01pm UTC](https://forum.kirupa.com/t/intermediate-question-use-count-function/263662/7 "2008-06-23T17:01:00Z")

</div>

No problem!

> $row = !isset($\_GET[‘row’]) ? 1 : $\_GET[‘row’];  
> It’s an alternate syntax for an if/else conditional. This is the same as:

```php
if(!isset($_GET['row']))//if the form hasn't been posted
{
    $row = 1;
}else{
    $row = $_GET['row'];
}

```

> $row%10 == 0 && $row \<= $maximum\_results\_in\_DB  
> This is saying if the there is no remainder when dividing the $row var by 10 (i.e. it is a multiple of ten) AND the value is less than or equal to the $maximum\_results\_in\_DB variable, do whatever action follows.

---

<div class="post-metadata">

### Author: ![benghee](https://avatars.discourse-cdn.com/v4/letter/b/9dc877/32.png) [@benghee](https://forum.kirupa.com/u/benghee)
#### Post date: [June 24, 2008, 6:12am UTC](https://forum.kirupa.com/t/intermediate-question-use-count-function/263662/8 "2008-06-24T06:12:07Z")

</div>

Ok, here i rewrite the code to include the sleep() function. All the records can be displayed without any timeout but when they update to MYSQL, it takes too long and result Max time limit error no matter how long I’m gonna set the limit. Any idea how to solve it? Thanks.

```php

<?php 

set_time_limit(1200); 
$send_count = 1000; 
$send_delay = 1; 

$handle = fopen("2006.csv", "r"); 
fgetcsv($handle, 20000, ","); 

while (($data = fgetcsv($handle, 20000, ",")) !== FALSE){ 

if($send_count == 0) 
{ 
$send_count = 10; 
sleep($send_delay); 
} 
$send_count--; 

$user = str_replace(",", "", $data[0]); 
$userid = str_replace(",", "", $data[1]); 
$pwd = str_replace(",", "", $data[2]); 
$email = str_replace(",", "", $data[3]); 

$query ="UPDATE testdb SET user ='".$user."',userid='".$userid."',pwd='".$pwd."' , 
email='".$email."' WHERE user='".$user."' LIMIT 1"; 
echo $query."<hr>"; 

mysql_query($query) or die(mysql_error()); 

} 
fclose($handle); 

?> 

```