0

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
15
Views
8 Years
Discussion Span
Last Post by almostbob
1

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.

0

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.

0

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
0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.