Condition if column not NULL

[Backslider]

I have a table with a column that lists country ids as a comma separated list.

 

Often this column is NULL, but other times it is not.

 

How would I structure a query that checks this column and if not NULL will check if the user's country ID (PHP variable) is in the list of country IDs in the column and only return that row if it is?

[Jessica]

You need to normalize your data, storing anything in a delimited list in a single row is bad.

[Backslider]

You need to normalize your data, storing anything in a delimited list in a single row is bad.

 

I'm really not here to be lectured on what you think is "bad". Were I to delve into CODD hell then I would end up with a table with many millions of rows just for country IDs. That's just plain dumb.

 

MySQL has absolutely no problem whatsoever with delimited lists.

[Backslider]

Just to give you a reality check on that:

 

If my table has 25000 rows and I have 256 country IDs, then that gives me a possible 6,400,000 rows in my "normalized" table. Let's cut that in half for a realistic figure: 3.2 milliion rows. You REALLY think that is good??

[Jessica]

Yes, it's better than what you're doing. 

Databases are meant to handle lots of rows. Put indexes on the right columns and it's no issue. 

 

If there's no problem with the way you're doing it, then you should have no problem writing the query you need. But you're here, right?

[Backslider]

Ahhhhhh Jessica. Just what I wanted today. The resident "I know everything" troll. Seems that every tech forum has at least one of your ilk.

[BagoZonde]

I'm not sure is the best option but try with this:

 

$sql='SELECT * FROM table WHERE column LIKE "%' . $user_country_id . '%" ORDER BY some_field';

 

Of course be cautious about your IDs data. If you're not sure that IDs could be similiar in some way (e.g. when looking for AB and there could be ABC too) you can make double check from records you get by explode values or just use commas even for last element and seek for $user_country_id="AB," instead of $user_country_id="AB".

 

About optimizing, just kill me, I have no idea what's the best option either. Hope it helps.

[Backslider]

Perhaps with your eminent superiority, Jessica', you can explain to me why MySQL has 'find_in_set' if what you say is true?

Edited January 12, 2013 by Backslider

[Backslider]

I'm not sure is the best option but try with this:

 

$sql='SELECT * FROM table WHERE column LIKE "%' . $user_country_id . '%" ORDER BY some_field';

 

 

Thanks for the try BagoZonde, however it doesn't meet requirements as it will only return rows that are not NULL and which the ID matches. FIND_IN_SET is better to use than LIKE for comma separated lists (that's what it's for).

[BagoZonde]

Thanks for the try BagoZonde, however it doesn't meet requirements as it will only return rows that are not NULL and which the ID matches. FIND_IN_SET is better to use than LIKE for comma separated lists (that's what it's for).

 

Ooops, sorry, I didn't catch that part with NOT NULL as important one. You're right. FIND_IN_SET return NULL if column is NULL and 0 if not match, so it looks like nice solution. BTW Thanks for advice with FIND_IN_SET, as you see I'm not though in SQL at all.

[Jessica]

Because people insist on using bad design, and they're being accommodating. You're free to continue down that path, just don't act so surprised and put off when people tell you it's wrong. There's no reason not to normalize the data other than laziness.

[DavidAM]

Just to give you a reality check on that:

 

If my table has 25000 rows and I have 256 country IDs, then that gives me a possible 6,400,000 rows in my "normalized" table. Let's cut that in half for a realistic figure: 3.2 milliion rows. You REALLY think that is good??

 

With your table structure, in order to execute the query you are asking about, mySql (or any other database server, for that matter) will have to search every one of those 25,000 rows every time (that's called a "table scan" and is really bad for performance). With a normalized database, and the CountryID indexed, the server quickly finds the rows of interest using indexes -- a whole lot faster.

 

Perhaps with your eminent superiority, Jessica', you can explain to me why MySQL has 'find_in_set' if what you say is true?

 

For the same reason that PHP used to have magic_quotes, and register_globals; to support lazy/un-knowledgeable programmers! Just because a language supports something, does not mean it is a good way to do it. Also, FIND_IN_SET is useful for SET-type columns.

 

Ahhhhhh Jessica. Just what I wanted today. The resident "I know everything" troll. Seems that every tech forum has at least one of your ilk.

 

The reason you are here, presumably, is to learn how to accomplish something from someone who knows more than you --- there is nothing wrong with knowing that there are people that know more than you, it is why I came here in the first place (and I still don't think I've caught up with some of the folks here) --- we would be remiss if we did not provide professional-grade advice when we see someone doing something that is considered bad-practice.

 

If you can't or don't want to change your design, you can simply say so --- it would be polite to thank the person for the expert advice, as well --- but there is no reason to attack someone for trying to help.

[Backslider]

it would be polite to thank the person for the expert advice, as well --- but there is no reason to attack someone for trying to help.

 

I did not attack anybody for trying to help. I called out a person, and rightfully so, for being a troll. Why? The "I know the answer, but I am not going to tell you, work it out yourself".

[Backslider]

With your table structure, in order to execute the query you are asking about, mySql (or any other database server, for that matter) will have to search every one of those 25,000 rows every time (that's called a "table scan" and is really

 

I am sure that if we had you structuring the query that would be the case. Please think about what you are saying before you blurt out your own unknowledgeable fuffle.

[Jessica]

You're welcome to seek out a forum where everyone is as smart as you are, since this clearly isn't it. I did give you the only answer I have for the problem. I'm not claiming I know how to do the thing you want to do, I claimed there's a better way to approach the whole situation.

 

I don't normally bother to mention it because I'd hate to be accused of bragging, but the idea that I am a troll on this forum is kind of ridiculous given the moderators and other gurus thought that I deserved the guru title. I've been here for 8 years. Calling me a troll is just name calling as there's absolutely no way this community would tolerate a troll being promoted to a badge and sticking around for that long. Use a little logic if you're going to try to accuse me of something please.

 

Personally I don't care when people don't like my responses. But David took the time to write a very detailed response and you blew him off. That's annoying. It's rude. If anyone is being a troll, it's you. You join our community, ask for advice, then act like a whiny baby, insulting established members of the community because you don't like the advice. I might deserve it but David sure doesn't.

 

Hopefully one of the moderators can lock this thread soon, and you can find somewhere to learn the wrong way to do things and be happy about it.

[DavidAM]

@Jessica --- although you don't deserve it any more than I do.

 

@Backslider At the risk of sounding immodest, I have over 25 years experience in computer programming and consulting. A good part of that spent in database design and administration. I have designed and managed databases that handle hundreds of millions of dollars and millions of barrels of oil, just to name a couple. I know a table scan when I see one, and I've never seen one that I liked.

 

Jessica is no troll, but I think she hit the nail on the head about you. You have clearly demonstrated your ignorance --- and before anyone charges me with name calling, that simply means "lack of knowledge" --- in this area. "CODD Hell" as you called it, is a fairly simple and straight-forward description of the most efficient means of storing and accessing data in a relational environment. If it scares you so bad that you consider normalization "hell", then you need to find someone else to design your database for you, you obviously don't have the ability to handle it.

 

Oh, and by the way, Jessica did not say she knew how to do it. I, on the other hand, know exactly how to do it; a solution that works in edge cases, at that. But I am not going to tell you. Such knowledge, while sometimes necessary (in very rare cases), is too dangerous to place into the hands of a heretic.

[fenway]

Perhaps with your eminent superiority, Jessica', you can explain to me why MySQL has 'find_in_set' if what you say is true?

For the SET column type -- obviously.

 

And if you think millions of rows is a bad idea, maybe you shouldn't be using a DB at all.