Selecting distinct data with mysql

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
User avatar
mattcooper
Forum Contributor
Posts: 210
Joined: Thu Mar 17, 2005 5:51 am
Location: London, UK

Selecting distinct data with mysql

Post 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!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

try

Code: Select all

$mailinglist_ids = '1,2,5,8';
$query = "SELECT DISTINCT * FROM mailinglist_subscribers WHERE list_id IN ($mailinglist_ids)";
User avatar
mattcooper
Forum Contributor
Posts: 210
Joined: Thu Mar 17, 2005 5:51 am
Location: London, UK

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
Post Reply