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 15 1654 ne**@celticbear .com wrote: 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
Usually the vertical bar '|' is a good choice.
-david- ne**@celticbear .com wrote: 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
I usually use an ASCII control character. 0x1E is designated as a
record seperator. Don't know if MySQL would accept it or not.
Chung Leong wrote: ne**@celticbear .com wrote: 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
I usually use an ASCII control character. 0x1E is designated as a record seperator. Don't know if MySQL would accept it or not.
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 ne**@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. ne**@celticbear .com wrote: Chung Leong wrote: ne**@celticbear .com wrote: 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
I usually use an ASCII control character. 0x1E is designated as a record seperator. Don't know if MySQL would accept it or not.
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.
Do something like implode("\x1B", $record);. The character is
definitely not two carets.
Sjoerd wrote: ne**@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 ne**@celticbear .com wrote: 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
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. js*******@attgl obal.net
=============== === ne**@celticbear .com wrote: Sjoerd wrote: ne**@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
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-
David Haynes wrote: ne**@celticbear .com wrote: Sjoerd wrote: ne**@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
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-
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: dixie |
last post by:
I have some code that adds new records into a table for each ID in a list
box when a button on a form is clicked. This works fine. My problem now is
that I wish to be able to edit all the records for people whose ID is in the
list box. I made minor changes to the code (mainly replacing rs.AddNew with
rs.Edit)and it appears to be updating only the first record and then
overwriting that record with the next, etc until it runs out of ID's...
|
by: Jesper Denmark |
last post by:
Hi,
I use the double.Parse functionality to convert a number
in a text file into a double. However, while this works
fine on one computer it doesn't on another. I've found
out that it is dependent on the comma seperator settings
on the computer. Is there a way to override this - I
would like double.Parse to always use a . as comma
seperator no matter what the system settings are.
|
by: Drum2001 |
last post by:
I have a database where I need to query multiple items. Is it possible
to run a query based on a textbox where the information is delimited by
a comma.
Example:
Show me all names where Social Security Number:
= 111223333, 444556666, 777889999
|
by: Peter Larsen [] |
last post by:
Hi,
How do i convert a string to float/double if the decimal-seperator doesn't
follow the local language settings ??
Eg.
string s = load a value from a file - "1234,12";
float f = float.Parse(s);
This will fail because my PC expect a dot - not a comma.
|
by: lstuyck73 |
last post by:
Hi, I'm using a menu control that uses a SiteMapDatasource to display
menu items in a horizontal menu. I want a seperator between each menu
item. If I use the StaticBottomSeperatorImageUrl property, the
seperator is shown between each menu item but also after the last one,
which I don't want - I want only seperators between the menu items,
not before the first or after the last menu item.
Does anyone know how I can do that?
Thanks!!
| |
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |