is there a way to ensure unique entries added to db

[mpsn]

Hi, I am frustrated with getting db to store just unique entries, b/c every time I refresh the browser, the script runs and re-inserts the SAME INSERT query (but with different primary key of course which I don't want).

 

My ques is is there a way upon the first insertion to tell it that this will be only instance of this record entered for the specified db table.

 

So I don't want to have to encapsulate each INSERT statement in an if block to make sure that only one record exists so now I am doing it incorrectly with exit statement.

 

please see code below:

<?php
$_filePath="C:\\\dir\\\email.xml";
$node=basename($_filePath);
$dom=new DOMDocument();
$dom->load($node);
$labelPath=array();
$connection=mysql_connect("localhost","root");
mysql_select_db("dummydpev999");
$isXdocExist=mysql_query("SELECT file_Path,file_Name FROM xdocument WHERE file_Path='$_filePath' AND file_Name='$node'");

$docId=0;

if(mysql_num_rows($isXdocExist)==1) {
print "Entry already exists!";
$docId=mysql_next_id("xdocument")-1;
print "<br />".$docId;
exit;
}
else {
mysql_query("INSERT INTO xdocument (file_Path,file_Name) VALUES ('$_filePath','$node')");
$docId=mysql_next_id("xdocument")-1;	
print "<br />".$docId;
}

writeXMLtoDBViaDOM($dom->documentElement,$labelPath,$docId);
?>

 

Sorry I should elaborate, I think my problem is that I have tables with foreign keys rather than all tables with just primary keys, I am unsure...

 

Any help much appreciated.

[ddubs]

What if you were to take the full path name with the file name say "/path/to/file/filename" and create a signature using md5. Then make the table's primary key the signature and make sure the unique flag is set. So:

 

<?php
$file_sig = md5($path.$file);
?>

 

Then you could drop any auto-increment id-type column on this table.

[mpsn]

Sorry I will be more clear, what I mean is that I have 4 db tables, table1 has PK, table2 has PK, table3 has PK and refs table1,table2 (so it has 2 FKs) and table4 has PK and refs table3

 

In short, I want to store xml to db and each node component is stored in these tables but table1 just stores the xml file name (something.xml and filepath: c:\dir\dir1\something.xml)

 

As you can see above, I have an if($isXdocExist) that ensures just one of the something.xml is loaded b/c each time I reload the browser, the script re-enters SAME entries for table3, table4 and NOT table1, table2, so that is why I suspect is the FK causing issue.

 

Any help much appreciated!

[ddubs]

Yes but it seems like you are relying on PHP logic rather than well structured SQL logic to account for existing entries. Its difficult to grasp your data model with out more information or some sort of ERD :\