[question]: how do i preserve id in mysql db before insert?

preserve id,mysql

[ALKateb]

Hello!
i am coding an article directory and i faced this problem!
those articles will have attachments attached to them! so there is the articles table in the database and the attachments table and uploading attachments will happen before the articles in inserted in the database so basicly we have no article ID! but still we need to add the article ID to the attachments in the attachments table!
i could do it like inserting an empty record in the database when someone start writing and article and then attaching the attachments to it when they are uploaded and once he submit the article it will be updated!

but i did not consider this solution as it's gonna take me lots of time which i don't have! probably this is the best solution but my question is:

is there another way to do this? less time consuming way!

i thought of storing all attachments IDs in a session and once the article is submitted it will perform a query and update all attachments to set the (attachment_article_id) equals to the last inserted id which is the article and the attachments IDs which it will perform the query on will be taken from a session!

this might do it! but i did not feel good about this at all! : ) i don't know why!

so if there is any other way please tell me :)
thanks in advance

[coopster]

Can we assume the article and attachments are being created and submitted in the same form? If so, insert the article first and when you insert the attachments you can use the MySQL LAST_INSERT_ID [google.com].

[ALKateb]

i could do so but the problem is there will be multiple attachments and the user will use those attachments in the article so he has to upload them then embeds them in the article so the attachments has to be uploaded before the topic is submitted so he can specify where the uploaded image or video will be placed in the article.

so what do u suggest?

[eelixduppy]

I'd suggest not having the association between the article and the attachments in the article table but rather in the attachments table. You are going to have to loop in some way to put all the attachments into the table, and when you do you should grab the last most insert id. If you know how many attachments were added you now have all the inserted ids. You should store these in a global variable of some kind (or print them into a hidden form element or something to keep track of it) and then when you submit the article you'll have to update those rows in the attachments table with the articles id. If you do it all from one form you can bypass the storing the ids in a global variable, which might make more sense if you can work it out like that.

I guess one more option would be to not insert the attachments into the database after they are uploaded, but rather just store their new location on the server until you are ready to submit the article, and then you can add everything to the database all at once instead of separately. Try playing around with it, though, and see what you can come up with.

[rocknbil]

Keep in mind browsers get closed and with them go hidden fields, sessions die, articles can go through various states of edit before making them live, any number of things can go wrong. You want to "mark" everything that's done so it can be recovered without leaving orphan records or images that don't link to anything.

What I would do is associate the images with the user id first, in addition to the article. This way, the user can always manage their own images. Make them create the article first, even if it's not "active." You can then assign images to a given article as you will have the article record ID to store in the attachments table. They can edit it ad nauseum and when it's ready, set it to active (or this can be administrative moderation for the active state.)

¦id¦img_id¦article_id¦user_id¦filename¦directory¦date_added

[ALKateb]

rocknbil
thanks for ur suggestion .. but actually the articles could be posted by guests so this would not work for me.

eelixduppy
i'm not sure where i said i want to creat the relation in the articles table maybe i mixed things up i'm sorry and yes i'm making it in the attachments table so there is a field in the attachment table named like (attachment_article_id) which refers to the id of the article this attachment it attached to

in the beginning i considered sending the attachments IDs in the form and when submitted go update them to attach them to the right article but i was afraid of someone trying to manupilate this since it's being sent in the form

but! when u mentioned it u gave me the idea of how to do it!

i guess am going to do it like this:

when the user upload the first attachment a random number or hash will be created and assigned to the (attachment_article_id) then as he keeps uploading new files they all will take the same random number for this field and once he is done and submit the article a javascript function will call the random number and store it in hidden field then a query will be performed and update all records in attachments table where (article_attachment_id) = the random number

in this case if the user manupilated the random number nothing is gonna happen to other articles or other attachments so he will only be harming his own article and i dont think someone submitting a real article will try to do this ^_^

and as rocknbil said it's not good to leave "orphan records or images" but in this case since there is random number or (hash) set to these records which are (orphan) a mass delete to these kind of records and files can be performed occasionally by the administrator or by the script itself

so i guess there is no problem with what i'm about to do or is there?