Need help with email-match query

I'm trying to figure out a MySQL query expression to match an email
address. Here's the situation:

User registers on my site with a "plus style" email address
(username+key@example.com). This is a perfectly legal address and I
don't want to disallow it, because I use this style myself and find
it useful. For example user+whatever@gmail.com will get delivered
to user@gmail.com.

Now, the user forgets his password. He is prompted to enter his
email address, to which my site will send a temporary password.

Problem: if he enters username@example.com instead of the address
username+key@example.com that's stored in my database, I should be
able to find the address. How do I construct a MySQL query to match
what the user enters with the "plus" address in the database?

The simplest way is to strip the +suffix from the username when the user
first registers on my site, but I'd rather not do that.

-A

Re: Need help with email-match query

axlq wrote:
> I'm trying to figure out a MySQL query expression to match an email
> address. Here's the situation:
>
> User registers on my site with a "plus style" email address
> (username+key@example.com). This is a perfectly legal address and I
> don't want to disallow it, because I use this style myself and find
> it useful. For example user+whatever@gmail.com will get delivered
> to user@gmail.com.
>
> Now, the user forgets his password. He is prompted to enter his
> email address, to which my site will send a temporary password.
>
> Problem: if he enters username@example.com instead of the address
> username+key@example.com that's stored in my database, I should be
> able to find the address. How do I construct a MySQL query to match
> what the user enters with the "plus" address in the database?
>
> The simplest way is to strip the +suffix from the username when the user
> first registers on my site, but I'd rather not do that.
>
> -A

MySQL questions should be asked in comp.databases.mysql. This is a PHP
newsgroup.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Need help with email-match query

On 11.09.2007 04:10 axlq wrote:
> I'm trying to figure out a MySQL query expression to match an email
> address. Here's the situation:
>
> User registers on my site with a "plus style" email address
> (username+key@example.com). This is a perfectly legal address and I
> don't want to disallow it, because I use this style myself and find
> it useful. For example user+whatever@gmail.com will get delivered
> to user@gmail.com.
>
> Now, the user forgets his password. He is prompted to enter his
> email address, to which my site will send a temporary password.
>
> Problem: if he enters username@example.com instead of the address
> username+key@example.com that's stored in my database, I should be
> able to find the address. How do I construct a MySQL query to match
> what the user enters with the "plus" address in the database?
>
> The simplest way is to strip the +suffix from the username when the user
> first registers on my site, but I'd rather not do that.
>
> -A

hi

you can use mysql regular expressions, e.g.

select * from users where email rlike 'user([+][^@]+)?@example[.]com'

or, if you need this query often create an extra "canonical email"
column in the database.



--
gosha bine

makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi

Re: Need help with email-match query

In article ,
Jerry Stuckle wrote:
>
>MySQL questions should be asked in comp.databases.mysql. This is a PHP
>newsgroup.

....which is about a language that integrates with mySQL. Not
everyone (including me) gets comp.databases.mysql. I didn't even
know that group existed until just now.

If you didn't want to answer the question, why did you bother replying?

-A

Re: Need help with email-match query

In article <46e64fd4$0$31120$6e1ede2f@read.cnntp.org>,
gosha bine wrote:
>
>you can use mysql regular expressions, e.g.
>
>select * from users where email rlike 'user([+][^@]+)?@example[.]com'

Cool. That's just what I wanted to know. I feared the answer would
involve something inefficient like reading in the entire column and
processing it in php.

>or, if you need this query often create an extra "canonical email"
>column in the database.

I was thinking of that solution too, but adding a redundant column,
didn't seem as elegant as constructing a query to work with what I
have.

-A

Re: Need help with email-match query

..oO(axlq)

>Jerry Stuckle wrote:
>>
>>MySQL questions should be asked in comp.databases.mysql. This is a PHP
>>newsgroup.
>
>...which is about a language that integrates with mySQL. Not
>everyone (including me) gets comp.databases.mysql. I didn't even
>know that group existed until just now.

If that group is not available on your newsserver you should ask the
server admin for adding it. If they don't want that, you should find a
better newsserver.

Micha

Re: Need help with email-match query

In article ,
Michael Fesser wrote:
>If that group is not available on your newsserver you should ask
>the server admin for adding it. If they don't want that, you should
>find a better newsserver.

My newsserver is a part of my hosting service. My admin will add
new newsgroups any customers ask for. I just didn't know it existed
in the first place.

-A

Re: Need help with email-match query

axlq wrote:
> In article ,
> Jerry Stuckle wrote:
>> MySQL questions should be asked in comp.databases.mysql. This is a PHP
>> newsgroup.
>
> ...which is about a language that integrates with mySQL. Not
> everyone (including me) gets comp.databases.mysql. I didn't even
> know that group existed until just now.
>
> If you didn't want to answer the question, why did you bother replying?
>
> -A

Because this is a PHP group, not a MySQL group. PHP interfaces with
Apache and Linux, also. So from your argument Apache and Linux
questions should be asked in this group, also.

That's why there are multiple groups. And if your news server doesn't
have comp.databases.mysql, ask them to get it. Or use Google Groups.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Need help with email-match query

"Jerry Stuckle" wrote in message
news:AKadnVnU3fUCzHrbnZ2dnUVZ_hudnZ2d@comcast.com...
> axlq wrote:
>> In article ,
>> Jerry Stuckle wrote:
>>> MySQL questions should be asked in comp.databases.mysql. This is a PHP
>>> newsgroup.
>>
>> ...which is about a language that integrates with mySQL. Not
>> everyone (including me) gets comp.databases.mysql. I didn't even
>> know that group existed until just now.
>>
>> If you didn't want to answer the question, why did you bother replying?
>>
>> -A
>
> Because this is a PHP group, not a MySQL group. PHP interfaces with
> Apache and Linux, also. So from your argument Apache and Linux questions
> should be asked in this group, also.
>
> That's why there are multiple groups. And if your news server doesn't
> have comp.databases.mysql, ask them to get it. Or use Google Groups.

amen. however, we should do him a favor and explain why he's going to be
laughed at in any db ng. the term is "magic values". i had to wait this long
to respond because i was rolling forever when i saw the user+key@domain
construct!

cheers.

Re: Need help with email-match query

"axlq" wrote in message
news:fc73hv$q2c$3@blue.rahul.net...
> In article <46e64fd4$0$31120$6e1ede2f@read.cnntp.org>,
> gosha bine wrote:
>>
>>you can use mysql regular expressions, e.g.
>>
>>select * from users where email rlike 'user([+][^@]+)?@example[.]com'
>
> Cool. That's just what I wanted to know. I feared the answer would
> involve something inefficient like reading in the entire column and
> processing it in php.
>
>>or, if you need this query often create an extra "canonical email"
>>column in the database.
>
> I was thinking of that solution too, but adding a redundant column,
> didn't seem as elegant as constructing a query to work with what I
> have.

i think what he's suggesting is not a redundant column. rather, a normalized
structure. one where an "email address" column contained only email
addresses (minus any other magic data you are combining with it). your "key"
in user+key@domain should be in its own column and given a well fitted name
that describes briefly what "key" is.