Page 1 of 1

Strange problem with a join

Posted: Tue Oct 08, 2002 12:35 am
by DSM
I have a site where there are two separate mailing lists, one for free subscribers and one for paying. I am able to send a newsletter to either list, prior to doing that I run a test to see how many addresses are on each list, a simple $mysql_numrows($sql); Each returns the proper number, ahhh, but when I combine the two lists instead of the proper amount of rows being returned (which is 95, 9 from free list, 86 from paid) I get 774.
heres the code, tell me what you see.

Code: Select all

<?php
	if($_POSTїwho] == 1):
	$sql = "select email from mailinglist"; //9 on this mailing list
	elseif($_POSTїwho] == 2):
	$sql = "select email from members"; //86 on this mailing list
	elseif($_POSTїwho] == 3):
	$sql = "select mailinglist.email, members.email from mailinglist,members"; //should return 95 but returns 774
	endif;
	
	$res = mysql_query($sql) or die ("Could not select email addresses.");

	$num_rows = mysql_numrows($res);
	//print"$num_rows";
	$count = 1;
	
	while ($count <= $num_rows):
	$row = mysql_fetch_array($res);
	
	$email = $rowї"email"];
	
	mail("$email", "$_POSTїsubject]", $body, $headers);
		
	$count = $count + 1;
	endwhile;

?>
As always any help is greatly appreciated

Posted: Tue Oct 08, 2002 3:38 am
by mikeq
9 * 86 = 774 - i.e. a cartesian join, all records in 1 table joined to every record in the second table.

You forgot to put a join condition between the tables, if there is no relation between the tables then you will need to do it as 2 seperate queries, unless you are using mysql v4 as I think it now supports UNION queries.

Posted: Tue Oct 08, 2002 5:06 am
by Coco
wouldnt it be easier to have them all in one table?

Code: Select all

<?php

   if($_POSTїwho] == 1): 
   $sql = "select email from members where paid = NO"; //9 on this mailing list 
   elseif($_POSTїwho] == 2): 
   $sql = "select email from members where paid = YES"; //86 on this mailing list 
   elseif($_POSTїwho] == 3): 
   $sql = "select email from members"; //should return 95 but returns 774 
   endif; 

?>
well thats how i would do it, if there are special fields for either that the other doesnt have then null em :)