Quick n dirty template to generate simple sql query: INSERT INTO TABLE ON DUPLICATE KEY UPDATE ...

createSqlInsertIntoTableOnDuplicateKeyUpdate

/**
 * @param string $table
 * @param array $rowInsert
 * @param array $rowUpdate
 * @return array
 */
function createSqlInsertIntoTableOnDuplicateKeyUpdate($table, $rowInsert, $rowUpdate)
{
    $insertColumnTokens = array();
    $insertValueTokens = array();

    $updateTokens = array();

    $params = array();

    foreach($rowInsert as $column=>$value) {
        $paramName ='INSERT_'.$column;
        $params[$paramName] = $value;

        $insertColumnTokens[] = $column;
        $insertValueTokens[] = ':'.$paramName;
    }

    foreach($rowUpdate as $column=>$value) {
        $paramName ='UPDATE_'.$column;
        $params[$paramName] = $value;


        $updateTokens[] = ''.$column.' = '.':'.$paramName;

    }

    $sqlInsert = '('.implode(', ',$insertColumnTokens).')'
        .' VALUES(
              '.implode(' , ', $insertValueTokens).'
        )';

    $sqlUpdate = implode(' , ',$updateTokens);


    $sql = 'INSERT INTO '.$table
        .' ' . $sqlInsert.' ON DUPLICATE KEY UPDATE '.$sqlUpdate
    ;

    $result = array(
        'sql'=>$sql,
        'sqlInsert'=>$sqlInsert,
        'sqlUpdate'=>$sqlUpdate,
        'params'=>$params,
    );

    return $result;
}