if file not exist insert in one table and take other values to insert in other table

Question

i have the following database 2 tables with PK_Hash_ID in md5 table and FK_Hash_ID in Files table.

Lets say for example I need to insert new record,

first I have to check if its MD5 exists in MD5 table, if not add it to the MD5 table and Files table, however if its already there on MD5 just take that Hash_ID and insert

it into files with some other values taken from the Parameters such File_name, Parent_Path, DataType,etc

Consider that the solution must be a Stored Procedure, That takes parameters

Possible duplicate of [MySQL: Insert record if not exists in table](https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table) — Dai, Sep 11 '17 at 06:52

score 0 · Answer 1 · answered Sep 11 '17 at 06:59

0

Make md5HASH column unique in md5 table, By which you can be sure that there will never be any duplicate entry in it. Try to insert each MD5HASH in md5 table, If query fails check for the error type if error is because of unique key constraint, that means the HASH already exists if no error HASH will be inserted into table md5. Sample code would be like

$sql = "insert into md5 values (null,'file_name',$hash)";
$result = mysqli_query($sql);
if( mysql_errno() == 1062) {
    //write the code which you want to execute in case of hash already existing 
} else {
   //write the code where hash doesn't existed earlier, but now it has already added in database. No need to add again.
}

I hope this helped.

answered Sep 11 '17 at 06:59

Vijay Rathore

577
7
14

in any case the files_tbl is going to have a new record its only the md5_tbl that i need to make sure nothing is dublicated for example i have (file_name , Prent_Path, etc ) first iam going to check md5 tbl if exist if its already exist take that md5_Id inserto the rest of the data in the Files_tbl ,, in other case if this md5hash not existed add it to the md5_tbl and then take the id again and insert the data to the files_tbl – Mohmmad S Sep 11 '17 at 07:11
Just make the md5Hash column unique. MySql will take care of the rest.You will have the value of HASH in all the cases so there is no need to select it from the database. – Vijay Rathore Sep 11 '17 at 07:13
ill try that for sure – Mohmmad S Sep 11 '17 at 07:14
Mark the answer as useful , If it helped you. – Vijay Rathore Sep 11 '17 at 07:14

Mohmmad S · Accepted Answer · 2018-10-22T17:35:32.150

Solved using IF EXISTS statement .

 BEGIN

If EXISTS (SELECT Hash_ID from MD5 Where MD5Hash = MD5Hash1)
Then
SELECT @H := Hash_ID from md5 where MD5Hash = MD5Hash1;
INSERT INTO MD5 Where MD5Hash = @H (Counter) values (+1);
INSERT INTO files (Hash_ID , File_Name , Path, Parent_Path , DataType )
values ( @H, FileName,Path, ParentPath, Type );
ELSE  
Insert INTO md5 (MD5Hash, Counter ) VALUES (MD5Hash1,+1);
SELECT @H := Hash_ID from md5 where MD5Hash = MD5Hash1;


INSERT INTO files (Hash_ID , File_Name , Path, Parent_Path , DataType )
values ( @H, FileName,Path, ParentPath, Type );
End IF ;
end

if file not exist insert in one table and take other values to insert in other table

2 Answers2