Page 1 of 2

Using an external text file to create array ...

Posted: Wed Dec 06, 2006 7:59 am
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 ...

Posted: Wed Dec 06, 2006 8:51 am
by kiajoe777
No input on this one at all?? :( I thought this would be relatively easy and that I was just missing something stupid ...

Posted: Wed Dec 06, 2006 9:15 am
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.

Posted: Wed Dec 06, 2006 9:22 am
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 ...

Posted: Wed Dec 06, 2006 9:43 am
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:

Posted: Wed Dec 06, 2006 9:57 am
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)) {

Posted: Wed Dec 06, 2006 10:01 am
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

Posted: Wed Dec 06, 2006 10:02 am
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 ...

Posted: Wed Dec 06, 2006 10:12 am
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

Posted: Wed Dec 06, 2006 10:16 am
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

Posted: Wed Dec 06, 2006 10:16 am
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. :)

Posted: Wed Dec 06, 2006 10:17 am
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

Posted: Wed Dec 06, 2006 10:19 am
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 ...

Posted: Wed Dec 06, 2006 10:24 am
by evilchris2003
have you tried something like this

Code: Select all

"SELECT id FROM Customers WHERE (EmailAddress='$emails')"

Posted: Wed Dec 06, 2006 10:37 am
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 ...