62683

MySQL & PHP: Send emails to the members who fall into last 3 active threads

Question:

My query below will get the last 3 active threads from my blog's commnets,

SELECT ID, Approved, RecipientID, RecipientScreenname, RecipientEmail FROM ( SELECT root_strings.str_id as ID, root_strings.str_approved as Approved, root_strings.mem_id as RecipientID, root_members_cfm.mem_screenname as RecipientScreenname, root_members_cfm.mem_firstname as RecipientFirstname, root_members_cfm.mem_email as RecipientEmail FROM root_strings LEFT JOIN root_members_cfm ON root_members_cfm.mem_id = root_strings.mem_id WHERE root_strings.parent_id = '1' AND root_strings.mem_id IS NOT NULL UNION ALL SELECT root_strings.str_id as ID, root_strings.str_approved as Approved, root_strings.mem_id as RecipientID, root_users.usr_screenname as RecipientScreenname, root_users.usr_firstname as RecipientFirstname, root_users.usr_email as RecipientEmail FROM root_strings LEFT JOIN root_users ON root_users.usr_id = root_strings.usr_id WHERE root_strings.parent_id = '1' AND root_strings.usr_id IS NOT NULL ) SQ ORDER BY ID DESC LIMIT 0,3

It returns a result like this,

ID Approved RecipientID RecipientScreenname RecipientEmail 14 1 3 x x@yahoo.co.uk 13 n/a NULL y y@yahoo.co.uk 13 n/a NULL y y@yahoo.co.uk

Then I will an email to each of them.

foreach($items_thread as $item_thread) { $sentmail = mail($item_thread['RecipientEmail'],$email_subject,$email_content,$email_headers); }

But the logic is not correct when you look closer as I will send y twice of the email!

y should just get one email. How can I fix it - should I fix the sql query or the php code?

Answer1:

The problem is that NULL!=NULL (nor is NULL=NULL), so DISTINCT does not consider the 2nd and 3rd results to be the same. If you change your query so that RecipientID shows as 0 (e.g COALESCE(RecipientID,0) AS RID) the problem will go away.

Answer2:

I have a solution for this now which is using array_unique, here it is <a href="https://stackoverflow.com/questions/5036403/php-remove-duplicate-items-in-an-array" rel="nofollow">Remove duplicate items from an array</a>

Recommend

  • Retrieve Records from Quickblox API
  • MySQL & PHP: Send emails to the members who fall into last 3 active threads
  • Tree Structure (Foreign Keys to itself) and templates
  • Build tree from parent_id id table structure
  • Postgresql and Oracle: update multiple tables from common subquery
  • Use Ruby closure_tree gem without rails
  • In JPA, having a many-to-one as primary key throws referential integrity constraint violation
  • Django Rest Framework nested object response
  • Defining table name for has_many self joins in rails?
  • MySQL Error 2014 - Commands out of sync - when trying to call consecutive stored procs
  • Building whole sheet programmatically with Python SDK
  • make a query, get results make query again
  • Find all matches of a regex in Java [duplicate]
  • Can Someone hello explain this class for a php threaded comments system?
  • Pagination in Hibernate with joined tables
  • Yii2 How to hide sub-menu items from other users
  • how to generating a dropdown list in yii2
  • how to use python's Request library to make an API call with an attachment and a parameter
  • Jquery Filter next select group based on selection of previous select group
  • How to display categories and end-less sub categories using PHP & MySQL?
  • Remove an item from datalist
  • insert from array has multi levels to database using php
  • How to increase performance in sql query?
  • How do I structure a SQL query to find an object that is the parent of two specific other objects?
  • BigQuery SQL: Average, geometric mean, remove outliers, median
  • Dynamically building horizontal menu
  • Oracle SQL: SQL query producing 4 times the results and data wont order correctly
  • How to update parent table timestamp when child table is modified?
  • Android parse Object KSOAP
  • How to make “Tree Dropdown” in codeigniter?
  • Include external XML ENTITIES using SYSTEM while resolving path with $ENV variable
  • Error with old code TYPE=HEAP for temporary table in mysql
  • In data Insert script how to put parent record's id in child record?
  • Symfony2 - how to print joined tables output
  • How to write an INSTEAD OF INSERT trigger on a multi table view that works with identities?
  • Sails.js associations
  • cakephp model association/join with same table [closed]
  • override relationship behaviour in sqlalchemy
  • PostgreSQL: update with left outer self join ignored
  • Wiring top-level DAGs together