Member Avatar for kako13

Hi, I have a table in MySQL that I want to update it if the row exist or insert the record does not exist.

mysql_query("REPLACE INTO `$it` (file_name, directory)
                VALUES ('$insert','$dir_http')"); // or die ("Insert query failed");

I was following this: http://dev.mysql.com/doc/refman/5.1/en/insert.html

But every time I run REPLACE INTO it duplicate the records instead of update/insert.

Example:

1st Time Query Execution
ID 1 Name: abc.txt
ID 2 Name: dct.txt

2st Time Query Execution
ID 1 Name: abc.txt
ID 2 Name: dct.txt
ID 3 Name: abc.txt
ID 4 Name: dct.txt

Table Structure:

mysql_query (" CREATE TABLE IF NOT EXISTS `$it`
				(id int NOT NULL AUTO_INCREMENT, file_name VARCHAR(40) NOT NULL, directory VARCHAR (60) NOT NULL,
				FULLTEXT (file_name), PRIMARY KEY (id) ) ") or die ("Create table query failed");
  • 3 Contributors
  • 7 Replies
  • 3K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by almostbob

Dani AI

Generated

the behavior you saw is expected. As hinted, REPLACE only matches existing rows on a PRIMARY KEY or UNIQUE index. With only id as the PK and file_name in a FULLTEXT index (which is for search, not uniqueness), each REPLACE becomes a fresh insert, hence the duplicates. Also note that REPLACE actually deletes the old row and inserts a new one, which can reset auto_increment IDs or other columns you did not include. (dev.mysql.com)

The robust fix is to add a UNIQUE key that reflects your natural key, then use an idempotent insert. If the pair must be unique per directory, make a composite key; if file names are globally unique, key only file_name.

-- Option A: file name unique per directory
ALTER TABLE `your_table`
  ADD UNIQUE KEY uniq_dir_file (directory, file_name);

-- Idempotent write (no change on duplicates)
INSERT IGNORE INTO `your_table` (file_name, directory) VALUES (?, ?);

If file names are globally unique and you want to update the directory when a duplicate name is seen:

ALTER TABLE `your_table`
  ADD UNIQUE KEY uniq_file (file_name);

INSERT INTO `your_table` (file_name, directory)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE directory = VALUES(directory);

These patterns rely on a UNIQUE index to detect duplicates and either skip or update atomically. (dev.mysql.com)

One more modernization tip building on ’s suggestion: the mysql_* API used in the thread was removed in PHP 7. Use mysqli (or PDO) with prepared statements to avoid SQL injection and quoting bugs.

$stmt = $mysqli->prepare(
  "INSERT INTO `your_table` (file_name, directory)
   VALUES (?, ?) ON DUPLICATE KEY UPDATE directory = VALUES(directory)"
);
$stmt->bind_param('ss', $path_base_name, $dir_http);
$stmt->execute();

See the PHP manual for prepared statements and the deprecation notice for the old mysql_* extension. (php.net)

Recommended Answers

All 7 Replies

Member Avatar for matthewl

There is a couple ways you can solve this. One way is pre-query to see if it exist. In my opinion this is the easiest method of checking if the row exists. The only disadvantage to this method is that it take more time writing code and it can take more processing time to process the page which is often nanoseconds.

$query = mysql_query("SELECT * FROM `table` WHERE `file_name` = '$filename' AND `directory` = '$directory'");
$result = mysql_fetch_assoc($query);
$num = mysql_num_rows($query);
if($num) {
mysql_query("UPDATE `table` SET `file_name` = '$filename', `directory` = '$directory' WHERE `id` = '".$result['id']."'");
} else {
mysql_query("INSERT INTO `table` (`id`, `file_name`, `directory`) VALUES (NULL, '$filename', '$directory')");
}

The other method is using 'ON DUPLICATE KEY' or an 'OR' Statement via MYSQL you can find more info at http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html. THis method takes less time processing, but it can be more confusing trying to understand the query you wrote.

Member Avatar for almostbob

.REPLACE works exactly like INSERT,..except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index

http://dev.mysql.com/doc/refman/5.1/en/replace.html

the primary key ID is not referenced by the replace statement, the replace wont work as you intend, it becomes an insert statemen

Member Avatar for kako13

There is a couple ways you can solve this. One way is pre-query to see if it exist. In my opinion this is the easiest method of checking if the row exists. The only disadvantage to this method is that it take more time writing code and it can take more processing time to process the page which is often nanoseconds.

$query = mysql_query("SELECT * FROM `table` WHERE `file_name` = '$filename' AND `directory` = '$directory'");
$result = mysql_fetch_assoc($query);
$num = mysql_num_rows($query);
if($num) {
mysql_query("UPDATE `table` SET `file_name` = '$filename', `directory` = '$directory' WHERE `id` = '".$result['id']."'");
} else {
mysql_query("INSERT INTO `table` (`id`, `file_name`, `directory`) VALUES (NULL, '$filename', '$directory')");
}

The other method is using 'ON DUPLICATE KEY' or an 'OR' Statement via MYSQL you can find more info at http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html. THis method takes less time processing, but it can be more confusing trying to understand the query you wrote.

Thanks for your response. I modify the code you wrote:

$query = mysql_query("SELECT * FROM `$it` WHERE `file_name` = '$path_base_name' AND `directory` = '$dir_http'");
    $result = mysql_fetch_assoc($query);
    $num = mysql_num_rows($query);
 
    if($num) // if the record exist, update it
	  	{
		mysql_query("UPDATE `$it` SET `file_name` = '$path_base_name', `directory` = '$dir_http' WHERE `id` = '" . $result['id'] . "'");
      	} 
		
	else // else insert new record
	  	{ // else began
    
	mysql_query("INSERT INTO `$it` (`id`, `file_name`, `directory`) VALUES (NULL, '$path_base_name', 'dir_http')");
      	} // else end 
	  
	} // if end

It behave strange. It duplicate the records.

Member Avatar for kako13

Thanks. Solved.

$query = mysql_query("SELECT * FROM `$it` WHERE `file_name` = '$path_base_name' AND `directory` = '$dir_http'");
    $result = mysql_fetch_assoc($query);
    $num = mysql_num_rows($query);
 
    if($num) // if the record exist, update it
	  	{
		mysql_query("UPDATE `$it` SET `file_name` = '$path_base_name', `directory` = '$dir_http' WHERE `id` = '" . $result['id'] . "'");
      	} 
		
	else // else insert new record
	  	{ // else began
         mysql_query("INSERT INTO `$it` (file_name, directory) VALUES ('$path_base_name','$dir_http')");
      	} // else end
Member Avatar for matthewl

Your welcome.

Member Avatar for almostbob

Do you know any other way?

None than than already shown in the prior posts,
replace references the unique identifier/index, the only part of the row that remains the same after the replace

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.