insert update on duplicate key support.

[shaq147]

I've implemented a quick support for inserting on duplicate key update for those interested.

Use case:

$db->insert( array('id', 'field1', 'field2', 'field3') )
    ->into('table')
    ->values('1', 'value1', 'value2', 'value3')
    ->updateOnDuplicate( array('field2' => 'value2', 'field3' => 'value3') );

PDO/Statement/InsertStatement.php added 2 functions, 3 private variables, and updated the __toString method

private $_updateOnDuplicate = false;
private $_updateValues = array();
private $_updateFields = array();

public function updateOnDuplicate($fieldsValues)
{
	$fieldValues = (array) $fieldValues;

	if(!empty($fieldValues) ) {
		$this->_updateOnDuplicate = true;
	        $this->_updateValues = array_values($fieldValues);
	        $this->_updateFields = array_keys($fieldValues);
        }	
        return $this;
}

protected function _prepareUpdateFields()
{
	$this->setValues($this->_updateValues);

	$str = '';
	$commaFlag = false;
	foreach($this->_updateFields as $field) {
		if($commaFlag) $str .= ', ';
		$str .= $field . ' = ?';
		$commaFlag = true;
	}
	
	return $str;
}

//Updated
public function __toString()
{
	if (empty($this->table)) {
		trigger_error('No table is set for insertion', E_USER_ERROR);
	}

	if (empty($this->columns)) {
		trigger_error('Missing columns for insertion', E_USER_ERROR);
	}

	if (empty($this->values)) {
		trigger_error('Missing values for insertion', E_USER_ERROR);
	}

	$sql = 'INSERT INTO '.$this->table;
	$sql .= ' '.$this->getColumns();
	$sql .= ' VALUES '.$this->getPlaceholders();
	
	if($this->_updateOnDuplicate) {
		$sql .= ' ON DUPLICATE KEY UPDATE '.$this->_prepareUpdateFields();
	}

	return $sql;
}

[stebogit]

Hello,
great job @shaq147!

How come this hasn't been added to the main project?
I'd say it's a convenient functionality, no?

[techjwalker]

@shaq147 Did you submit a PR for this? If the functionality works, I would love to see this implemented.

EDIT: Made a PR for it myself, with documentation updates to reflect the changes.

[kwhat]

@shaq147 do you want to make pull request with the code above? I think we should add this to 2.1 or maybe even 2.0.

[kwhat]

This feature request will go hand 'n hand with the #104 request as the features are somewhat related.

[kwhat]

I will be working on this tomorrow. I think we can just add a $pairs array property and a duplicate(?array $paris = null) method on the Statement\Insert class.