Using an external text file to create array ...

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

kiajoe777
Forum Newbie
Posts: 12
Joined: Wed Dec 06, 2006 7:50 am

Using an external text file to create array ...

Post by kiajoe777 »

Here is what I'm trying to do ...

I am attempting to call an external file and creating an array with it ... then feed that data into a mysql query, one at a time, and get the results from the query ...

Here's the code I'm trying to use to do it ...

Code: Select all

<?php
	$dbhost= 'localhost';
	$dbusername = 'user';
	$dbpassword = 'pass';
	$dbname = 'db';
	$conn = mysql_connect($dbhost,$dbusername,$dbpassword);
	mysql_select_db($dbname, $conn);
	$lines = file('/home/user/public_html/test.txt');
	foreach($lines as $data) {
		$lists_result = mysql_query("SELECT ID FROM Email WHERE EmailAddress = '$data'", $conn);
		$lists_data = mysql_fetch_array($lists_result);
		echo $lists_data[ID];
		}
	}; 
?>
The text file looks like this ...

Code: Select all

email@address1.com
email@address2.com
email@address3.com
etc ...
Now, it seems to work fine in terms of that all the queries run as I can see them in the process table ... but it ends up just passing the last queries results and that's all that echos ...

What in the world am I doing wrong?? Any help would be appreciated ...
kiajoe777
Forum Newbie
Posts: 12
Joined: Wed Dec 06, 2006 7:50 am

Post by kiajoe777 »

No input on this one at all?? :( I thought this would be relatively easy and that I was just missing something stupid ...
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Now, it seems to work fine in terms of that all the queries run as I can see them in the process table ... but it ends up just passing the last queries results and that's all that echos ...
I'm not sure what your having problems with, could you rephrase please.
kiajoe777
Forum Newbie
Posts: 12
Joined: Wed Dec 06, 2006 7:50 am

Post by kiajoe777 »

I want it to echo the ID that is associated with each email address that is in the text file ...

What is happening is that the foreach loop seems to be just running all the queries and then just passing the results from the last query in the text file ... so I would just get the ID echoed for "email@address3.com" and not from the previous ones ...

All the queries do run as I can see them in the mysql process table ... but like I said, I only get the last result echoed ...
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

$dbhost= 'localhost';
   $dbusername = 'user';
   $dbpassword = 'pass';
   $dbname = 'db';
   $conn = mysql_connect($dbhost,$dbusername,$dbpassword);
   mysql_select_db($dbname, $conn); 

   $emails = implode(',', file('/home/user/public_html/test.txt'));

   $result = mysql_query("SELECT ID FROM Email WHERE EmailAddress IN (".$emails.")", $conn);
   
   while ($lists_data = mysql_fetch_assoc($result)) {
      echo $lists_data['id'];
   }
No point on running a whole wack of queries when this can be accomplished in a single query :wink:
kiajoe777
Forum Newbie
Posts: 12
Joined: Wed Dec 06, 2006 7:50 am

Post by kiajoe777 »

When using your script I get the following:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /data/home/user/public_html/test.php on line 21

Line 21 is:

Code: Select all

while ($lists_data = mysql_fetch_assoc($result)) {
User avatar
evilchris2003
Forum Contributor
Posts: 106
Joined: Sun Nov 12, 2006 6:43 am
Location: Derby, UK

Post by evilchris2003 »

that error arises from the statement having no results

echo you query to the screen to see if its correct well what your expecting anyway
kiajoe777
Forum Newbie
Posts: 12
Joined: Wed Dec 06, 2006 7:50 am

Post by kiajoe777 »

And the other problem is that I'm doing this for about 250,000 (yes thousand) email addresses ... so I don't think that will work for me ...
User avatar
evilchris2003
Forum Contributor
Posts: 106
Joined: Sun Nov 12, 2006 6:43 am
Location: Derby, UK

Post by evilchris2003 »

if you want to limit the query i suggest using extra parameters like the domain from the email address if there are several different ones
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

kiajoe777 wrote:When using your script I get the following:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /data/home/user/public_html/test.php on line 21

Line 21 is:

Code: Select all

while ($lists_data = mysql_fetch_assoc($result)) {
try changing the query line to

Code: Select all

$result = mysql_query("SELECT ID FROM Email WHERE EmailAddress IN (".$emails.")", $conn) or die(mysql_error());
and see what mysql is telling us
kiajoe777
Forum Newbie
Posts: 12
Joined: Wed Dec 06, 2006 7:50 am

Post by kiajoe777 »

Nope, don't want to limit the query ... I just need the correct results ...

BTW ... These are valid subscribers ... nothing to do with spam. :)
kiajoe777
Forum Newbie
Posts: 12
Joined: Wed Dec 06, 2006 7:50 am

Post by kiajoe777 »

The error is as follows:

Code: Select all

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@AOL.COM ,ANANGEL4EVRY1TOC@AOL.COM)' at line 1
kiajoe777
Forum Newbie
Posts: 12
Joined: Wed Dec 06, 2006 7:50 am

Post by kiajoe777 »

This is what the syntax of an "IN" should be according to mysql ...

Code: Select all

SELECT 'wefwf' IN ('wee','wefwf','weg');
But I'm not sure how to obtain that ...
User avatar
evilchris2003
Forum Contributor
Posts: 106
Joined: Sun Nov 12, 2006 6:43 am
Location: Derby, UK

Post by evilchris2003 »

have you tried something like this

Code: Select all

"SELECT id FROM Customers WHERE (EmailAddress='$emails')"
kiajoe777
Forum Newbie
Posts: 12
Joined: Wed Dec 06, 2006 7:50 am

Post by kiajoe777 »

evilchris2003 wrote:have you tried something like this

Code: Select all

"SELECT id FROM Customers WHERE (EmailAddress='$emails')"
That doesn't seem to be doing the trick either ...
Post Reply