Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2007
    Posts
    34

    Unanswered: how to use data in one field to change data in others

    hi,

    i'm trying to change data in a form where one of the fields has something like a fruit (orange, apple, etc) and the other fields are blank, but i'd like to change (fill the form fields) them to correspond to the types of fruit that my other field has. for example if field has oranges, then other fields would be filled with juicing, navel, florida, california, etc. any help appreciated. thanks.

    mike

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry I dont understand what you want to achieve

    unless you are suggesting that your user selects a product category, which then limits the choices in say a list box to named varieties of that category
    that would invovle a fair bit of network traffic.

    that would depend laregly on your front end.. the environment where you are creating your front end that you display to the user.

    to do that you would need a two table model

    the first identifies the product category

    eg
    product_categories
    id...pk
    description
    parent_category ...caters for a top down navigation, FK to product_categories.id

    eg
    1 | Fruit | <NULL>
    2 | Vegetables | <NULL>
    3 | Citrus | 1
    4 | Orange | 3
    5 | Potato | 2
    etc..

    products
    id...pk
    variety
    product_category FK to product_categories.id

    eg
    1 | King Edward | 5
    2 | Navel | 3
    3 | Clementine | 3
    etc...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    I think the post has more to do with the server scripting language and/or javascript in the browser. Querying the database for the specific information to populate a second select/option box on a form based on the value selected in the first select/option box would be the easy part.

    Which part of this do you need help on? Your post is a general statement of what you want, but does not actually contain a question.

  4. #4
    Join Date
    Feb 2007
    Posts
    34

    followup

    to clarify, i already have a database structured and one field is the apple field with blank fields as modifiers. what i'm asking is can someone tell me the correct sql code to use the apple field to populate those other fields with data of my choice, for example, grannysmith, goldendelicious. i will chose the values for the other fields based on the main apple field. thanks.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you may know your data structure, we dont, so its unlikely we can magic up some SQL to meet your requirement

    you dont mention at what point your user will select the category oranges, and you dont mention how you will then retrieve your sub categories of oranges
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2007
    Posts
    34

    data structure

    my data is in two tables but only one of them is what i'm talking about. here:
    field1 = fruit = apple
    field2= fruit_spec1 = macantosh
    field3= fruit_spec2 = granny_smith

    currently field1 has a value = apple
    fields2, 3, etc are blank and i want to fill them in using the value in field1 as a trigger for the values that i chose for fields2 and field3.

    does that help?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by hconnor
    ....does that help?
    in short: no

    have a look here and here or even here which should give you a flavour if the sort information you need to provide to give the experts (and amateurs like me) a chance of answering your question

    it helps if you provide your table(s) design with some indicative data
    it helps if you provide details of waht front end environemnt you are using (there would be no point in providing an example that woudl work for say MS Access but wont work in PHP or Ruby
    it helps if you tell us what verion of MySQL you are using
    it helps if you clearly explain you user requirement, not lapse into pseudo DBA speak. I haven't got a scooby what "and i want to fill them in using the value in field1 as a trigger for the values that i chose for fields2 and field3." actually means

    does it mean you want soem SQL to populate column field 3, does it mean you want to do a look up and transfer the vlaue form field3 or field 2 into field 1 I dont understand hgow you correlate what is an apple or what is an orange
    I dont see how you knwo what varities are for which druit or vegetable
    what do you mean using the vlaue in field 1 as a trigger for values you choiise in field 2 or 3.

    you almost certainly know what you meant to say, but you need to look at what you do say, if you dont provide the deatils then you are not going to get an answer that makes sense.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2007
    Posts
    34
    sorry about being vague its the darn nda. let me try again.
    i've built mysql database and one of its tables has a number of fields.
    physician_ID
    first_name
    last_name
    specialty
    subspecialty1
    subspecialty2
    etc.

    i have filled in the specialties, and to make searches easier, I want to fill the subspecialty fields which are currently blank. i was hoping to use phymyadmin and enter the sql code to fill in the subspecialties. for example, for a cardiology, i want to fill in subspecialty1 = invasive, subspecialty2= noninvasive. for different specialties i'll modify the code and specifiy which specialty to find and what to put in each of the subspecialty fields. what i'm trying to do is batch everything. i want to find every record that has cardiology in the specialty field and modify the subspecialty1 and subspecialty2 fields. i'm new at mysql, which you can probably tell. does this clarify things better? thanks.
    mike
    Last edited by hconnor; 02-27-07 at 20:06.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey, what happened to the fruits? i want the fruits back!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, in a more serious vein, have a look at this -- Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2007
    Posts
    34
    alas, the fruits have been eaten.
    in a more serious vein, the tables are already set and have a good deal of data in them. i'm looking for the actually mySQL statement (or direction for where to find it so that i can write it) for searching and replacing i think. but i want to search the category field and replace the null data fields with data as a batch . . .

  12. #12
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Wow. Sorry to be blunt, but if you had been upfront and non-evasive with the information needed (the 8th post, 12hr after the first post is the only one that contains useful information), you could have had an answer hours ago. From the mysql manual -

    Code:
    UPDATE tbl_name
        SET col_name1=expr1 [, col_name2=expr2 ...]
        WHERE where_condition
    Specifically, for your situation -

    Code:
    UPDATE your_table
        SET subspecialty1 = 'value you want', 
              subspecialty2 = 'value you want'
    WHERE specialty = 'value you want to match'
    Note: There is a coma , separating each col_name/expr pair. Don't forget to add them if your list contains more items.

  13. #13
    Join Date
    Feb 2007
    Posts
    34
    thanks for the input. that worked like a charm. But I feel like I should have known that. Is there a good resource for mySQL examples besides the manual? Or is that pretty much the source everyone learns from?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •