Inserting a value only once (avoiding duplicates)

Get help on web editors (Frontpage, Dreamweaver) and web languages (HTML, ASP, PHP).
Post Reply
DarkRanger
Registered User
Posts: 8346
Joined: 10 May 2006, 02:00
Processor: Intel i5-3750
Motherboard: Gigabyte
Graphics card: nVidia GTX 550Ti
Memory: 8GB Jetram
Contact:

Inserting a value only once (avoiding duplicates)

Post by DarkRanger »

So I've tried this countless times and every time it doesn't work as it's supposed to, so I need some advice.

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*|
|==|==============|
Then, there are also tables for linking files to clients etc which all look like this:

Code: Select all

|==|==============|==============|
|id|file          |client        |
|==|==============|==============|
|1 |**file id     |***client id  |
|2 |**other id    |***other id   |
|==|==============|==============|
now, in the clients table, clients are unique. and in the second table the id's of those clients just get linked to the id's of the files (obviously if its linked to them by a user).

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
			}
		}
	}
PS: clients and so are received as an array().
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Inserting a value only once (avoiding duplicates)

Post by RuadRauFlessa »

Code: Select all

mysql_num_rows($result)
obviously results to <= 0 so your code keeps going into the else part of the if statement.

output the value of $result before the if statement and check what it is. It could be that the value in $clients[$k] is not the exact same value as it is in the database.

That would be my first thoughts.

Slightly off topic though I would actually advise that you make it impossible to create a link before a client has been loaded. Basic process flow rather than trying to force this. Then you won't have the issue. Or rather you should not have the issue. Also with the linking the user obviously has to select the client and at such a point you could more easily have a drop down with possible clients and have their id's returned to you rather than the client's name in text. This would mean that you always have a valid client to work with and would eliminate a lot of issues like this before they even crop up.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
DarkRanger
Registered User
Posts: 8346
Joined: 10 May 2006, 02:00
Processor: Intel i5-3750
Motherboard: Gigabyte
Graphics card: nVidia GTX 550Ti
Memory: 8GB Jetram
Contact:

Re: Inserting a value only once (avoiding duplicates)

Post by DarkRanger »

RuadRauFlessa wrote:

Code: Select all

mysql_num_rows($result)
obviously results to <= 0 so your code keeps going into the else part of the if statement.

output the value of $result before the if statement and check what it is. It could be that the value in $clients[$k] is not the exact same value as it is in the database.

That would be my first thoughts.
I'll look into it...
RuadRauFlessa wrote:Slightly off topic though I would actually advise that you make it impossible to create a link before a client has been loaded. Basic process flow rather than trying to force this. Then you won't have the issue. Or rather you should not have the issue. Also with the linking the user obviously has to select the client and at such a point you could more easily have a drop down with possible clients and have their id's returned to you rather than the client's name in text. This would mean that you always have a valid client to work with and would eliminate a lot of issues like this before they even crop up.
That would be a good idea as I see (looking through the database) a lot of spelling mistakes cropping up. I actually never thought Stupid is going to be the one that has to update the database. I thought that they'd appoint someone with a smidgen of an IQ at least...
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Inserting a value only once (avoiding duplicates)

Post by RuadRauFlessa »

Always remember
Rich Cook wrote: Programming today is a race between software engineers striving to build bigger and better idiot- proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
DarkRanger
Registered User
Posts: 8346
Joined: 10 May 2006, 02:00
Processor: Intel i5-3750
Motherboard: Gigabyte
Graphics card: nVidia GTX 550Ti
Memory: 8GB Jetram
Contact:

Re: Inserting a value only once (avoiding duplicates)

Post by DarkRanger »

RuadRauFlessa wrote:Always remember
Rich Cook wrote: Programming today is a race between software engineers striving to build bigger and better idiot- proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Signature material that... Thank you
Image
Post Reply