Strange problem with a join

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
DSM
Forum Contributor
Posts: 101
Joined: Thu May 02, 2002 11:51 am
Location: New Mexico, USA

Strange problem with a join

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post 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 :)
Post Reply