高效的PHP代码将数组数据插入mysql表？

Question

So I have a flatfile db in the format of username:$SHA$1010101010101010$010110010101010010101010100101010101001010:255.255.255.255:1342078265214

Each record on a new line... about 5000+ lines.. I want to import it into a mysql table. Normally I'd do this using phpmyadmin and "file import", but now I want to automate this process by using php to download the db via ftp and then clean up the existing table data and upload the updated db.

id(AUTH INCREMENT) | username | password | ip | lastlogin

The script I've got below for the most part works.. although php will generate an error: "PHP Fatal error: Maximum execution time of 30 seconds exceeded" I believe I could just increase this time, but on remote server I doubt I'll be allowed, so I need to find better way of doing this.

Only about 1000 records will get inserted into the database before that timeout...

The code I'm using is below.. I will say right now I'm not a pro in php and this was mainly gathered up and cobbled together. I'm looking for some help to make this more efficient as I've heard that doing an insert like this is just bad. And it really sounds bad aswel, as a lot of disk scratching when I run this script on local pc.. I mean why does it want to kill the hdd for doing such a seemingly simple task.

<?php
require ('Connections/local.php');

$wx = array_map('trim',file("auths.db"));
$username = array();
$password = array();
$ip = array();
$lastlogin = array();
foreach($wx as $i => $line) {

        $tmp = array_filter(explode(':',$line));
        $username[$i] = $tmp[0];
        $password[$i] = $tmp[1];
        $ip[$i] = $tmp[2];
        $lastlogin[$i] = $tmp[3];

mysql_query("INSERT INTO authdb (username,password,ip,lastlogin) VALUES('$username[$i]', '$password[$i]', '$ip[$i]', '$lastlogin[$i]') ") or die(mysql_error()); 
}
?>

Answer 1

Try this, with bound parameters and PDO.

<?php
require ('Connections/local.php');

$wx = array_map('trim',file("auths.db"));
$username = array();
$password = array();
$ip = array();
$lastlogin = array();

try {
    $dbh = new PDO("mysql:host=$ip;dbname=$database", $dbUsername, $dbPassword);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

$mysql_query = "INSERT INTO authdb (username,password,ip,lastlogin) VALUES(:username, :password, :ip, :lastlogin)";
$statement = $dbh->prepare($mysql_query);

foreach($wx as $i => $line) {
        set_time_limit(0);

        $tmp = array_filter(explode(':',$line));
        $username[$i] = $tmp[0];
        $password[$i] = $tmp[1];
        $ip[$i] = $tmp[2];
        $lastlogin[$i] = $tmp[3];

        $params = array(":username"  => $username[$i],
                        ":password"  => $password[$i],
                        ":ip"        => $ip[$i],
                        ":lastlogin" => $lastlogin[$i]);
        $statement->execute($params);
}
?>