[SOLVED] Importing Text Into MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
furiousweebee
Forum Commoner
Posts: 69
Joined: Sun Jul 11, 2004 7:38 am
Location: Brisbane, Australia
Contact:

[SOLVED] Importing Text Into MySQL

Post 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();
}
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
furiousweebee
Forum Commoner
Posts: 69
Joined: Sun Jul 11, 2004 7:38 am
Location: Brisbane, Australia
Contact:

Post 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 .'''), ';
Last edited by furiousweebee on Thu Jul 22, 2004 12:51 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

mysql_connect($hostname, $username, $password) or die(mysql_error());
furiousweebee
Forum Commoner
Posts: 69
Joined: Sun Jul 11, 2004 7:38 am
Location: Brisbane, Australia
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

mysql_select_db($database);
furiousweebee
Forum Commoner
Posts: 69
Joined: Sun Jul 11, 2004 7:38 am
Location: Brisbane, Australia
Contact:

Post by furiousweebee »

Ok, it worked, but it only put in the last line of the text file.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you need to move mysql_query() into the loop.
furiousweebee
Forum Commoner
Posts: 69
Joined: Sun Jul 11, 2004 7:38 am
Location: Brisbane, Australia
Contact:

Post 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();
}
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

move the fclose() call outside the loop.
furiousweebee
Forum Commoner
Posts: 69
Joined: Sun Jul 11, 2004 7:38 am
Location: Brisbane, Australia
Contact:

Post by furiousweebee »

That worked.... you sir, are a genius. Thank you very much!!

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