NYCPHP Meetup

[Joshua S. Freeman]

First of all, thanks for reading my ridiculously long message!

On 5/21/03 9:11 PM, "Melissa" <melissa at inexact.info> wrote:

> Just as a point of database normalization, I don't think you need to have
> the specimen id and the composition group id in both tables
>I would leave the composition group id column out of the specimen table, and
>just have the relation be specimen id in both the composition group table and
>the specimen table.

I see.. so... i should remove the column for 'composition_group_id' from the
'specimen' table... but the composition_group table will look the way I
described it below...



> 
> As for the 'other' option...you would have to create two insert statements,
> the first one inserting a new record into the composition options table, the
> second inserting that record (including the newly created value in
> composition options) into the composition group table.

OK.. so, all I'm left with is that the next time someone loads the form, all
the menus/lists and checkboxes are dynamically built in the <form> so that
any additions to the menus are properly displayed..

Thanks Melissa...

cheers,

J.


> 
> 
> 
>> -----Original Message-----
>> From: Joshua S. Freeman [mailto:jfreeman at amnh.org]
>> Sent: Wednesday, May 21, 2003 8:49 PM
>> To: NYPHP Talk
>> Subject: [nycphp-talk] new problems
>> 
>> 
>> If I can solve the problems described below for one place in the
>> project I'm
>> working on, the solution will be applicable to many other places
>> as well. I
>> also think that the information will be useful for other people
>> learning how
>> to build apps using MySQL/PHP.
>> 
>> As I've mentioned before, the main table in my database is called
>> 'specimens'.
>> 
>> With regards to the survey form here's what I can do so far:
>> 
>> I am able to use PHP to build <select><option></option></select> menus and
>> lists.
>> 
>> Some of these are menus where the surveyor can only select one item.  I
>> *can* figure out how to write INSERT statements so that these
>> selections can
>> be inserted in a record in the 'specimens' table.
>> 
>> Others of these are menus where the surveyor can select more than
>> one item.
>> I *believe* this means I'm going to have to create a table that
>> relates the
>> specimen_id (the key to the 'specimens' table) with the 1 or >1 number of
>> items that are selected from the list by the surveyor.
>> 
>> I'm not sure what the structure of that relate-table should be.
>> 
>> For example, there's a section of the survey regarding the
>> 'composition' of
>> the specimens that are being surveyed.  In my form, it's a drop-down list
>> from which multiple selections can be made.  The drop-down list
>> in the form
>> is dynamically built by querying the 'composition' table.
>> 
>> here's a dump of the 'composition' table:
>> 
>> CREATE TABLE composition (
>>   comp_element_id smallint(3) NOT NULL auto_increment,
>>   comp_element varchar(50) NOT NULL default '',
>>   PRIMARY KEY  (comp_element_id)
>> ) TYPE=MyISAM;
>> 
>> #
>> # Dumping data for table `composition`
>> #
>> 
>> INSERT INTO composition VALUES (1, 'Paper/Carton');
>> INSERT INTO composition VALUES (2, 'Glandular Secretion');
>> INSERT INTO composition VALUES (3, 'Leaves');
>> INSERT INTO composition VALUES (4, 'Bark Fragments');
>> INSERT INTO composition VALUES (5, 'Paper/Carton with high Saliva
>> Content');
>> INSERT INTO composition VALUES (6, 'Insect Remains');
>> INSERT INTO composition VALUES (7, 'Wood/Twigs');
>> INSERT INTO composition VALUES (8, 'Leaves/Wood chewed into Paste');
>> INSERT INTO composition VALUES (9, 'Clay/Mud');
>> INSERT INTO composition VALUES (10, 'Wax');
>> INSERT INTO composition VALUES (11, 'Rootlets');
>> INSERT INTO composition VALUES (12, 'Plant Resin');
>> 
>> Let's say that the surveyor selects id numbers 1, 3, 4, 8, 10, 12 from the
>> drop-down multiple select list.
>> 
>> In that case, I guess I have to create a new table that relates my main
>> 'specimen' table with the compositional elements in the
>> 'composition' table.
>> 
>> I will call this table 'composition_group'.
>> 
>> This table has three columns. One stores a 'comp_group_id', one stores the
>> 'specimen_id' number of the specimen we're working on at the
>> moment, and the
>> last column stores the the id numbers 1, 3, 4, 8, 10 and 12 from the
>> 'composition' table so we know which compositional elements this
>> specimen is
>> comprised of.
>> 
>> I believe the 'composition_group' table will look like this:
>> 
>> 
>> CREATE TABLE composition_group (
>>   comp_group_id smallint(3) NOT NULL auto_increment,
>>   specimen_id smallint(3) NOT NULL,
>>    comp_group_values varchar(50) NOT NULL default '',
>>   PRIMARY KEY  (comp_group_id)
>> ) TYPE=MyISAM;
>> 
>> #
>> # Dumping data for table `composition_group`
>> #
>> 
>> INSERT INTO composition_group VALUES (1, 5, '1, 3, 4, 8, 10, 12');
>> INSERT INTO composition_group VALUES (2, 6, '2, 3, 10');
>> INSERT INTO composition_group VALUES (3, 7, '4, 6, 7, 8, 11');
>> INSERT INTO composition_group VALUES (4, 8, '7, 8, 10');
>> 
>> Does this make sense?...
>> 
>> Meanwhile, back in the main 'specimen' table, the column for 'composition'
>> will contain the 'comp_group_id' that relates to the current
>> 'specimen_id'.
>> 
>> Right?
>> 
>> ***I'm worried about the fact that the comp_group_values are numeric and
>> need to have commas between them... will this be a problem?***
>> 
>> 
>> So.. that's the first problem...
>> 
>> 
>> I think the second problem is stickier.
>> 
>> For many of the areas of the survey, whether they are presented as single
>> select drop-down menus, multiple select drop-down menus or
>> checkboxes, there
>> is also a space for the surveyor to input an 'other' such as:
>> 
>> <input type="text" size="10" name="composition"> Other
>> 
>> using this example, whatever is typed in there needs to be added
>> to the end
>> of the 'composition' table and made part of the record in the
>> 'composition_group' table for that survey...
>> 
>> thus, then NEXT time someone loads the form, the new compositional element
>> would be part of the drop down menu or list.
>> 
>> Where I get completely lost how to handle the 'other' problem with survey
>> areas that use checkboxes instead of menus/lists.  I guess this means that
>> I'm going to have to also learn how to build groups of checkboxes
>> dynamically by looking inside the database..
>> 
>> Well.. I could probably figure this out using the same PHP I used to
>> dynamically build menus/lists.
>> 
>> Anyway.. these are the next problems I need help solving.
>> 
>> Any advice greatly appreciated.
>> 
>> !!!
>> 
>> Thanks!
>> 
>> J.
>> 
>> 
>> 
>> 
>> 
>> 
> 
> 
> 
> --- Unsubscribe at http://nyphp.org/list/ ---
> 
> 
>