Page 1 of 1

[SOLVED] Importing Text Into MySQL

Posted: Thu Jul 22, 2004 12:37 am
by furiousweebee
I've made a text file containing a list of email addresses. It has no fields or anything, just an address on one line, then the next address on the next line. Below is the code I'm using at present, but I just see a blank screen when I load the file in my browser.

Code: Select all

<?php
$handle = fopen('addresses.txt', 'r');

// DB connection stuff
$hostname = "localhost";
$database = "******";
$username = "******";
$password = "******";
$connect = $dblink = mysql_connect($hostname, $username, $password or die(mysql_error());
mysql_select_db($database, $connect);

// Just so we can append to it later
$query = 'INSERT INTO `mailing_list` (email) VALUES ';

// Read from the file one line at a time. "Do the dirty work"
while (!feof($handle)) {
   $data = rtrim(fgets($handle));
   // Don't waste time with the NULL stuff, as in the automatic new line at the end of the file
   if($data == NULL){
   // Don't include it as data by breaking out of the while
   break;
   }
   // Builds ONE query, much better to do if you have a large list to enter
   $query .=  '(''' . $data .'''), ';
}
// We're done reading from the file, so close it
fclose($handle);
// Chop off the dangling comma and whitespace of the query
$finalQuery = substr($query, 0, -2);
// If you want to, I would do this first with the actual query execution code commented out:
echo $finalQuery;
// Finally, run the query when all is good:
$result = mysql_query($finalQuery);
if($result) {
   echo 'Success!';
} else {
   echo '<pre>', var_dump($finalQuery), '</pre><br /><br />Something went wrong: ', mysql_error();
}
?>

Posted: Thu Jul 22, 2004 12:43 am
by feyd
you have a parse error.. you're missing a right paren ')' after $password in the connect line..

secondly, your query will probably fail due to multiple values being passed, with only 1 insert line. You'll need to alter it to insert each one individually by moving the query call into the loop.

Posted: Thu Jul 22, 2004 12:49 am
by furiousweebee
Ok, well I changed those things now, but it's still a blank page:

Code: Select all

$connect = $dblink = mysql_connect($hostname, $username, $password or die(mysql_error()); ) // stupid smilies, had to put a space there 
mysql_select_db($database, $connect);

// Read from the file one line at a time. "Do the dirty work"
while (!feof($handle)) {
   $data = rtrim(fgets($handle));
   // Don't waste time with the NULL stuff, as in the automatic new line at the end of the file
   if($data == NULL){
   // Don't include it as data by breaking out of the while
   break;
   }
   // Builds ONE query, much better to do if you have a large list to enter
   $query = 'INSERT INTO `mailing_list` (email) VALUES ';
   $query .=  '(''' . $data .'''), ';

Posted: Thu Jul 22, 2004 12:51 am
by feyd

Code: Select all

mysql_connect($hostname, $username, $password) or die(mysql_error());

Posted: Thu Jul 22, 2004 12:54 am
by furiousweebee

Code: Select all

mysql_connect($hostname, $username, $password) or die(mysql_error());
mysql_select_db($database, mysql_connect);
Error message:

Code: Select all

INSERT INTO `mailing_list` (email) VALUES ('4023192059@xxxxxx.net')
string(71) "INSERT INTO `mailing_list` (email) VALUES ('4023192059@xxxxxx.net')"

Something went wrong: No Database Selected

Posted: Thu Jul 22, 2004 12:56 am
by feyd

Code: Select all

mysql_select_db($database);

Posted: Thu Jul 22, 2004 12:58 am
by furiousweebee
Ok, it worked, but it only put in the last line of the text file.

Posted: Thu Jul 22, 2004 12:59 am
by feyd
you need to move mysql_query() into the loop.

Posted: Thu Jul 22, 2004 1:02 am
by furiousweebee
Now it just put the first line in:

Code: Select all

<?php
$handle = fopen('addresses.txt', 'r');

$hostname = "localhost";
$database = "****";
$username = "****";
$password = "****";
mysql_connect($hostname, $username, $password) or die(mysql_error());
mysql_select_db($database);


while (!feof($handle)) {
   $data = rtrim(fgets($handle));
 
  if($data == NULL){

   break;
   }
 
   $query = 'INSERT INTO `mailing_list` (email) VALUES ';
   $query .=  '(''' . $data .'''), ';

fclose($handle);

$finalQuery = substr($query, 0, -2);

echo $finalQuery;

$result = mysql_query($finalQuery);
}
if($result) {
   echo '<br><b>Success!</b>';
} else {
   echo '<pre>', var_dump($finalQuery), '</pre><br /><br />Something went wrong: ', mysql_error();
}
?>

Posted: Thu Jul 22, 2004 1:12 am
by feyd
move the fclose() call outside the loop.

Posted: Thu Jul 22, 2004 1:25 am
by furiousweebee
That worked.... you sir, are a genius. Thank you very much!!

Now how about that engagement ring we talked about, honey? :oops: