We have a knowledge management database. This database reflects all files that exists in a certain location. The database then links various files, to various clients, disciplines, projects and sectors and each has it's own comment.
The clients, disciplines, projects and sectors tables all look the same.
Table example:
Code: Select all
|==|==============|
|id|client |
|==|==============|
|1 |*client name* |
|2 |*other client*|
|==|==============|
Code: Select all
|==|==============|==============|
|id|file |client |
|==|==============|==============|
|1 |**file id |***client id |
|2 |**other id |***other id |
|==|==============|==============|
I did it this way to keep database size down.
Anyway, my code keeps putting double values in. It keeps inserting clients even if they are already in the database. Please help. Here is my code:
Code: Select all
// check if they exist in the database, if they do, save ID, if they don't create ID and then save
if(count($clients) > 0)
{
mysql_query("DELETE FROM fileclients WHERE file = '" . $id . "'") or die(mysql_error()); // remove duplicates
for($k = 0; $k < count($clients); $k++)
{
$query = "SELECT id FROM clients WHERE client = '" . $clients[$k] . "'"; // Take id to link file to client
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result) > 0)
{
$row = mysql_fetch_assoc($result); // fetch the result (will only be one)
$rowId = $row['id'];
mysql_query("DELETE FROM fileclients WHERE file = '" . $id . "' AND client = '" . $rowId . "'"); // remove duplicates
mysql_query("INSERT INTO fileclients (file, client) VALUES ('" . $id . "', '" . $rowId . "')") or die(mysql_error()); // assign the file id to the client ID.
}
else
{
mysql_query("INSERT INTO clients (client) VALUES ('" . $clients[$k] . "')") or die(mysql_error()); // Insert new client into database
$i_id = mysql_insert_id();
mysql_query("INSERT INTO fileclients (file, client) VALUES ('" . $id . "', '" . $i_id . "')") or die(mysql_error()); // assign the file id to the client id
}
}
}