CODEWITHSUNDEEP
phpmysqlarraysinsert
NestedCodeblocksFTW
NestedCodeblocksFTW

Reputation: 746

Efficient php code to insert array data into mysql table?

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()); 
}
?>

Upvotes: 1

Views: 1673

Answers (2)

Tuncay G&#246;nc&#252;oğlu
Tuncay G&#246;nc&#252;oğlu

Reputation: 1767

Instead of sending queries to server one by one in the form 

insert into table (x,y,z) values (1,2,3)

You should use extended insert syntax, as in:

insert into table (x,y,z) values (1,2,3),(4,5,6),(7,8,9),...

This will increase insert performance by miles. However you need to be careful about how many rows you insert in one statement, since there is a limit to the size of a single SQL can be. So, I'd say start with 100 row packs and see how it goes, then adjust pack size accordingly. Chances are your insert time will go down to like 5 seconds, putting it way under max_execution_time limit.

Upvotes: 0

jdstankosky
jdstankosky

Reputation: 657

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);
}
?>

Upvotes: 4

Related Questions