A better way to get MYSQL result??

[hjshy]

I'm sure there must be a better way to get the results I'm after then what I've done...

 

I want the query to select the s_id and s_email fields from table 'subscribers', then to cross check the table 'Newsletter_Subscribers' to make sure the s_id does not appear in there and count how many results there are.

 

  $query1 = mysql_query("SELECT s_id, s_email FROM subscribers" );
  while($qry1 = mysql_fetch_array($query1)) { 

     $query2 = mysql_query("SELECT * FROM Newsletter_Subscribers
                                          WHERE id_subscriber<>".$qry1['s_id'] )  ;
  }

 

I apologise if this is painful to the eye, everything's self taught, so any advice would be really appreciated.

 

 

(MySQL version: 5.0.92)

[AyKay47]

Select subscriber.s_id, subscribers.s_email from subscribers join newsletter_subscribers on newsletter_subscribers.id_subscriber != subscribers.s_id

 

You want to use a join here, never query a database inside of a loop.

[kickstart]

Hi

 

Further to the above, if you want the counts then something like this:-

 

SELECT subscriber.s_id, subscribers.s_email, COUNT(Newsletter_Subscribers)
FROM subscribers 
INNER JOIN newsletter_subscribers 
ON newsletter_subscribers.id_subscriber != subscribers.s_id
GROUP BY subscriber.s_id, subscribers.s_email

 

Although logically it seems strange. You appear to be getting a list of subscribers and then getting a count of all the newsletter subscribers who are not that subscriber.

 

All the best

 

Keith