[SOLVED] Resource ID #6 message

[ainoy31]

Hello-

 

I get the resource id #6 when executing the following statement.  Here is my code:

 

$sql = "SELECT usr.* FROM ml_users usr INNER JOIN ml_subscriptions mls " .

"ON usr.user_id = mls.user_id WHERE mls.pending = 0";

 

if($_POST['ml_id'] != 'all')

{

$sql .= " AND mls.ml_id = " . $_POST['ml_id'];

}

 

$result = mysql_query($sql) or die('Could not get list of email addresses. ' . mysql_error());

 

$headers = "FROM: "  . ADMIN_EMAIL . "\r\n";

 

while ($row = mysql_fetch_array($result))

{

echo $row['user_email'];

}

 

any suggestions would be appreciated.

 

 

[php_dave]

Does mysql_fetch_array default to ASSOC?  i would change it to mysql_fetch_assoc just to be sure this isnt the problem.

 

[teng84]

i guess fetching is not the issue check your query i believe its the query that has a problem

maybe you get that resource error because theres a prob on your sql syntax try to query it in your db and check it that works on your db

[ainoy31]

OK.  Here are my tables:

 

Table - ml_users

user_id int(11) NOT NULL auto_increment,

user_fname varchar(32) NOT NULL,

user_lname varchar(32) NOT NULL,

user_email varchar(255) NOT NULL,

PRIMARY KEY (user_id)

--------------------------------------

Table - ml_subscriptions

ml_id int(11) NOT NULL default '0',

user_id int(11) NOT NULL default '0',

pending tinyint(1) NOT NULL default '1',

PRIMARY KEY (ml_id, user_id)

----------------------------------------

Table - ml_lists

ml_id int(11) NOT NULL auto_increment,

listname varchar(255) NOT NULL,

PRIMARY KEY (ml_id)

 

I changed my query to:

$sql = "SELECT ml_users.user_email FROM ml_users, ml_subscriptions, ml_lists WHERE ml_users.user_id = ml_subscriptions.user_id AND ml_subscriptions.ml_id = ml_lists.ml_id AND ml_subscriptions.pending = 0 AND ml_lists.ml_id = " . $_POST['ml_id'];

 

$result = mysql_query($sql);

$num = mysql_num_rows($result);

 

I echo $num and returns 0.  I know there is 4 records there using the count method.  I should have 4 email addressed returned.  Any suggestion is much appreciated.  TY

 

 

[ainoy31]

I figure out the issue in my query.  It is at the ml_subscriptions.pending = 0.  When I change the value to 1, it displays the email addresses and then sends an email to those addresses.  Thanks for all the help.