how to insert data into relational database

[midgley]

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?

 

 

[Mchl]

Posting this image somewhere, where it is accessible without need to register would be helpful

[midgley]

good point

 

heres the new link:

http://www.ink-recycle.co.uk/deleteme.html

[trq]

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;

[midgley]

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

[midgley]

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

[fenway]

It can't auto-update a record that isn't there.

[midgley]

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.

[fenway]

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.

[Mchl]

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

[phparray]

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

 

[midgley]

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.

 

[shlumph]

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!