Prefix uploaded file's name with primary key value for record BEING inserted

[alpha2zee]

I am modifying a commonly used PHP/MySQL system (DaDaBik) in which pathnames to uploaded files are stored in the database while the actual files are in table-specific directories. (A user may or may not choose to upload a file for the record he is adding.) Currently, there is no deletion of files when records are deleted, and the way DaDaBik is written, the following may be the easiest way to achieve this objective.

I want to associate the filenames with records by prefixing primary key values to them. So, a file named abc.jpg will become 123_abc.jpg after uploading, 123 being the primary key value of the record. Deleting record 123 would thus just involve looking for any file with prefix 123_.

The question is how do I get the primary key value for a record BEING inserted? (The primary key may or may not be of auto_increment type.) That way I can write the block of code to rename the uploaded file within the PHP function that has the MySQL insert statement.

Perhaps, this is not possible. The other option then is to let the record be inserted successfully, get the primary key value, check which file was uploaded (how?) and then copy (for adding the prefix) and unlink (the original upload).

[devinemke]

why bother prefixing all the files, the file names are stored in the table. if i understand you correctly you simply want to delete the assocciated file when a record is deleted. if you are using your own script to delete records simply delete the file (using [man]unlink[/man]) right before you delete the record. if this file deletion is all happening after the fact (after records have been deleted) then just load the files into an array, load the records into as array, and then use [man]array_diff[/man] to calculate the difference, then delete the orphaned files.

[alpha2zee]

The DaDaBIK PHP application that allows one to create a highly customizable front-end for a MySQL database in order to search, insert, update and delete records (http://www.dadabik.org). Say I have two tables - artists and albums. DaDaBik will let me create table-specific front-ends (update an artist, delete an album, etc.). So I can have an insert-form generated for artists and another one for albums such that only the latter has a file-upload option.

Different tables can have none or many columns named anything that store the file path information as text. So text such as image.gif associated with a record may or may not refer to a file in one table and be the opposite in another. Because of this generalized nature, it is not possible to do what you suggest and the workarounds are more complicated than with what I want help for.

[devinemke]

so how do the files relate to database? you are saying there is not a fixed field in a fixed table that stores the file paths? there must be a way to relate the two, or perhaps i am still not undertsanding.

in any case, you can use [man]mysql_insert_id[/man] to grab the last primary key number and then simply [man]rename[/man] the file.

[alpha2zee]

Yes, DaDaBik is a complicated (but very useful for non-techies) system. It works by generating a new table for every MySQL table, the new table having information for the front-end (like providing a single-select menu for a particular form field).

Anyway, I found a way to get around my problem. Basically, the field that stores the file path information now stores that info with some 'tag' text - instead of 'uploads/gallery/image.gif,' the field gets a value of 'file located at uploads/gallery/image.gif.' When a record is being deleted, a check is done for any field value of form 'file located at ' and then is unlinked.