seperator instead of a comma for listed items in DB?

[news@celticbear.com]

I'm making a recipe database, and need to have DB fields in mySQL that
will have lists of values that would get sent to an array to be worked
on.

I anticipate that at times a comma will need to be used in the value
itself. So, what's a commonly used symbol or something that people tend
to use as a separator of a list that would then be used in the split()
function to send the elements into an array?

Nearly every punctuation has a chance of being in the actual data. Any
suggestions?

Thanks!
Liam

[David Haynes]

Re: seperator instead of a comma for listed items in DB?

news@celticbear .com wrote:[color=blue]
> I'm making a recipe database, and need to have DB fields in mySQL that
> will have lists of values that would get sent to an array to be worked
> on.
>
> I anticipate that at times a comma will need to be used in the value
> itself. So, what's a commonly used symbol or something that people tend
> to use as a separator of a list that would then be used in the split()
> function to send the elements into an array?
>
> Nearly every punctuation has a chance of being in the actual data. Any
> suggestions?
>
> Thanks!
> Liam
>[/color]
Usually the vertical bar '|' is a good choice.

-david-

[Chung Leong]

Re: seperator instead of a comma for listed items in DB?


news@celticbear .com wrote:[color=blue]
> I'm making a recipe database, and need to have DB fields in mySQL that
> will have lists of values that would get sent to an array to be worked
> on.
>
> I anticipate that at times a comma will need to be used in the value
> itself. So, what's a commonly used symbol or something that people tend
> to use as a separator of a list that would then be used in the split()
> function to send the elements into an array?
>
> Nearly every punctuation has a chance of being in the actual data. Any
> suggestions?
>
> Thanks!
> Liam[/color]

I usually use an ASCII control character. 0x1E is designated as a
record seperator. Don't know if MySQL would accept it or not.

[news@celticbear.com]

Re: seperator instead of a comma for listed items in DB?


Chung Leong wrote:[color=blue]
> news@celticbear .com wrote:[color=green]
> > I'm making a recipe database, and need to have DB fields in mySQL that
> > will have lists of values that would get sent to an array to be worked
> > on.
> >
> > I anticipate that at times a comma will need to be used in the value
> > itself. So, what's a commonly used symbol or something that people tend
> > to use as a separator of a list that would then be used in the split()
> > function to send the elements into an array?
> >
> > Nearly every punctuation has a chance of being in the actual data. Any
> > suggestions?
> >
> > Thanks!
> > Liam[/color]
>
> I usually use an ASCII control character. 0x1E is designated as a
> record seperator. Don't know if MySQL would accept it or not.[/color]

Interesting.
I had to do a search on control characters and that 0x1e, because I
have no idea what they are.
(Well, except in Windows I know I can use ALT+(numpad) to write ASCII
characters, but that's beside the point.

Evidently 0x1e is shown as two carrets: ^^
When I create the full value that would go into the field, would I
simply insert ^^ between the different values before it gets sent to
mySQL, and then use ^^ as the split() seperator?
Or do I have to use that 0x1e label in some way?

Does the ^^ tell mySQL or PHP something special?
I can't seem to find anything with Google on it.

Thanks for the reply!
-Liam

[Sjoerd]

Re: seperator instead of a comma for listed items in DB?


news@celticbear .com wrote:[color=blue]
> need to have DB fields in mySQL that
> will have lists of values[/color]

Typically, one would use escape characters. For example, | would be
used to seperate two fields. A literal | would be escaped: \|. This
makes it possible to use the seperation character in the data.

Another, probably better option, would be to make a seperate table.
Instead of storing the ingredients seperated by a character like this:
1, Chinese Chicken, chicken|salt|pe pper|mushrooms
Think about making a ingredients table and storing it there:

[Recipes]
1, Chinese Chicken
2, Meatloaf

[Ingredients]
1, chicken
1, salt
1, pepper
1, mushrooms
2, meat
2, loaf

This makes it easy to search and it is more logical from a DB point of
view.

[Chung Leong]

Re: seperator instead of a comma for listed items in DB?


news@celticbear .com wrote:[color=blue]
> Chung Leong wrote:[color=green]
> > news@celticbear .com wrote:[color=darkred]
> > > I'm making a recipe database, and need to have DB fields in mySQL that
> > > will have lists of values that would get sent to an array to be worked
> > > on.
> > >
> > > I anticipate that at times a comma will need to be used in the value
> > > itself. So, what's a commonly used symbol or something that people tend
> > > to use as a separator of a list that would then be used in the split()
> > > function to send the elements into an array?
> > >
> > > Nearly every punctuation has a chance of being in the actual data. Any
> > > suggestions?
> > >
> > > Thanks!
> > > Liam[/color]
> >
> > I usually use an ASCII control character. 0x1E is designated as a
> > record seperator. Don't know if MySQL would accept it or not.[/color]
>
> Interesting.
> I had to do a search on control characters and that 0x1e, because I
> have no idea what they are.
> (Well, except in Windows I know I can use ALT+(numpad) to write ASCII
> characters, but that's beside the point.
>
> Evidently 0x1e is shown as two carrets: ^^
> When I create the full value that would go into the field, would I
> simply insert ^^ between the different values before it gets sent to
> mySQL, and then use ^^ as the split() seperator?
> Or do I have to use that 0x1e label in some way?
>
> Does the ^^ tell mySQL or PHP something special?
> I can't seem to find anything with Google on it.[/color]

Do something like implode("\x1B", $record);. The character is
definitely not two carets.

[news@celticbear.com]

Re: seperator instead of a comma for listed items in DB?


Sjoerd wrote:[color=blue]
> news@celticbear .com wrote:[color=green]
> > need to have DB fields in mySQL that
> > will have lists of values[/color]
>
> Typically, one would use escape characters. For example, | would be
> used to seperate two fields. A literal | would be escaped: \|. This
> makes it possible to use the seperation character in the data.
>
> Another, probably better option, would be to make a seperate table.
> Instead of storing the ingredients seperated by a character like this:
> 1, Chinese Chicken, chicken|salt|pe pper|mushrooms
> Think about making a ingredients table and storing it there:
>
> [Recipes]
> 1, Chinese Chicken
> 2, Meatloaf
>
> [Ingredients]
> 1, chicken
> 1, salt
> 1, pepper
> 1, mushrooms
> 2, meat
> 2, loaf
>
> This makes it easy to search and it is more logical from a DB point of
> view.[/color]

Well, since you brought it up, speaking of the DB, that's kind of what
I had in mind, but, differemt.
The tbl_ingredients would have two columns, ID and NAME like:
1, garlic
2, chicken
3, pepper
4, salt
etc...

Then the tbl_recipes would have colums like:
ID, NAME, INGREDIENTS, MEASUREMENTS, NOTES
with data like:
1, Chinese Chicken, 1|2|3|4, 1 clove|1 lbs. breast| 1 tsp.| 1 T., more
stuff here....

Where the ingredients would be the ingredient's ID in a CSV, (but
obviously using something other than a comma,).
That's the best I could come up with to be able to have a list of
unknown and constantly changing number of ingredients that would save
space in the table.

The problem I'm seeing with your suggestion is that the [ingredients]
table would have a lot of redundant info.
Every recipe that required pepper would have a row with a recipie ID
and a value of "garlic," which would be repeated in the database n
number of times.

I suppose of course, if the database remains small, that amount of
redundant data isn't going to hurt performance at all.... and would
possibly be easier to manage....

Thanks for the reply and the feedback!!
-Liam

[Jerry Stuckle]

Re: seperator instead of a comma for listed items in DB?

news@celticbear .com wrote:[color=blue]
> I'm making a recipe database, and need to have DB fields in mySQL that
> will have lists of values that would get sent to an array to be worked
> on.
>
> I anticipate that at times a comma will need to be used in the value
> itself. So, what's a commonly used symbol or something that people tend
> to use as a separator of a list that would then be used in the split()
> function to send the elements into an array?
>
> Nearly every punctuation has a chance of being in the actual data. Any
> suggestions?
>
> Thanks!
> Liam
>[/color]

Liam,

This violates first normal form (more than one value in a row/column).

Rather, create a second table containing the recipe id and a single value, then
add each of the values to this second table.

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

[David Haynes]

Re: seperator instead of a comma for listed items in DB?

news@celticbear .com wrote:[color=blue]
> Sjoerd wrote:[color=green]
>> news@celticbear .com wrote:[color=darkred]
>>> need to have DB fields in mySQL that
>>> will have lists of values[/color]
>> Typically, one would use escape characters. For example, | would be
>> used to seperate two fields. A literal | would be escaped: \|. This
>> makes it possible to use the seperation character in the data.
>>
>> Another, probably better option, would be to make a seperate table.
>> Instead of storing the ingredients seperated by a character like this:
>> 1, Chinese Chicken, chicken|salt|pe pper|mushrooms
>> Think about making a ingredients table and storing it there:
>>
>> [Recipes]
>> 1, Chinese Chicken
>> 2, Meatloaf
>>
>> [Ingredients]
>> 1, chicken
>> 1, salt
>> 1, pepper
>> 1, mushrooms
>> 2, meat
>> 2, loaf
>>
>> This makes it easy to search and it is more logical from a DB point of
>> view.[/color]
>
> Well, since you brought it up, speaking of the DB, that's kind of what
> I had in mind, but, differemt.
> The tbl_ingredients would have two columns, ID and NAME like:
> 1, garlic
> 2, chicken
> 3, pepper
> 4, salt
> etc...
>
> Then the tbl_recipes would have colums like:
> ID, NAME, INGREDIENTS, MEASUREMENTS, NOTES
> with data like:
> 1, Chinese Chicken, 1|2|3|4, 1 clove|1 lbs. breast| 1 tsp.| 1 T., more
> stuff here....
>
> Where the ingredients would be the ingredient's ID in a CSV, (but
> obviously using something other than a comma,).
> That's the best I could come up with to be able to have a list of
> unknown and constantly changing number of ingredients that would save
> space in the table.
>
> The problem I'm seeing with your suggestion is that the [ingredients]
> table would have a lot of redundant info.
> Every recipe that required pepper would have a row with a recipie ID
> and a value of "garlic," which would be repeated in the database n
> number of times.
>
> I suppose of course, if the database remains small, that amount of
> redundant data isn't going to hurt performance at all.... and would
> possibly be easier to manage....
>
> Thanks for the reply and the feedback!!
> -Liam
>[/color]

How about this instead?

Three tables: Recipe, Quantity, Ingredient

Recipe
ID, NAME, DESCRIPTION, AUTHOR, ...

Ingredient
ID, DESCRIPTION

Quantity
RECIPE_ID, INGREDIENT_ID, AMOUNT, UNIT

That way you could have:

Recipe:
1, 'Chinese Chicken', 'A neat recipe that is easy to make', ...

Ingredient:
1, 'Garlic'
2, 'Chicken'
3, 'Salt'
4, 'Pepper'

Quantity:
1, 1, 1, 'clove'
1, 2, 1, 'lb white meat'
1, 3, 1, 'tsp'
1, 4, 1, 'tbsp'

Then, let's say you have another recipe that calls for salt.

Recipe:
2, 'Mashed Potatoes', 'Light and fluffy mashed potatoes'

Ingredient:
5, 'Potatoes'
6, 'Buttermilk'

Quantity:
2, 3, 1, 'tbsp'
2, 5, 2, 'lbs cubed'
2, 6, .25, 'cup'
2, 4, 0, 'to taste'

etc.

-david-

[news@celticbear.com]

Re: seperator instead of a comma for listed items in DB?


David Haynes wrote:[color=blue]
> news@celticbear .com wrote:[color=green]
> > Sjoerd wrote:[color=darkred]
> >> news@celticbear .com wrote:
> >>> need to have DB fields in mySQL that
> >>> will have lists of values
> >> Typically, one would use escape characters. For example, | would be
> >> used to seperate two fields. A literal | would be escaped: \|. This
> >> makes it possible to use the seperation character in the data.
> >>
> >> Another, probably better option, would be to make a seperate table.
> >> Instead of storing the ingredients seperated by a character like this:
> >> 1, Chinese Chicken, chicken|salt|pe pper|mushrooms
> >> Think about making a ingredients table and storing it there:
> >>
> >> [Recipes]
> >> 1, Chinese Chicken
> >> 2, Meatloaf
> >>
> >> [Ingredients]
> >> 1, chicken
> >> 1, salt
> >> 1, pepper
> >> 1, mushrooms
> >> 2, meat
> >> 2, loaf
> >>
> >> This makes it easy to search and it is more logical from a DB point of
> >> view.[/color]
> >
> > Well, since you brought it up, speaking of the DB, that's kind of what
> > I had in mind, but, differemt.
> > The tbl_ingredients would have two columns, ID and NAME like:
> > 1, garlic
> > 2, chicken
> > 3, pepper
> > 4, salt
> > etc...
> >
> > Then the tbl_recipes would have colums like:
> > ID, NAME, INGREDIENTS, MEASUREMENTS, NOTES
> > with data like:
> > 1, Chinese Chicken, 1|2|3|4, 1 clove|1 lbs. breast| 1 tsp.| 1 T., more
> > stuff here....
> >
> > Where the ingredients would be the ingredient's ID in a CSV, (but
> > obviously using something other than a comma,).
> > That's the best I could come up with to be able to have a list of
> > unknown and constantly changing number of ingredients that would save
> > space in the table.
> >
> > The problem I'm seeing with your suggestion is that the [ingredients]
> > table would have a lot of redundant info.
> > Every recipe that required pepper would have a row with a recipie ID
> > and a value of "garlic," which would be repeated in the database n
> > number of times.
> >
> > I suppose of course, if the database remains small, that amount of
> > redundant data isn't going to hurt performance at all.... and would
> > possibly be easier to manage....
> >
> > Thanks for the reply and the feedback!!
> > -Liam
> >[/color]
>
> How about this instead?
>
> Three tables: Recipe, Quantity, Ingredient
>
> Recipe
> ID, NAME, DESCRIPTION, AUTHOR, ...
>
> Ingredient
> ID, DESCRIPTION
>
> Quantity
> RECIPE_ID, INGREDIENT_ID, AMOUNT, UNIT
>
> That way you could have:
>
> [..]
> -david-[/color]

Huh, OK. I hadn't thought of it that way. Intuitively it wouldn't seem
like you'd want to have the unique data that makes up the components of
the recipe in a "quantity" table as opposed to the table that contains
the recipe name and details, etc.
That erroneous thinking is what led me to the silliness of trying to
shove multiple data values into one field.
This solves (makes irrelevant) my array/CSV problem.
Thanks for the clue-by-four!
-Liam

[David Haynes]

Re: seperator instead of a comma for listed items in DB?

news@celticbear .com wrote:[color=blue]
> David Haynes wrote:[color=green]
>> news@celticbear .com wrote:[color=darkred]
>>> Sjoerd wrote:
>>>> news@celticbear .com wrote:
>>>>> need to have DB fields in mySQL that
>>>>> will have lists of values
>>>> Typically, one would use escape characters. For example, | would be
>>>> used to seperate two fields. A literal | would be escaped: \|. This
>>>> makes it possible to use the seperation character in the data.
>>>>
>>>> Another, probably better option, would be to make a seperate table.
>>>> Instead of storing the ingredients seperated by a character like this:
>>>> 1, Chinese Chicken, chicken|salt|pe pper|mushrooms
>>>> Think about making a ingredients table and storing it there:
>>>>
>>>> [Recipes]
>>>> 1, Chinese Chicken
>>>> 2, Meatloaf
>>>>
>>>> [Ingredients]
>>>> 1, chicken
>>>> 1, salt
>>>> 1, pepper
>>>> 1, mushrooms
>>>> 2, meat
>>>> 2, loaf
>>>>
>>>> This makes it easy to search and it is more logical from a DB point of
>>>> view.
>>> Well, since you brought it up, speaking of the DB, that's kind of what
>>> I had in mind, but, differemt.
>>> The tbl_ingredients would have two columns, ID and NAME like:
>>> 1, garlic
>>> 2, chicken
>>> 3, pepper
>>> 4, salt
>>> etc...
>>>
>>> Then the tbl_recipes would have colums like:
>>> ID, NAME, INGREDIENTS, MEASUREMENTS, NOTES
>>> with data like:
>>> 1, Chinese Chicken, 1|2|3|4, 1 clove|1 lbs. breast| 1 tsp.| 1 T., more
>>> stuff here....
>>>
>>> Where the ingredients would be the ingredient's ID in a CSV, (but
>>> obviously using something other than a comma,).
>>> That's the best I could come up with to be able to have a list of
>>> unknown and constantly changing number of ingredients that would save
>>> space in the table.
>>>
>>> The problem I'm seeing with your suggestion is that the [ingredients]
>>> table would have a lot of redundant info.
>>> Every recipe that required pepper would have a row with a recipie ID
>>> and a value of "garlic," which would be repeated in the database n
>>> number of times.
>>>
>>> I suppose of course, if the database remains small, that amount of
>>> redundant data isn't going to hurt performance at all.... and would
>>> possibly be easier to manage....
>>>
>>> Thanks for the reply and the feedback!!
>>> -Liam
>>>[/color]
>> How about this instead?
>>
>> Three tables: Recipe, Quantity, Ingredient
>>
>> Recipe
>> ID, NAME, DESCRIPTION, AUTHOR, ...
>>
>> Ingredient
>> ID, DESCRIPTION
>>
>> Quantity
>> RECIPE_ID, INGREDIENT_ID, AMOUNT, UNIT
>>
>> That way you could have:
>>
>> [..]
>> -david-[/color]
>
> Huh, OK. I hadn't thought of it that way. Intuitively it wouldn't seem
> like you'd want to have the unique data that makes up the components of
> the recipe in a "quantity" table as opposed to the table that contains
> the recipe name and details, etc.
> That erroneous thinking is what led me to the silliness of trying to
> shove multiple data values into one field.
> This solves (makes irrelevant) my array/CSV problem.
> Thanks for the clue-by-four!
> -Liam
>[/color]
Glad to help.

The 'Quantity' table is better known as a 'many-to-many' join table and
is used quite a lot in database schema design. It is called
'many-to-many' since a recipe may use many ingredients and an ingredient
may be used in many recipes.

-david-

[Gordon Burditt]

Re: seperator instead of a comma for listed items in DB?

>I'm making a recipe database, and need to have DB fields in mySQL that[color=blue]
>will have lists of values that would get sent to an array to be worked
>on.[/color]

If you're putting a list of values in a single field, that represents
a one-to-many relationship, and calls for use of another table.
For example, a table called Ingredients which contains the
ingredient, quantity, and ID of the recipe to which it belongs.
[color=blue]
>I anticipate that at times a comma will need to be used in the value
>itself. So, what's a commonly used symbol or something that people tend
>to use as a separator of a list that would then be used in the split()
>function to send the elements into an array?
>
>Nearly every punctuation has a chance of being in the actual data. Any
>suggestions?[/color]

Use another table.

Gordon L. Burditt

[JDS]

Re: seperator instead of a comma for listed items in DB?

On Mon, 17 Apr 2006 08:12:58 -0700, news@celticbear .com wrote:
[color=blue]
> Nearly every punctuation has a chance of being in the actual data. Any
> suggestions?[/color]

You can use a multi-character separator.

@@@

or
@!@

or

THIS_IS_THE_SEP ARATOR

etc.


Who says a split() field separator has to be one character?
--
JDS | jeffrey@example .invalid
| http://www.newtnotes.com
DJMBS | http://newtnotes.com/doctor-jeff-master-brainsurgeon/

[JDS]

Re: seperator instead of a comma for listed items in DB?

On Mon, 17 Apr 2006 12:09:24 -0700, news@celticbear .com wrote:
[color=blue]
> Then the tbl_recipes would have colums like:
> ID, NAME, INGREDIENTS, MEASUREMENTS, NOTES
> with data like:
> 1, Chinese Chicken, 1|2|3|4, 1 clove|1 lbs. breast| 1 tsp.| 1 T., more
> stuff here....[/color]

You *could* do that but typically a reational database will use *another*
table to "link" the one-to-many relationship together.

table 1: ingredients
table 2: recipe
table 3: recipe-to-ingredients-linker

Table 1 and 2 would be as you described but table 3 would be (roughly) as
follows:

create table recipe_ingredie nts (
id int()
recipe_id int()
ingredient_id int()
... additional_colu mns ...
)

You then would join all three tables using recipe_id to join recipes to
the linker and then using ingredient_id to join to the ingredients.

This way, you can have an unlimited number of ingredients.

It is clunky from a *human* point of view but fast, flexible, scalable,
etc. from a machine point of view.

(The additional_colu mns could be things like "quantity" or other modifiers)

later...

--
JDS | jeffrey@example .invalid
| http://www.newtnotes.com
DJMBS | http://newtnotes.com/doctor-jeff-master-brainsurgeon/

[Chaos_Not@Home]

Re: seperator instead of a comma for listed items in DB?

Yeah I've used triple-colons in the past :::

for me it doesn't hurt my eyes to see that. :-)
--------
I typically lurk here as I keep trying to get my hands dirty w/ both
PHP & MySQL - but one issue I have - a road block really - is the setup
of a database and how it should be broken up, etc... There is some
nice insight here.

Thanks,
Scott