Page 1 of 1

Selecting distinct data with mysql

Posted: Thu Feb 15, 2007 5:23 am
by mattcooper
Hi all,

I'm trying to build a simple newsletter mailer with multi list capability. The author checks boxes to specify which lists should receive their email and the data is then posted to a script that compiles a queue.

At the moment, I'm using a set of queries to place data into a "buffer" table that will have duplicate addresses in it, then move distinct email addresses along with other data associated with it into the main queue, where it will be sent by cron later.

I think that this is rather a long-winded approach to a simple set of tasks which are as follows:

1. For each checkbox providing a list id, grab data about people who have signed up to it, but...
2. Exclude duplicates (if users are subscribed to more than one list, they'll appear multiple times in the subscribers table)
3. Place the resultant, duplicate-free array of user info into the queue table

Currently, the php script to acheive this is rather bulky and, if I don't use ini_set() to extend the max_execution_time config, the server times out if the queue is big.

Using

Code: Select all

SELECT DISTINCT * FROM mailinglist_subscribers WHERE list_id = "$list_id"
doesn't do what I want.

I need to select a distinct email address where the user is subscribed to at least one of the (potentially) several list ids that have been posted. Is "distinct" the way to go, or can this be achieved with "limit", or is there a better way?

Thank in advance for your help!

Posted: Thu Feb 15, 2007 5:46 am
by volka
try

Code: Select all

$mailinglist_ids = '1,2,5,8';
$query = "SELECT DISTINCT * FROM mailinglist_subscribers WHERE list_id IN ($mailinglist_ids)";

Posted: Thu Feb 15, 2007 6:02 am
by mattcooper
Thanks. But wouldn't that have the effect I'm trying to avoid - resultant duplicates?

This is the way I'm looking into this at the moment:

Code: Select all

foreach($list as $list) {
		
		$q = "select distinct `address`,`firstname`,`list_id`,`confirmed` 
			  from `mailinglist_subscribers` 
			  where `list_id` = '$list' 
			  and `confirmed` = '1' 
			  order by `address` asc";
			  
		$r = mysql_query($q);
		while($row = mysql_fetch_assoc($r)) {
			
			# insert into the mailinglist_queue table
			mysql_query("insert into `mailinglist_queue`
						 values (
						 	'',
							'$row[list_id]',
							'$message_id',
							'$row[firstname]',
							'$row[address]',
							'$row[confirmed]')")
							
			or die("Can't build the queue because ".mysql_error());
			
		}
	}
But it still seems to require a buffer table to strip duplicates, which do exist in the queue table. I'm trying to ensure that recipients are signed up to al of the lists specified, but only get one email.

Make sense?

Posted: Thu Feb 15, 2007 6:17 am
by volka
But wouldn't that have the effect I'm trying to avoid - resultant duplicates?
No, that's why there's a DISTINCT in the query.
And there's no need for a temporary table.