midgley Posted January 18, 2009 Share Posted January 18, 2009 hi, im hoping somebody on this forum might be able to shed some light on how to insert data into a relational database heres how i have my database set up: https://cid-b405ca050a85fc76.skydrive.live.com/self.aspx/New%20folder/relationhips.jpg so how would i go about inserting a new photo and linking it to an existing user? this for instance doesnt work INSERT INTO photos (photoName) VALUES ('dog') WHERE users.userId = '1' can anybody help me out on this one? Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/ Share on other sites More sharing options...
Mchl Posted January 18, 2009 Share Posted January 18, 2009 Posting this image somewhere, where it is accessible without need to register would be helpful Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739694 Share on other sites More sharing options...
midgley Posted January 18, 2009 Author Share Posted January 18, 2009 good point heres the new link: http://www.ink-recycle.co.uk/deleteme.html Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739701 Share on other sites More sharing options...
trq Posted January 18, 2009 Share Posted January 18, 2009 INSERT queries do not accept a WHERE clause, you either need to insert a new record.... INSERT INTO photos (photoName) VALUES ('dog'); or UPDATE an oexisting one.... UPDATE photos SET photoName = 'dog' WHERE id = 1; Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739703 Share on other sites More sharing options...
midgley Posted January 18, 2009 Author Share Posted January 18, 2009 USERS(userId) USERSPHOTOS(userId,photoId) PHOTOS(photoId) i can insert users and i can insert photos but i dont understand how to go about associating a photo with a user. the foreign keys in the USERSPHOTOS table are always empty Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739717 Share on other sites More sharing options...
midgley Posted January 18, 2009 Author Share Posted January 18, 2009 im thinking that mysql would auto update the foreign keys in USERSPHOTOS but only if i some how associate the photo with a user upon using INSERT INTO Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739721 Share on other sites More sharing options...
fenway Posted January 18, 2009 Share Posted January 18, 2009 It can't auto-update a record that isn't there. Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739733 Share on other sites More sharing options...
midgley Posted January 18, 2009 Author Share Posted January 18, 2009 so i have to manually insert data into the USERSPHOTOS table then. like this: -insert photoname into photos (which would auto increment photoId) -then retrive the user id from users -then insert the userid and photoid into the link table called USERSPHOTOS can you let me know if this is the right way because i was thinking that mysql let you insert data into multiple tables all with one mysql_query thus letting mysql update the USERPHOTOS table for me. Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739741 Share on other sites More sharing options...
fenway Posted January 18, 2009 Share Posted January 18, 2009 No, you can't insert into multiple tables at once. But you can use LAST_INSERT_ID() (I'm sure there's an equivalent php function) to get the new photoID. You obviously know userId already. Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739748 Share on other sites More sharing options...
Mchl Posted January 18, 2009 Share Posted January 18, 2009 But you can use LAST_INSERT_ID() (I'm sure there's an equivalent php function) to get the new photoID. mysql_insert_id And its mysqli counterpart of course Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739752 Share on other sites More sharing options...
phparray Posted January 18, 2009 Share Posted January 18, 2009 Sounds like your confusing foreign keys a bit. They only have two cascading features. ON UPDATE and ON DELETE. There is not ON INSERT and therefore your idea won't work inherently. You can accomplishes this while placing the burden on MySql by using a trigger. See: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739753 Share on other sites More sharing options...
midgley Posted January 18, 2009 Author Share Posted January 18, 2009 you spelled it out to me there, i was thinking that mysql was capable of cascading updates and thus saving me from typing out more querys. so as i understand it then, using mysql as a relational database is more about setting constraints than actually shortoning the code required to carry out a particular job. might look into this trigger thing but for now im going to keep things simple. so mysql_insert_id will retreive the last id inserted into the database. Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739757 Share on other sites More sharing options...
shlumph Posted January 18, 2009 Share Posted January 18, 2009 so mysql_insert_id will retreive the last id inserted into the database. Yep, and you can then use that ID to insert data into another table. Kewl! Link to comment https://forums.phpfreaks.com/topic/141330-how-to-insert-data-into-relational-database/#findComment-739762 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.