Submit Textfile Contents To Database

[xbiohazardx]

So I have a large text file & I want to submit its contents to a database.

How exactly would I got about doing that?

Note: The text file is in a format of
cat
dog
fish

(Return has been pressed after everyword)

Also would there be a way to assign a variable to each word
So like line 1 would be say "cat" but would be declared like $line1 so I can also manipulate the text.

Thanks in advance.

[Disease]

So I have a large text file & I want to submit its contents to a database.

How exactly would I got about doing that?

Note: The text file is in a format of
cat
dog
fish

(Return has been pressed after everyword)

Also would there be a way to assign a variable to each word
So like line 1 would be say "cat" but would be declared like $line1 so I can also manipulate the text.

Thanks in advance.

You aren't going to be assigning each line as its own variable. You'd need to know exactly how many lines there are in the file and then it wouldn't be extensible nor flexible. Rather you can use an array. Simply put, an array is a container of values. PHP offers a very simple way to feed each line of a file directory into an array via its file () function.

From there you'd want to build a SQL INSERT statement. Supposing you're using MySQL, you can insert multiple rows in one INSERT statement by creating a list of VALUE pairs. Example:

Code:

INSERT into table_name (col1, col2) VALUES (someData, someData), (moreData, moreData), (evenMoreData, evenMoreData)

An example:

PHP Code:

<?php

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

?>

[Orgy]

Wow Disease, that's a lot more complex than I would have told him.

I would have just had him explode it by "\n", and then use a for each loop.

[Disease]

Wow Disease, that's a lot more complex than I would have told him.

I would have just had him explode it by "\n", and then use a for each loop.

The file () function handles splitting the data into an array based on new lines for you. The only complex thing I did past that was throwing the elements of that array through the trim () function so there would be no residual whitespace.

[Orgy]

Yeah, I guess it's not as complex as I originally thought when I first glanced at it. Still more than I would have done.

[xbiohazardx]

You aren't going to be assigning each line as its own variable. You'd need to know exactly how many lines there are in the file and then it wouldn't be extensible nor flexible. Rather you can use an array. Simply put, an array is a container of values. PHP offers a very simple way to feed each line of a file directory into an array via its file () function.

From there you'd want to build a SQL INSERT statement. Supposing you're using MySQL, you can insert multiple rows in one INSERT statement by creating a list of VALUE pairs. Example:

Code:

INSERT into table_name (col1, col2) VALUES (someData, someData), (moreData, moreData), (evenMoreData, evenMoreData)

An example:

PHP Code:

<?php

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

?>

That works for the most part, It works with a small amount of words but I'm trying to insert 1000's-millions and this script dosnt submit them when I try.

[Diabolic]

Wow, That confused me.. I had to break it down to understand it hahaaa.

[Arеs]

Wow this also confused me. But now I understand it.

[Disease]

That works for the most part, It works with a small amount of words but I'm trying to insert 1000's-millions and this script dosnt submit them when I try.

It's likely not submitting because the transfer size of the query exceeds your MySQL configuration. You'll need to stagger the process. For example, you might build an INSERT query of every 100 or 1000 entries.

[xbiohazardx]

It's likely not submitting because the transfer size of the query exceeds your MySQL configuration. You'll need to stagger the process. For example, you might build an INSERT query of every 100 or 1000 entries.

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?