Hello, everyone --
I have received a great deal of help from many members of this list, so
I'd like to acknowledge that now. Someday, when I have my head wrapped
around this stuff, I hope to return the favor. In the meantime,
however, I have come across my worst brainbuster yet. Any help on this
is greatly appreciated.
I constructed my tables in the most "normalized" way that I could
(without overdoing it), so that in some cases, there is no -direct-
relationship between tables. That is, I have a table called "people"
and a table called "files", and a foreign key table called "filespeople":
Database: medialab_db Table: people
+-------------+----------------------+------+-----+
| Field | Type | Null | Key |
+-------------+----------------------+------+-----+
| people_id | smallint(5) unsigned | | PRI | (auto-incremented)
| first_name | varchar(36) | | |
| last_name | varchar(36) | | |
+-------------+----------------------+------+-----+
Database: medialab_db Table: files
+-------------+-----------------------+------+-----+
| Field | Type | Null | Key |
+-------------+-----------------------+------+-----+
| file_id | mediumint(8) unsigned | | PRI | (auto-incremented)
| file_name | varchar(64) | | |
+-------------+-----------------------+------+-----+
Database: medialab_db Table: filespeople
+-----------+-----------------------+------+-----+
| Field | Type | Null | Key |
+-----------+-----------------------+------+-----+
| file_id | mediumint(8) unsigned | | PRI |
| people_id | smallint(5) unsigned | | PRI |
+-----------+-----------------------+------+-----+
The relationship, in real life, is that I would like to establish
many-to-many relationships between "files" records and "people" records,
so that a record in "files" would be associated with several people from
"people". There is a foreign key table called "filespeople". The SQL
used to write a SELECT statement would use the join like so:
SELECT files.file_name, people.first_name, people.last_name
FROM files, people, filespeople
WHERE files.file_name = $filename
AND files.file_id = filespeople.file_id
AND people.people_id = filespeople.people_id
(the $filename variable is a user-selected variable, I'm using PHP)
So I designed my "files" and "people" tables without any direct
relationship with one another, thinking to link them with the SELECT
statement.
What I completely forgot, up until this point, was that I would need to
INSERT these records (from pre-written HTML/PHP forms), and there is no
WHERE clause in the INSERT statement to keep everything together. In my
scenario, a user might add a record to "files" and wish to associate
that record to some of the records in "people", either new or
pre-existing (typed into an HTML text input form or something). How
should SQL code be arranged to "link" these records over the foreign key
table?
INSERT INTO files (file_name) VALUES ("$filename")
and
INSERT INTO people (first_name, last_name) VALUES ("$firstname",
"$lastname")
but... to keep it all together... is lost on me... and then later to
have UPDATE statements to do the same thing! Although I suspect this
may be easier as I can use the WHERE clause in an UPDATE statement.
If anyone has a link to a tutorial on this very concept, that would be
greatly appreciated as well!
Thank you,
Erik
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
