Need help with email-match query

[axlq]

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
(us**********@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 us***********@gmail.com will get delivered
to us**@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 us******@example.com instead of the address
us**********@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

[Jerry Stuckle]

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
(us**********@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 us***********@gmail.com will get delivered
to us**@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 us******@example.com instead of the address
us**********@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.
js*******@attglobal.net
==================

[gosha bine]

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
(us**********@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 us***********@gmail.com will get delivered
to us**@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 us******@example.com instead of the address
us**********@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

[axlq]

In article <K5******************************@comcast.com>,
Jerry Stuckle <js*******@attglobal.netwrote:

>
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

[axlq]

In article <46***********************@read.cnntp.org>,
gosha bine <st********@gmail.comwrote:

>
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

[Michael Fesser]

..oO(axlq)

>Jerry Stuckle <js*******@attglobal.netwrote:

>>
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

[axlq]

In article <ms********************************@4ax.com>,
Michael Fesser <ne*****@gmx.dewrote:

>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

[Jerry Stuckle]

axlq wrote:

In article <K5******************************@comcast.com>,
Jerry Stuckle <js*******@attglobal.netwrote:

>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.
js*******@attglobal.net
==================

[Steve]

"Jerry Stuckle" <js*******@attglobal.netwrote in message
news:AK******************************@comcast.com. ..

axlq wrote:

>In article <K5******************************@comcast.com>,
Jerry Stuckle <js*******@attglobal.netwrote:

>>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.

[Steve]

"axlq" <ax**@spamcop.netwrote in message
news:fc**********@blue.rahul.net...

In article <46***********************@read.cnntp.org>,
gosha bine <st********@gmail.comwrote:

>>
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.