5

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.

Sujit Agarwal
  • 12,348
  • 11
  • 48
  • 79
  • You're probably not going to be able to send that many INSERT statements so quickly. Have you considered either configuring the database server to not close the connection so quickly, or reopening the connection when it gets closed? – Wooble Jun 03 '11 at 18:46
  • Its not in my hand to configure the server. Because i dont have the priviliges. So what is the alternate soln? – Sujit Agarwal Jun 03 '11 at 18:47
  • Using a prepared statement is probably a good idea too, and not just because it will avoid SQL injection. – Wooble Jun 03 '11 at 18:48
  • There is no probability of sql injection. This insertion of values is to be done once only. And then my task will be over. – Sujit Agarwal Jun 03 '11 at 18:49
  • 1
    "There is no probability of SQL injection." I guess you work for Sony. – Michael B Jun 03 '11 at 18:56
  • I mentioned the above line beacause, there is no other person who would be using the database except me at this moment. No HTML forms, No front-end, no other user.. I am working with filling up the DB. After the db is complete with values, my task will be over. – Sujit Agarwal Jun 03 '11 at 18:59
  • @Coding-Freak: using a prepared statement fors something being done repeatedly will perform better than building SQL statements and calling `mysql_query` on them, too; it's not *just* for security. In any case, constructing SQL like that is a bad habit, even if in this particular case it won't get exploited. – Wooble Jun 03 '11 at 19:28

4 Answers4

17

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.
}
bhamby
  • 15,112
  • 1
  • 45
  • 66
3

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);
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
0

I think @nick's answer is best - but an alternative maybe is to write SQL to create an SP that lists all the inserts, i.e. as a large string, send that across the wire, execute the SP, and delete it.

I'm a MSSQL person myself and I would never recommend this - but it's the kind of crazy hack I've had to use in the pass when I couldn't rely on MSSQL's own brand of bulk upload functionality.

Alternatively can you build multiple insert statements in SQL and send it as a single command to the DB? Again sorry not a mysql expert but this is very possible in other databases.

James Gaunt
  • 14,631
  • 2
  • 39
  • 57
  • 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. – Sujit Agarwal Jun 03 '11 at 18:53
  • Can mySQL accept an SQL instruction that inserts multiple inserts anyway - i.e. not as an SP. In which case split the inserts down in to batches. – James Gaunt Jun 03 '11 at 18:56
0

If you can use MySQL transactions and fire off queries in batches of, say, several hundred at a time, it may improve the reliability and speed of the insert.

damianb
  • 1,224
  • 7
  • 16