Insert Checkbox Values Into Table As Delimited String

[Jessica]

I wish I could like that 1000 times.

[stevew]

He'll figure out the "why" soon enough on his own, but since you appear to be reasonable and interested I'll explain:

 

By storing a delimited list in a single field, you remove your ability to search, sort, update, and delete. That's basically the entire point of databases now isn't it?

 

If my data looks like this:

1,5,234,88,124,2,79

 

How do I search for anything matched to number 88? The only real way to do that is:

 

WHERE FIND_IN_SET(88, theField);

 

You could also do:

WHERE theField LIKE '88,%' OR theField LIKE '%,88,%' OR theField LIKE '%,88'

 

Both of those queries are very very inefficient, because they require parsing huge string lists.

 

Now how do you return the item with the largest number of IDs associated with it? You...can't really. I guess you could try to count the number of commas:

SELECT name, (LENGTH(theField) - LENGTH(REPLACE(theField, ',', ''))) / LENGTH(',') FROM theTable GROUP BY theField ORDER BY (LENGTH(theField) - LENGTH(REPLACE(theField, ',', ''))) / LENGTH(',') DESC

 

What about editing? How do I remove ID 88 from a specific record? Well that's easy! I just...select it out of the database...put it into a PHP variable...explode it...loop through it...unset the proper index...implode it...and reinsert it into the database.

 

If the original advice of a lookup table was used INSTEAD, what we have is:

Find things linked to 88:

SELECT theTable.* FROM theTable JOIN lookupTable USING (id) WHERE lookupTable.lookup = 88;

 

Find all things ordered by number of lookups:

SELECT theTable.name, COUNT(lookupTable.lookup) FROM theTable JOIN lookupTable USING (id) GROUP BY theTable.name ORDER BY COUNT(lookupTable.lookup) DESC;

 

Delete 88 from record 5:

DELETE FROM lookupTable WHERE id = 5 AND lookup = 88;

 

Once the tables are done correctly, every query is very basic datbase 101. You don't need an entirely separate programming language, you don't need to wrap your head about the stupid voodoo magic I did with the LENGTH() function, you don't need to handle edge cases involved in lists of numbers, your stuff is designed right so it works right

 

And that, is why we said he was doing it wrong. He was.

 

 

Ha ha...thanks but fortunately I learned this lesson the wrong way myself!

 

P.S.

WHERE FIND_IN_SET(88, theField);

thx

Edited October 12, 2012 by stevew

[ManiacDan]

FIND_IN_SET is an ironic function, because it's only useful in incorrectly done databases. It's like they finally gave up and wrote bad database design into their query language.

[Christian F.]

Who wants to tell him that he (the OP) is open for SQL injections, btw? *Evil grin*

Oh, wait, he doesn't need "expert level advice" or comments on his code, he just need straight answers that directly relates to his questions. Guess I won't tell him why, or how, then.

Edited October 13, 2012 by Christian F.

[ManiacDan]

Requinix caught his SQL injection bugs days ago.

 

NOBODY CARES ABOUT YOUR OPINIONS CHRISTIANF

[requinix]

Requinix caught his SQL injection bugs days ago.

 

NOBODY CARES ABOUT YOUR OPINIONS CHRISTIANF

Actually it's not technically SQL injection: OP would have to wrap the list in quotes for it to work with his only-using-one-column approach, and since the string's contents have been mres()ed then he's safe. What could cause problems is expecting the output to be strictly numeric so the risk of injection is really XSS (not to mention bugs with the code expecting numbers and getting something else).

 

But yeah, I pointed it out to Dan back when the code came up I just didn't want to bother posting about it.

[Christian F.]

Hehe, didn't see it until yesterday. Not a whole lot of free time left after I'm done with the civil defense courses, unfortunately.

 

* gives ManicDan a hug.

[spfoonnewb]

As an expert, I do agree that in most situations (especially as a newbie to programming) you would want to normalize your data, however there are some cases where it may be better to do it another way. You can point out your opinion while #1 still answering the question and #2 not being so harsh about it. Best programming practices are just that, best practices, but that doesn't mean you should always follow the rule. Every project and application is different, and there are actually legitimate use cases for storing data in such a way. It's the same argument people have about storing serialized arrays or JSON in databases, and I used to think like you. The OP did post that he understands the risks associated with this decision. If he finds out later that he didn't use the best solution, let him learn then, but don't go off telling him you should "never" store data this way, that's simply not true.