Using the REPLACE INTO with PHP

[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");

[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.

[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

[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.

[kako13]

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

Do you know any other way?

[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

[matthewl]

Your welcome.

[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