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

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 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.

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.

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

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?

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.

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

hey, what happened to the fruits? i want the fruits back!!

okay, in a more serious vein, have a look at this -- Categories and Subcategories

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 . . .

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 -

Specifically, for your situation -

Note: There is a coma , separating each col_name/expr pair. Don't forget to add them if your list contains more items.

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?