Page 1 of 1
php/mysql question
Posted: Tue Nov 11, 2008 6:32 am
by romanudwo
i have a table where i keep list of domain names that have problems receiving our emails. then we have a table where we have user details with their primary and secondary email addresses. i need to do a check of the email addresses against the first table and if the user we are trying to contact is with problematic email address it should send the email to our admin to send manually, otherwise the email can be send by the server automatically. i think iu have to first build an array from the first table but then what? any help is appreciated.
Re: php/mysql question
Posted: Tue Nov 11, 2008 6:41 am
by VladSun
You can directly extract this list with a more complicated SQL query. Take a look at
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
I think you'll find SUBSTRING_INDEX function to be useful

Then you need a INNER JOIN when substring (i.e. the domain) is found (in user's email address).
Re: php/mysql question
Posted: Tue Nov 11, 2008 11:09 pm
by romanudwo
I am not really sure how to go about your suggestion. i looked at the suggested function but could not figure out how to make it work for my situation. any other suggestions/samples?
Thank you
Re: php/mysql question
Posted: Wed Nov 12, 2008 1:27 am
by VladSun
[sql]SELECT user.mailFROM userINNER JOIN problematic_domain ON substring_index(user.email, '@', -1) = problematic_domain.name[/sql]
While this will work for small DB, I do encourage you to reconsider your DB design for a larger one. That's because you can't use an index on function results.
I think a third table which references both user and problematic_domain tables will be better.