Submit Textfile Contents To Database

[Disease]

Is there a way I can configure mysql to allow a query that large? Or any other way i can submit like 100k-1million words at a time?

If you go that route you'll need to increase the max_allowed_packet directive in your MySQL configuration (my.ini). It's likely you'll also need to increase PHP's maximum memory limit, because of the size of the query string it needs to handle.

PHP Code:

<?php

// PHP memory limit
ini_set("memory_limit","256M");

mysql_connect ("server", "username", "password");
mysql_select_db ("database_name");

// Build the lines array, thus $lines[0] will be the first and etc.
$lines = array_map ('trim', file ("file", FILE_SKIP_EMPTY_LINES));
$sql = "INSERT INTO table_name (column_name) VALUES ";

foreach ($lines as $index => $curLine)
{
  $sql .= "('$curLine')";
  
  // count () returns the number of lines, but it isn't zero-based, so we add 1 to the line number to make sure it isn't the last element to avoid the trailing comma
  if (count ($lines) != ($index + 1))
  {
    $sql .= ", ";
  }
}

// And query it
mysql_query ($sql) or die (mysql_error ());

?>

With the above script you're looking for one of two errors. If you get a PHP error about memory allocation, then you need to increase the "256M." That setting works fine on my test set of 1.3M entries, but your entries may be larger. Either way I don't imagine you'll need to exceed 512M at the very high end of the spectrum.

If, on the other hand, you get a SQL error, such as "MySQL has gone away" then you need to increase your max_allowed_packet directive even further. Again, I configured mine at 256M for testing purposes and it worked fine. Play around with your own numbers until you successfully import all of the data.

[xbiohazardx]

Your the freakin best m8, worked like a charm!

[Disease]

Your the freakin best m8, worked like a charm!

Glad you got this sorted out. If you haven't already I recommend reverting your MySQL configuration back to normal if this was a one-time deal.

[Chimi]

Disease, you are extremely skillful, I'm jealous. This has helped me out also, thanks!