php efficient read file and insert sql

Question

file: data.txt (11617 lines)

   user  datetime
   23   2015-03-01 08:04:15 
   15   2015-05-01 08:05:20 
  105   2015-05-01 08:07:10 
   15   2015-06-01 08:08:29 
  105   2015-06-01 08:12:48

I only need data in 2015-06, I'm using fget and check each line's datetime but really slow, more than 50s.

    $d='data.txt';
    import($d);
    function import($d){
        $handle = fopen($d, "r") or die("Couldn't get handle");
        if ($handle) {
            while (!feof($handle)) {
                $buffer = fgets($handle, 4096);
                $line=explode("\t",$buffer);
                if(date("Y-m",strtotime($line[1])=="2015-06"){
                   mysql_query("INSERT INTO `table` ....");
                }
                else{
                   //break? when month>6
                }
            }
            fclose($handle);
        }
    }

SOLUTION: less than 2s!!!! (thanks to Kevin P. and Dragon)

            if(substr($line[1],0,7)=="2015-06"){
               $sql.=empty($sql)?"":","."(`".$line[1]."`.........)";
            }
            elseif(substr($line[1],0,7)>"2015-06"){
               break;// when month>6
            }
            mysql_query("INSERT INTO `table` ....".$sql);

for the date compare, just use substr() if( substr($line[1],0,7)== "2015-06"){ ... — user557846
– user557846, Commented Aug 11, 2015 at 1:34
@jpw: if it's a web application, making a user wait 50 seconds is huge. — Amadan
– Amadan, Commented Aug 11, 2015 at 1:37

Amadan · Accepted Answer · 2015-08-11 01:50:15Z

2

Can't be helped, use something faster than PHP. For instance, you can use grep or awk to read the file and filter it quickly. For example:

$lines = explode("\n", `awk '$2 ~ /^2015-06/ { print }' data.txt`);

EDIT: Also, fgets is not guaranteed to give you whole lines. You are getting 4096 bytes at a time; the boundary could be in the middle of a line, which will make the line not match if you are lucky, or break your code due to missed assumptions (such as the length of the $line array when constructing the SQL) if not.^*

^{*) Or vice versa - it would be better for it to break completely, that is at least an obvious error yelling to be fixed; as opposed to silent data droppage.}

edited Aug 11, 2015 at 1:50

answered Aug 11, 2015 at 1:34

Amadan

200k23 gold badges252 silver badges321 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:14:37Z

1

Maybe insert multiple entries in to the DB at once instead of calling it every time you find a desired time?

In which case it's similar to this

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Aug 11, 2015 at 1:37

Kevin P.

4014 silver badges13 bronze badges

1 Comment

xtr3mz Over a year ago

much much much more faster! less than 2s

GerBawn · Accepted Answer · 2015-08-11 01:39:06Z

0

Maybe you should use grep to filter out the lines you do not need.

answered Aug 11, 2015 at 1:39

GerBawn

3011 gold badge4 silver badges13 bronze badges

Collectives™ on Stack Overflow

php efficient read file and insert sql

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related