Fastest way to insert 134675 values in remote database

Question

I have an array with more than 134675+ values, I need to insert them to my mySQL table. I know all the things needed in this to work with PHP and mySQL data insertion. Is there a fast method that would let me insert all these values on a remote server within 30-60 seconds? because when i am trying it with the foreach method, the page is timing out. The remote server is not allowing DB connections to persist for more than 60 seconds. I dont know why. So please help me with a fast logic.

Here is some code i tried:

foreach($array as $value)
{
    $sql="insert into collected values('".$value."')";
    $res=mysql_query($sql);
    //then some extra code.
}

NOTE I dont have so many access privileges on the server. My DB account can only insert values and nothing more than that. And its a constraint on the mySQL DB. I cannot use CSV or any other thing.

Answer 1

You could include in your loop the mysql_ping() function. This function checks to make sure that the connection is open, and if it is not, it re-connects.

Using your own example, you could do something like:

foreach($array as $value) {
    mysql_ping($dbconn);
    $sql="insert into collected values('".$value."')";
    $res=mysql_query($sql);
    //then some extra code.
}

Edit: It should be noted that according to the docs, after MySQL 5.0.14, PHP does not automatically reconnect. If you use a newer version of MySQL you will have to put in your own connection logic, maybe like this (I haven't tested):

function check_dbconn($connection) {
    if (!mysql_ping($connection)) {
        mysql_close($connection);
        $connection = mysql_connect('server', 'username', 'password');
        mysql_select_db('db',$connection);
    } 
    return $connection;
}

foreach($array as $value) {
    $dbconn = check_dbconn($dbconn);
    $sql="insert into collected values('".$value."')";
    $res=mysql_query($sql, $dbconn);
    //then some extra code.
}

Answer 2

I think it would be better if you put your values within a csv file and you use load data syntax.

edit. Example

Let's suppose you have a txt file with all your values

value1
value2
value3
and so on

Once you create your table structure

create table mytest (
id int not null auto_increment primary key,
myvalue varchar(50)
) engine = myisam;

and upload your txt file you can do something like this

load data infile 'myfile.txt'
into table mytest (myvalue);