-1

i have 2 tables customers and imap_emails. customer table contain emails of customers.

I am using PHP-IMAP to fetch email from email server and then saving to database table imap_emails.

the imap_emails table has 2 fields from and to and to field contains comma separated values.

i need to fetch emails from first table and then search against to and from on imap_emails.

First i thought about a LIKE condition to search , but i would like to have something like FIND_IN_SET or something other.

How can i achieve this in a better way ? ( For some reasons i cannot use relations on this table )

Please advice.

Red
  • 6,230
  • 12
  • 65
  • 112
  • 2
    " field contains comma separated values." red flag, normalise the db first –  Apr 25 '13 at 07:18
  • @Dagon How can i normalize this table ?? its just emails and it reading from an external application. I do not have the proper relation to keep. – Red Apr 25 '13 at 07:21
  • The search is based on `Customer_ID`. But i will not get the `ID` while inserting to the table. I will only get one email address to search against the imap server. Then how can i create a key ?? – Red Apr 25 '13 at 07:27
  • why can't you use FIND_IN_SET ?? What's the draw back you are finding ?? – sandy Apr 25 '13 at 07:47
  • @sandy Could you please explain how can i use `FIND_IN_SET` to check any of the value in a comma separated string exists on a table field ? – Red Apr 25 '13 at 08:46
  • FIND_IN_SET will return the records with the email you search for, and you can use those result with IN to check if it exists or not – sandy Apr 25 '13 at 08:49
  • @sandy its looks likes you are misunderstanding te question. If i have only one email to search against comma separated value, then its fine. But i am already mention that i have multiple emails address that need to search against the comma-separated values. – Red Apr 25 '13 at 08:55
  • 1
    FIND_IN_SET will work (although it will likely be slow). You could use multiple FIND_IN_SET statements in the WHERE clause, one for each email address you are searching for. It would be better to extract the email addresses when you insert / update to the table and then put each one onto their own row on another table. – Kickstart Apr 25 '13 at 08:55
  • Alternative for searching multiples would be to create a temp table, insert your list of email addresses to search for into the temp table, then do a JOIN based on FIND_IN_SET on the email address between your main table and your temp table. – Kickstart Apr 25 '13 at 08:59
  • 1
    i understand your question, you have bunch of emails in "from" and you want to search in "to" (which is comma separated), you wanted to push all emails at one shot and give you the resultant, the same you can do from PHP by sending them in a loop – sandy Apr 25 '13 at 09:00
  • @Kickstart Agree. #at Sandy How can i do this with PHP ? i need to return the results first :) let me try .. – Red Apr 25 '13 at 09:03

1 Answers1

2

Example of doing a join based on FIND_IN_SET

SELECT *
FROM imap_emails
INNER JOIN customers
ON FIND_IN_SET(customers.email, imap_emails.to) > 0

You don't say how you want to narrow this down (ie, if an email uses 2 email addresses do you want both emails brought back), so can't add much at this stage.

Kickstart
  • 21,403
  • 2
  • 21
  • 33