m-higgins
asked on
PHP / MYSQL restore broken link script in table...using file name with appended ID
Hello Experts...
I have a directory full of CV's and cover letters that carry regular expressions and an ApplicantID
eg:
CoveringLetterforVacancyRe f:005_Cove ringLetter _4_6663.do c
CV_CV_4_6074.doc
The file extention may also be .docx or .pdf
I also have a table in my database with the Applicants and their ID's that match.
My problem is (I didn't do a backup before performing a Structural sysnc on phpMyAdmin) I have lost just under 3K rows from another table that linked the ApplicantID to the filename located in "documents/applicant/cv"
Does anyone have any sugestions how I can fix this?
Here is the table info:
Table1: applications
ID
CandidateID
DateAdded
EncryptID
Table2: docs
ID
ApplicantID
Description
FileName
DateAdded
I'm thinking:
- 1st find the missing info between the two tables on 'CandidateID' AS 'ApplicantID'.
- Then use the PHP function if_file_exist ../documents/applicant/cv/ "CV-{Appli cantID}.do c" (or .docx, or .pdf etc)
- If it DOES, then insert a new row in the (docs) table:
copy "ApplicantID", "DateAdded", "EncryptID" from the (applications) table a pre-defind string for the "Description"
then add the filename in the "FileName" column thus restoring the link and we can download the documents again...
- And if there's any left maybe display a list??
I'm desperate right now as I seem to be fumbling along with no real results....
Can anyone please help
I have a directory full of CV's and cover letters that carry regular expressions and an ApplicantID
eg:
CoveringLetterforVacancyRe
CV_CV_4_6074.doc
The file extention may also be .docx or .pdf
I also have a table in my database with the Applicants and their ID's that match.
My problem is (I didn't do a backup before performing a Structural sysnc on phpMyAdmin) I have lost just under 3K rows from another table that linked the ApplicantID to the filename located in "documents/applicant/cv"
Does anyone have any sugestions how I can fix this?
Here is the table info:
Table1: applications
ID
CandidateID
DateAdded
EncryptID
Table2: docs
ID
ApplicantID
Description
FileName
DateAdded
I'm thinking:
- 1st find the missing info between the two tables on 'CandidateID' AS 'ApplicantID'.
- Then use the PHP function if_file_exist ../documents/applicant/cv/
- If it DOES, then insert a new row in the (docs) table:
copy "ApplicantID", "DateAdded", "EncryptID" from the (applications) table a pre-defind string for the "Description"
then add the filename in the "FileName" column thus restoring the link and we can download the documents again...
- And if there's any left maybe display a list??
I'm desperate right now as I seem to be fumbling along with no real results....
Can anyone please help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Checking to see if you've had a chance to try this out...
ASKER
Excellent gr8gonzo! sorry for the late reply....but thanks to you and your help I have managed to put something together that has sorted my situation.
Thank you again.
The fixlog was a nice touch! defo marking this one for another time...
Thank you again.
The fixlog was a nice touch! defo marking this one for another time...
SELECT * FROM applications t1 WHERE t1.CandidateID NOT IN (SELECT ApplicantID FROM docs)
So start by running the below script. It should be a dry run. It won't change anything in the docs or the database - it will just check to make sure it is possible to do the linking the way that has been suggested in your question and log the results to a file called fixlog.txt. This way you can be certain we're on the right track or if we need to adjust the script first.
Also need you to verify all the possible filename conventions used. You used both dashes - and underscores _ in your description of how the files are named, so we need to be certain of the potential results. The log should also contain a list of files that it was not able to figure out, but it's currently checking for the candidate ID at the end of the filename after an underscore and before the extension, like this: *_[CANDIDATE ID].[extension].
Open in new window