> 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?
Don't fret too much looking for a complicated solution, the solution is just as easy as it sounds. You must insert into each table individually. So for instance... someone enters a new person... you insert it into the person table. Someone enters a new file, you insert it into the files table. Now when someone wants to associate a file with a person, the user selects the person he wants (so you have the people_id) and the user selects the file he wants (so you have the file_id), and then you insert a row into the filespeople table. There is not a single command to do this for you, it will take 3 seperate inserts. > 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. Update will work in the same fashion... If person 'ryan' is working on 'file1' but is moved to 'file2'... do an update to the corresponding row in the filespeople table (or you can delete the old row, and insert the new one, but updating would yeild better performance). It sounds like you have setup the correct select statement but dont entirely understand why it works. I would suggest that you sit down with a pen and paper and 'draw' the three tables with some mock data and see how you can use the 'filespeople' table to join the two tables. > If anyone has a link to a tutorial on this very concept, that would be > greatly appreciated as well! Devshed ( http://www.devshed.com/ ) has some good simple tutorials that can help you understand what is going on with this SQL. There are also a lot of other good tutorials on the site (the ones I am refering to can be found in the mysql section I believe). Good luck, ryan --------------------------------------------------------------------- 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
