PHP INSERT ... ON DUPLICATE UPDATE : array

[freakunleash]

Hi All,

Need your help.

I'm trying to create a small movie DB for my personal use. I have a input form from where I'm collecting all the data as an array and processing through PHP to MYSQL.

I have three tables where I have tried to create many-to-many relation between title & cast table through title_cast table. below is the structure of tables.
If im getting array data from the form for cast table where there is no existing cast present in the table than values are getting inserted properly. but if any cast is present in the array than it failed with duplicate entry error.

All I want is that if array data has values for any existing cast than it should update or skip it & rest of the script should continue but it should update the value in title_cast table to maintain relationship.

CREATE TABLE testing.title(
  id INT(5) NOT NULL AUTO_INCREMENT,
  title TEXT NOT NULL,
  `year` TEXT DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET latin1
COLLATE latin1_swedish_ci;


CREATE TABLE testing.cast(
  id INT(5) NOT NULL AUTO_INCREMENT,
  name TEXT DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX name (name)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET latin1
COLLATE latin1_swedish_ci;


CREATE TABLE testing.title_cast(
  id INT(5) NOT NULL,
  id_title INT(5) NOT NULL,
  id_cast INT(5) NOT NULL,
  role TEXT DEFAULT NULL,
  PRIMARY KEY (id, id_title, id_cast)
)
ENGINE = INNODB
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

PHP INSERT queries

//******************************************* TITLE UPDATE *******************************************//	
$sql_title = "INSERT INTO title (id, title, year) VALUES 
(NULL, '$title', '$year')";

if (!mysql_query($sql_title,$con))
  {
  die('Error: ' . mysql_error());
  }

$get_title_id = "SELECT LAST_INSERT_ID() as title_id";
$result = mysql_query($get_title_id);
$row = mysql_fetch_array($result);
$title_id = $row['title_id'];
echo $title_id;


//******************************************* CAST UPDATE *******************************************//	

	
$sql = array(); 
    foreach( $cast as $row ) {

		$name = mysql_real_escape_string($row['name']);
		$year = mysql_real_escape_string($row['year']);

       $sql[] = '( "'.($name).'", "'.($year).'")';
	}
	
    mysql_query('INSERT INTO cast (name, year) VALUES '.implode(',', $sql)) or die(mysql_error());
	
	//'ON DUPLICATE KEY UPDATE name = values('.$name.') 


//******************************************* TITLE_CAST UPDATE *******************************************	//

	$sql_query = "INSERT INTO title_cast (id_title, id_cast) 
					SELECT $title_id, hi.cast_id 
					FROM cast_inserts hi
					WHERE hi.conn_id = CONNECTION_ID()";
	if (!mysql_query($sql_query,$con))
	  {
	  die('Error: ' . mysql_error());
	  }

[diafol]

I can't help thinking that there's a better way to do this. What fields are sent by your form?

You seem to be passing year into cast table - why?

[freakunleash]

sorry that was wrong information for cast insert. I pasted the code of crew page which i was testing for something else. correct code is :

$sql = array();
			foreach( $cast as $row ) {
				$name = mysql_real_escape_string($row['name']);
				$role = mysql_real_escape_string($row['role']);
				$photo = mysql_real_escape_string($row['photo']);
				
				$sql[] = '( "'.($name).'", "'.($role).'", "'.($photo).'")';
			}
 
mysql_query('INSERT INTO cast (name, year, photo) VALUES '.implode(',', $sql)) or die(mysql_error());

Below are the values coming from the form:

For Title table:

$title = $_POST['title'];
$year = $_POST['year'];

For cast table value is coming through an array & array value look like:

$cast = array
  0 => 
    array
      'name' => string 'Johnny Depp'
      'role' => string 'Inspector Frederick Abberline'
      'photo' => string '../images/JohnnyDepp.jpg '
  1 => 
    array
      'name' => string 'Heather Graham'
      'role' => string 'Mary Kelly'
      'photo' => string '../images/HeatherGraham.jpg ' 
  2 => 
    array
      'name' => string 'Ian Holm'
      'role' => string 'Sir William Gull'
      'photo' => string '../images/IanHolm.jpg '
  3 => 
    array
      'name' => string 'Robbie Coltrane'
      'role' => string 'Sergeant Peter Godley'
      'photo' => string '../images/RobbieColtrane.jpg '
  4 => 
    array
      'name' => string 'Ian Richardson'
      'role' => string 'Sir Charles Warren '
      'photo' => string '../images/IanRichardson.jpg '

If there are better ways than please point me in right direction. i will try to explore those options also.

[diafol]

OK, I'll try again - please post your form (html) - so I can see how it works and which fields you have.