Inserting Data into a mySQL table from a text file using PHP

[husslela03]

hello,

 

I have a text file like this:

 

UserName:FirstName:LastName

doej:John:Doe

 

I would like to put this into a mySQL table using a PHP script.

 

How would I do this?

Would I read the file into an array and then use a for loop, then do the insert sql statement?

 

The First line of the text file are the data field names and the rest are the data that goes into them.

 

Any help would be greatly appreciated!

[phant0m]

Have a look at explode()

to split by new lines, use "\n" as needle.

suppose you have the content of your file in $file

<?php
$lines = explode("\n", $file);
if(count($lines) == 1){ //might be mac-like line ending (CR - 0x0D i.e. 13)
  $lines = explode("\r", $file);
}
$lines = array_map("trim", $lines, array_fill(0, count($lines), " \n\r\t")); //clean superfluous whitespace
foreach($lines as $line){
  $names = explode(':', $line);
  $user = $names[0];
  $first = $names[1];
  $last = $names[2];
//do with them whatever you like
}

[phant0m]

sorry, didn't read everything

this will read you data format and insert it into the database accordingly.

 

 

this might work:

<?php
    $file="blub:2\nsub:seven\nblub:two";
    $lines = explode("\n", $file);
    if(count($lines) == 1){ //might be mac-like line ending (CR - 0x0D i.e. 13)
      $lines = explode("\r", $file);
    }
    $lines = array_map("trim", $lines, array_fill(0, count($lines), " \n\r\t")); //clean superfluous whitespace
    $line = reset($lines);
    $format = explode(':', $line); //now contains array(0 => 'UserName', 1=> 'FirstName' 2 => 'LastName')
    $format = array_map('mysql_real_escape_string', $format);
    //generate query
    $sql = "INSERT INTO table_here (".implode(', ', $format).") VALUES ";
    $to_repeat = "('%s' ". str_repeat(", '%s'", count($format) - 1) ." )";
    $sql_stack = array();
    while($line = next($lines)){
      $names = explode(':', $line);
      $names = array_map('mysql_real_escape_string', $names);
      $sql_stack[] = call_user_func_array('sprintf', array_merge(array($to_repeat), $names));
    }

    $sql .= implode(', ', $sql_stack);
    mysql_query($sql);
?>

 

I haven't tested it

but it seems to output valid mysql statements

//edit: bugfix

 

//edit2: performance increased - now only performs one query

[mrMarcus]

something like this might be a little simpler:

 

<?php
$file = file ('/path/to/your/file.php');

if (is_array ($file))
{
foreach ($file as $v)
{
	$arr = explode (':', mysql_real_escape_string ($v));

	//query;
	$sql = mysql_query ("
		INSERT INTO `table`
			(`UserName`, `FirstName`, `LastName`)
		VALUES
			('".$arr[0]."', '".$arr[1]."', '".$arr[2]."')
	");
}
}
else
{ echo 'File not an array.'; }
?>

 

EDIT: forgot mysql_query() 

[phant0m]

hmm yeah, the file() seems simpler. But I think you'd need FILE_IGNORE_NEW_LINES to remove the new-line at the end.

and performing mysql_real_escape_string () on the entire string seems to be more efficient - why do I think to complicated? 

 

btw: ignore the first line on my second post, forgot to delete it before posting. It was just for testing purposes

 

The First line of the text file are the data field names and the rest are the data that goes into them.

in the end, that seems to be the only difference between your(mrMarcus) solution and mine.

[mrMarcus]

aaah, i just thought that was to demonstrate what each value represented.  didn't think it would remain in the file.  i didn't accommodate for that, you're right.

[husslela03]

Here's what i did, but it didn't seem to pull the data in

 

I'm actually working with a text file now that has a list of words

like a dictionary

each word is on one line,

 

the table i want to create is

word ID

and then word

 

each word is an entry

 

$filename='words5.txt';

$fp=fopen($filename, 'r');

$file_contents=fread($fp, filesize($filename));

 

$words=explode("\n", $file_contents);

 

foreach ($words as $word)

{

  $sql=mysql_query("INSERT INTO Words ('word') VALUES ('".$words[0]."')");

}

 

mysql_close($con);

?>

[mrMarcus]

did you try my example?  file() opens the file and puts the contents into an array (each line in the file becomes an index in the array), so right off the bat that eliminates these three lines:

 

$fp=fopen($filename, 'r');
$file_contents=fread($fp, filesize($filename));

$words=explode("\n", $file_contents);

 

and try changing $words[0] to just $word.

 

make a simple alteration to my code, and it would work just fine for you again:

 

<?php
$file = file ('words5.txt');

if (is_array ($file))
{
   foreach ($file as $v)
   {
      //query;
      $sql = mysql_query ("
         INSERT INTO `Words`
            (`word`)
         VALUES
            ('".mysql_real_escape_string($v)."'')
      ");
   }
}
else
{ echo 'File not an array.'; }
?>

[husslela03]

I do not get any errors, but on the other hand,

it doesn't populate the table,

 

Here's my full code

 

<?php

//read word file into database

//create Lingo database
$con=mysql_connect("localhost", "xxxx", "xxxx");
if (!$con)
{
  die('Could not connect: ' .mysql_error());
}

if (mysql_query("CREATE DATABASE my_Lingo", $con))
{
  echo "Database Created";
}

else
{
  echo "Error creating database: " .mysql_error();
}

//create the words table
mysql_select_db("my_lingo", $con);
$sql="CREATE TABLE Words
(
  wordID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(wordID),
  word varchar(15)
)";

mysql_query($sql,$con);

//build the word table

$file=file('words5.txt');

if(is_array ($file))
{
  foreach($file as $v)
  {
    //query
    $sql=mysql_query("INSERT INTO 'Words' ('word') VALUES ('".mysql_real_escape_string($v)."'')");
  }
}
else
{
  echo 'File not an array.';
}




mysql_close($con);
?>

[mrMarcus]

you don't need to change the code i gave you:

 

change this line:

 

$sql=mysql_query("INSERT INTO 'Words' ('word') VALUES ('".mysql_real_escape_string($v)."'')");

 

to:

 

$sql = mysql_query("INSERT INTO `Words` (`word`) VALUES ('".mysql_real_escape_string($v)."')") or trigger_error (mysql_error());

 

NOTE the backticks ` surrounding `Words` and (`word`) .. don't replace those with single-quotes.

 

and you should only create the table one time outside of the script, or at least have a clause where if the table is already created, do not try to create it again.

 

and, is 15 characters for `word` field enough?  think about upping that to 50 or something if there is the chance of having longer words, as this will cause the query not to run.

[husslela03]

it worked! Thank you so much! I am grateful.

[husslela03]

do you know how to in mySQL how to make a random selection from this table now?

 

Like, I want to select a random value ...

 

 

[mrMarcus]

simple.

 

<?php
//ORDER BY RAND() is taking a random result form the db;
$sql = mysql_query ("
SELECT `field1`
FROM `table`
ORDER BY RAND()
") or trigger_error ('Error: ' . mysql_error());
?>

 

NOTE: RAND() can be a resource hog on large datasets, so, if you have a db/table with thousands of records, RAND() is not the most optimal and can decrease performance significantly.  thousands, might be hundreds of thousands, not 100% sure.  however, if you're talking about a table with < 1000 records/entries, you should not be affected.

 

as an extra step to ensure performance, create an index related to the SELECT query you are going to use in conjunction with RAND() for improved performance.  if you need help with creating indexes, head over the MySQL Help forum.