Mysql/Insert&Update

[guest]

Well, all i wanna do is insert first time and update the rest.
like
if (Insert == error_duplicate) {
update instead
}

[FMB]

if your table uses an auto-increment field and unique fields, you could just go ahead and issue the INSERT Query, after that query check MYSQL_INSERT_ID to see if a new auto-increment value is made, in that case, the insert was ok. If mysql_insert_id returns 0, then you have to issue an UPDATE query.

If you have no auto-increment field and/or no unique fields, you will have to do a check first:

$query = mysql_db_query($db,"SELECT * FROM <table> WHERE <conditions>");
if (mysql_num_rows($query)==0) {
   ... insert query ....
} else {
   ... update query ...
}

[tsinka]

Hi,

if you have a unique key and try to insert data with a value that already exists in the field having a unique key you should rather fetch the mysql error code to make sure that you have a duplicate key problem and not another problem because of e.g. a syntactical error in your query or another problem like

  $link = mysql_connect("host","user","pass");
  mysql_select_db("thedb",$link);
  if (!@mysql_query("INSERT INTO ....",$link)) {
    if (mysql_errno($link) == 1062) {
      // execute update query
    } else {
      // another error
      echo "Error: ".mysql_error()."<br>\n";
    }
  }

Thomas

[Roger_Ramjet]

Originally posted by guest
Well, all i wanna do is insert first time and update the rest.
like
if (Insert == error_duplicate) {
update instead
}

What you may want is REPLACE instead of INSERT: http://dev.mysql.com/doc/mysql/en/REPLACE.html

"REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted."

This is much simpler if there is no additional data in the existing record that you need to conserve. If there is then you can use 2 simple SQL queries to do it all for you with no need for any PHP.

An UPDATE can only update existing records, so running an insert first will update the existing records. No need for any scripting.

An INSERT will only be able to insert new records, this is what a PRIMARY KEY is for. Again, no need for any scripting.

In both cases myssql_affected_rows will return the number of rows inserted or updated (if you need to know) http://uk.php.net/function.mysql-affected-rows

[Roger_Ramjet]

Originally posted by guest
Well, all i wanna do is insert first time and update the rest.
like
if (Insert == error_duplicate) {
update instead
}

What you may want is REPLACE instead of INSERT: http://dev.mysql.com/doc/mysql/en/REPLACE.html

"REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted."

This is much simpler if there is no additional data in the existing record that you need to conserve. If there is then you can the On DUPLICATE KEY form of the INSERT query:

"If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and already contains the value $var1, the following two statements have identical effect:

mysql> INSERT INTO table (a,b,c) VALUES ($var1,$var2,$var3)
-> ON DUPLICATE KEY UPDATE b=$var2,c=$var3;

mysql> UPDATE table SET b=$var2,c=$var3 WHERE a=$var1;" http://dev.mysql.com/doc/mysql/en/INSERT.html

If a=$var1 exists then an update is accomplished, if it does not exist then the insert is accomplished.

The second solution allows for updating of selective columns so that things like date inserted, last accessed, etc can be preserved.