MySQL: Select like?

[Anon]

I've been trying to do a select from a database where i have a long string with email addresses in it.

now, what i want to do is get a list of records with one certain email address in it...

so for example, the dataset has [e-mail]|[e-mail]|

I want to be able to get that record because it contains the email address [e-mail].

I've read the mysql manual, but it just fails to make sense.

I hope someone can help!

[Anon]

"SELECT FROM tablename WHERE (email = 'desired_email')"

You will have to repeat that statement for every table you want to hunt, that is assuming the email is in every table you are hunting.

[Anon]

Just re-read the question and I may have misunderstood :

IF the email addresses are all stored in one long string, you have to EXPLODE the string first and grab the one you want from the string.

$emails = explode("|", $email_string_from_db);

Then do a for loop through the $emails[] array to find the email you want and assign it to a variable for the previous select statement in the other post.

[Anon]

Umm. Yes, that's kind of what i wanted...

mysql> SELECT notify FROM topic WHERE (notify = '[e-mail]');
Empty set (0.00 sec)

But i'm looking through a LOT of email addresses in ONE column, seperated by the pipe, so that example is always going to return empty, because it's not finding an exact string match.

[Anon]

i don't want to have to explode the string, because i'd have to do it for each individual row. I know you can do it with mysql command (like, match strcmp, etc), so i don't have to go through and select again and again.

[Anon]

if the list of email addresses is all in the same field, then you need to use LIKE, but this will return the whole field. You will need to devise your own rules to cough up anything more specific.

For example:

if your table is called THETABLE and the filed with the email addresses bunched up inside it is called EMAILS :

Select EMAILS from THETABLE where EMAILS like '[e-mail]%'

will come up with "[e-mail]|[e-mail]".

Is that what you need to do? Of course, if you want a different field returned, just select the appropriate field.

HTH

Jack
[e-mail]

[reagent]

What no one has commented on is: why is there a field w/ multiple emails separated by the pipe symbol? Is it some sort of user table where a user can have multiple email addresses? I think you would be better served by re-evaluating your current schema, which would solve this problem and definitely help as your database gets more complicated.

p.

[Anon]

Bingo!

Thanks very much.

I just needed the information from the table so i could make a list of articles a user was subscribed to, so i pretty much needed all the information!

Thanks Jack and Icehawg

[degauss]

Nope, definately not flawed.

I'd like to know how you think it's flawed when you haven't seen any of the code or the db structure that i've been working on.

Is there a set of rules set in stone that says "thou shalt not put a delimited array within a mysql field!"?

[reagent]

I didn't say that your schema WAS flawed, I just thought it may need to be reevaluated, as it seems that your data may not be normalized. There have been many times on this forum where I have seen users creating fields such as:
link_1
link_2, etc...

which would benefit from a separate table. This particular thread you began looked similar. You're right that I don't know what the application is, or what your code looks like, but I just wanted to interject in the case that you had approached it the wrong way.

Once again, I am sorry if I have offended you.

p.

[Anon]

I respectfully agree with p. reagan. I, too, know nothing of your application, but it does seem as though you would benefit from reevaluating your schema. By putting the email addresses in a separate table, related to the table in question, you could both avoid using 'LIKE' and more elegantly 'JOIN' the two tables, getting exactly the information you need in the minimum number of rows. I think this might be faster and easier to manage.

I've used databases long enough to have had occasion to use denormaling tricks here and there for a variety of reasons. Therefore, I definitely wouldn't say that inserting a pipe-delimited string in your table is wrong--it may be perfect for your app. I just wanted to suggest the alternative in this case.

[degauss]

Yeah, i'm sorry i flamed.

You know how it gets with personal projects though, it feels like everyone in the world is out to criticise what you're doing.

So i apologise for my short temper.

And thank you for your continuing help.

🙂