NYCPHP Meetup

[Melissa]

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.

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.



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