Simplified Version of Over-extended Inquiry Below:
What is the best way to enter data into two tables at once in such a way so that each new row will be able to be referenced in some manner by a common ID? In other words, should i allow an auto-increment to be the common ID, or should i, in addition to the auto-increment for each table, have some sort of cross-referencing ID, as i might arrive at by using mysql_insert_id() to log one table's auto increment ID into the other? is there something else i should do altogether? The purpose is to be able to call the row from either table, almost as a single entry-- split to two tables (i think that's what my app needs!)
END
i've got myself all confused here, but i think i'm on the cusp of fixing a critical component of my app. i beg your pardon for the length of this topic post, but i'm pulling my hair out here!
many of you have probably at least heard me talk about this already. (hopefully, i'll earn your sympathy for my trials w/ this app, cause i think i need it!)
i'm creating (or should i say, trying to create and failing miserably at) a concert event scheduler. per the advice of someone in the MySQL.com forums, i decided to further normalize my db by adding another table (not shown in the illustration at that link) for artists only-- i'm calling it my "Lineup" table-- so i can have the Performers for a particular date listed seperate from the other concert info (such as ticket price, show date, date added, show time, etc). i still believe that was a good idea, and will make my app better-- however, since i've done this, i haven't been able to regain control of my app-- finding difficulty in managing how i will add this concert data and have it enter into both of these tables at once.
for example, if i were to add the following info:
Artist : band one
Artist : performer two
Artist : singer three
Artist : comedian four
Artist : juggler five
Artist : guy with a mop six
Today's Date: Saturday 05th of November 2005 01:47 PM
Show Date: 2005-11-05
Show Time: 5:00
Tickets Date of Show: 20
Tickets in Advance: 10
Age Requirement? no
Alcohol to be Served? yes
Concert Notes: first concert info added today
Lineup Notes: first lineup info today
i need for those artists to go into my separate table which has the following (cut from myphpadmin):
LINEUP TABLE:
Field Type Collation Attributes Null Default Extra Action
line_id int(11) No auto_increment
headliner varchar(100) latin1_swedish_ci No
spprt1 varchar(100) latin1_swedish_ci Yes NULL
spprt2 varchar(100) latin1_swedish_ci Yes NULL
spprt3 varchar(100) latin1_swedish_ci Yes NULL
spprt4 varchar(100) latin1_swedish_ci Yes NULL
spprt5 varchar(100) latin1_swedish_ci Yes NULL
line_notes text latin1_swedish_ci Yes NULL
CONCERT TABLE:
Field Type Collation Attributes Null Default Extra Action
concert_id int(11) No auto_increment
line_id int(11) No 0
date_added date No 0000-00-00
date_modified date No 0000-00-00
show_date date No 0000-00-00
show_time datetime Yes NULL
tix_dos float(5,2) Yes NULL
tix_adv float(5,2) Yes NULL
age_req enum('yes', 'no') latin1_swedish_ci Yes NULL
alcohol enum('yes', 'no') latin1_swedish_ci Yes NULL
concert_notes text latin1_swedish_ci Yes NULL
here's my currently erroneous code for the data entry, intended to go into both at the same time:
EDIT: i wasn't allowed to submit this many characters! so made a PDF (which fortunately has code highlighting carried over from TSW WebCoder!):
(Please ignore my excessive personal notes therein!)
i know i've got a lot of stuff wrong in there-- please realize that this file has elements carried over from before i made the "Lineup" table-- elements which might still need editing for proper function w/ the new tables / fields, etc. i guess my reason for this post, partially, is i'm trying to decide whether to start over-- or what i should do to handle this data entry. remember, this is my first app, so i'm really inexperienced. i don't want someone to just write the code for me, but i obviously must need a lot of help. ;(
thanks for reading!!
