Update if an insert has been made

[Tezread]

I am strugglin to create a conditional update. I have an insert:

<?php 
include("include/session.php"); 

if(isset($_POST['submit'])){ 
    include("connect.php"); 
  //array to hold form errors       

  $form_error = array(); 
//validate your form data 

  if(!isset($_POST['activity_checkbox'])){ 
      $form_error[] = "No activities selected"; 
  } 

  if(!isset($_POST['expert_checkbox'])){ 
      $form_error[] = "No areas of expertise selected";       

  }       

  if(count($form_error) > 0){ 
      //hold the error in session so you can display the error in your form page. 
      $_SESSION['form_error'] = $form_error; 

  //redirect to the form 
  header("Location: http://www.mindseyemidlands.co.uk/notts_quality/info_resource/sourcesofhelp_page1.php");       

    }  else { 
      //do your data processing 

  $username = $_SESSION['username']; 
  foreach($_POST['activity_checkbox'] as $value){ 
      $sql = 'INSERT INTO supportactivityuser (`username`, `supportactivity_id`) VALUES ("'.$username.'", "'.$value.'")'; 
    $result = mysql_query($sql, $connection) or die(mysql_error()); 
    //execute your query 
  } 

  foreach($_POST['expert_checkbox'] as $value){ 
      $sql = 'INSERT INTO expertuser (`username`, `expert_id`) VALUES ("'.$username.'", "'.$value.'")'; 
    $result = mysql_query($sql, $connection) or die(mysql_error()); 
      //execute your query 
  } 

  //remove the form_error session 
  unset($_SESSION['form_error']); 
  //redirect to result display page 
  header("Location: http://www.mindseyemidlands.co.uk/notts_quality/info_resource/sourcesofhelp_page2.php");       
} 
}       

?> 

i cannot modify this so that if an insert has already been made it will update the record instead thus allowing the user as many tries as they want and the latest results being shown

thanks in advance

[NogDog]

You could do a REPLACE instead of an INSERT. See http://dev.mysql.com/doc/refman/4.1/en/replace.html for more info, and remember that at least one of the fields being set must be a primary key or have a unique index on it.

[bogu]

Or u can read the INSERT SYNTAX ...

[Tezread]

NogDog wrote:

remember that at least one of the fields being set must be a primary key or have a unique index on it.

in my case the table is called supportactivityuser.

the fields are

supportactivityuser_id (primary key)
username
supportactivity_id

basically a user can have one ore more entries i.e:

[Tezread]

i tried this but the replace didn't work

else { 
      //do your data processing 

  $username = $_SESSION['username']; 
  foreach($_POST['activity_checkbox'] as $value){ 
      $sql = 'REPLACE INTO supportactivityuser (`username`, `supportactivity_id`) VALUES ("'.$username.'", "'.$value.'")'; 
    $result = mysql_query($sql, $connection) or die(mysql_error()); 
    //execute your query 
  } 

this is becasue i cannot have the username set as unique as there will be more than one entry into the table. I have to use update. I have tried this a foreach update but it didn't work - now I lost the code I tried for the update - sorry.

[liquorvicar]

bogu had the answer for you.
Use "INSERT INTO... ON DUPLICATE KEY UPDATE"

[Tezread]

anyone??

[Tezread]

liquorvicar wrote:

bogu had the answer for you.
Use "INSERT INTO... ON DUPLICATE KEY UPDATE"

I notice in the manual it mentions:

. In general, you should try to avoid using an ON DUPLICATE KEY clause on tables with multiple unique indexes

[bogu]

Why dont u post your tables structures (simplified if posible only the important fields) and we'll see how to create your INSERT query ...

[Roger_Ramjet]

Tezread wrote:

I notice in the manual it mentions:

. In general, you should try to avoid using an ON DUPLICATE KEY clause on tables with multiple unique indexes

That is because your insert could easily clash with different unique keys in different rows, and the problem with that is that when in insert "matches several rows, only one row is updated."

Predicting which one that will be, or even finding out which one it was, is very difficult.

If you want to force the issue in your particular case then create a multi column index on the user and activity columns and make that your primary key. You can then use the REPLACE syntax without any problems.

[Tezread]

Just to clarify here are my table structures:

supportactivity table

supportactivity_id id the primary key here

http://www.mindseyemidlands.co.uk/notts_quality/info_resource/supportactivity.gif

supportactivityuser table

no indexes on this table set up as of yet but im thinking that username is the key field here

http://www.mindseyemidlands.co.uk/notts_quality/info_resource/supportactivityuser.gif

[liquorvicar]

You can't have username as any sort of unique key on the supportactivityuser table for obvious reasons. As Roger Ramjet suggested, a multi-column key on username and supportactivity_id would work and should then allow you to use REPLACE or INSERT... ON DUPLICATE KEY (see http://dev.mysql.com/doc/refman/4.1/en/replace.html for discussion of the differences). Note, however, that neither are part of the SQL standard.

[Tezread]

liquorvicar wrote:

You can't have username as any sort of unique key on the supportactivityuser table for obvious reasons. As Roger Ramjet suggested, a multi-column key on username and supportactivity_id would work and should then allow you to use REPLACE or INSERT... ON DUPLICATE KEY (see http://dev.mysql.com/doc/refman/4.1/en/replace.html for discussion of the differences). Note, however, that neither are part of the SQL standard.

Just so I understand this- how do i create a multi column key? does this involved changing ym database structure via phpmyadmin?

[liquorvicar]

If you're using PMA, then it's fairly straightforward. On the table structure page, there's a section below the main column info to add an index. Put 2 into the box marked "Create an index on" and click go. You can then select the 2 columns you want to create the index on. For reference: MySQL docs on Multiple indexes

[Tezread]

just to update on progress. this is my current solution which is bringing out an error:

  $username = $_SESSION['username']; 
      foreach($_POST['activity_checkbox'] as $value){ 
         $sql = 'INSERT INTO supportactivityuser (`username`, `supportactivity_id`) VALUES ("'.$username.'", "'.$value.'")
		 ON DUPLICATE KEY UPDATE supportactivity_id = $value';
 	$result = mysql_query($sql, $connection) or die(mysql_error()); 

  } 

  foreach($_POST['expert_checkbox'] as $value){ 
      $sql = 'INSERT INTO expertuser (`username`, `expert_id`) VALUES ("'.$username.'", "'.$value.'")
	   ON DUPLICATE KEY UPDATE expert_id = $value';
    $result = mysql_query($sql, $connection) or die(mysql_error()); 
      //execute your query 

  } 
  

the resulting error message is

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE supportactivity_id = $value'

here is my revised table structure with the multi column keys:

http://www.mindseyemidlands.co.uk/notts_quality/info_resource/multicolumn.gif

[liquorvicar]

Try

$sql = 'INSERT INTO supportactivityuser (`username`, `supportactivity_id`) VALUES ("'.$username.'", "'.$value.'") 
         ON DUPLICATE KEY UPDATE timestamp = NOW()';

BTW, you do have MySQL v.4.1+ don't you? INSERT...ON DUPLICATE KEY is only available from MySQL v4.1 onwards.

[Tezread]

same error message- im running MySQL 5.0

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE timestamp = NOW()' at line 2

[Roger_Ramjet]

The manual is contradictory on this but

"A common problem stems from trying to use an identifier such as a table or column name that is a reserved word such as SELECT or the name of a built-in MySQL data type or function such as TIMESTAMP or GROUP."

Now, further down the page it sez that 'timestamp' is permitted as an unquoted identifier. So which is true?

Personally, I have NEVER found any reason to use any datatype or function name as a table or column identifier. Absolutely no need whatsoever for that. So even if timestamp as the column name is not the problem you should change it - if only because other db engines are not as slack about this as mysql and bad habits are hard to break.

[liquorvicar]

Can you echo out the query that's failing? In my experience, MySQL quotes the SQL string starting from where the error is (or where it thinks the error is). That would rule out the quoted/unquoted issue on the timestamp column (although Roger Ramjet's point about not using that as a column name is a very valid one). Is it something as simple as not having a space between the closing bracket and ON?

[Tezread]

making some progress. renaming the timestamp column has helped. The ON DUPLICATE KEY approach is still not accepted so this may well be a version issue. If I use the alternative REPLACE INTO SET..... i.e:

  $username = $_SESSION['username']; 
  foreach($_POST['activity_checkbox'] as $value){ 
  $sql = 'REPLACE INTO supportactivityuser SET username = $username, supportactivity_id = $value'; 

$result = mysql_query($sql, $connection) or die(mysql_error()); 

  } 

  foreach($_POST['expert_checkbox'] as $value){ 
   $sql = 'REPLACE INTO expertuser SET username = $username, expert_id = $value'; 

    $result = mysql_query($sql, $connection) or die(mysql_error()); 
      //execute your query 

  } 

the error message reads;
Unknown column '$username' in 'field list'