php/mysql question

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
romanudwo
Forum Newbie
Posts: 2
Joined: Tue Nov 11, 2008 6:27 am

php/mysql question

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: php/mysql question

Post 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).
There are 10 types of people in this world, those who understand binary and those who don't
romanudwo
Forum Newbie
Posts: 2
Joined: Tue Nov 11, 2008 6:27 am

Re: php/mysql question

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: php/mysql question

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply