<?php
/*
Okay, here is an easy one, I've practically written it for you:


Create a function that will insert a new record or update an existing record for a database table that has at least one, and possibly more, primary keys.
The data to be put into the table shall be stored in an associative array containing a column names as the key and column values as the value.  

The function will take as input the following parameters:

$db:					PDO object to be used to access the database
$table_name: 	String matching a database table name that will have values that are to be inserted or updated.
$row:					An associative array of keys/values that represent a column name as the key and column data as 
              the values to be inserted or updated in the database table.

The function must use php PDO and **prepared arrays**

Operation
=========

1. Query the database for the primary keys in the table specified and create a new list 
   that is the intersection of the keys in the table and the keys in the $row array.

			$primary_keys is an array of column names that make up the primary keys for the table

2. Check if the unique row exists
    example query:
				SELECT count(*) AS count FROM $table_name WHERE `key1`=? and `key2`=? ...

3. If the result of the above function is equal to zero use the data to build an insert statement from the $row associative array.
    example query:
				INSERT INTO $table_name VALUES (?,?,?,...);

4. If the result of the above function is greater than zero use the data to build an update statement from the $row associative 
   array and the where clause from the $primary_keys array from requrement 1 above.
    example query:
				UPDATE $table_name
				SET `nonkey3`=?,`nonkey4`=?,...
				WHERE `key1`=? and `key2`=? ...;

Error detection
===============

o. If the $row array does not contain necessary keys to uniquly detect a row of the table an exception should be thrown
o. If an insert or excption fails throw and exception with a message as to why the operation failed.

The resultant function should work with the following version of mysql and php:

# mysql --version
mysql  Ver 14.14 Distrib 5.6.28, for Linux (x86_64) using  EditLine wrapper
# php --version
PHP 5.5.18 (cli) (built: Nov 12 2014 19:59:36)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2014 Zend Technologies
    with the ionCube PHP Loader v4.6.1, Copyright (c) 2002-2014, by ionCube Ltd.

*/

//usage example:

	$dbname ="mydb";
	$db_user ="myuser";
	$db_password ="mypassword";

	$db = new PDO("mysql:host=localhost;dbname=$dbname",$db_user,$db_password);
	$table_name = "user";
	$row = array(
								"email" => "wile@pontech.com",
								"fullname" => "Wile E. Cyote",
							);
							
	create_update($db, $table_name, $row);

function create_update($db, $table_name, $row)
{
	/*
			1. Query the database for the primary keys in the table specified and create a new list 
				 that is the union of the keys in the table and the keys in the $row array.
	
					$primary_keys is an array of column names that make up the primary key
	*/
	$debug = 0;
	if($debug) print "Extracting list of primary_keys for $table_name:\n";
	$statement = "SHOW KEYS FROM `$table_name` WHERE Key_name='PRIMARY'";
	$prepared_array = array();
	$sth = $db->prepare($statement);
	$sth->execute($prepared_array);

	while($db_row = $sth->fetch(PDO::FETCH_BOTH)) {
		$key = $db_row["Column_name"];
		if($debug) print("$key\n");
		if( array_key_exists ($key , $row) ) {
			$primary_keys[$key] = $row[$key];
		}	
		else {
			// Error detection
			// ===============
			// o. If the $row array does not contain necessary keys to uniquly detect a row of the table an exception should be thrown
			$error = 'primary key missing from $row data';
			throw new Exception($error);
		}
	}
	if($debug) print_r($primary_keys);

	/*
			2. Check if the unique row exists
					example query:
							SELECT count(*) AS count FROM $table_name WHERE `key1`=? and `key2`=? ...
	*/

	$statement = "SELECT count(*) as count FROM `$table_name` WHERE ";
	$where_clause = "";
	$key_count = 0;
	foreach($primary_keys as $key=>$value) {
		if($where_clause != "") {
			$where_clause = $where_clause . " AND ";
		}
		$where_clause = $where_clause . "$key=?";
		$where_prepared_array[$key_count] = $value;
		$key_count++;
	}
	$statement = $statement . $where_clause;
	if($debug) print($statement . "\n");
	if($debug) print_r($where_prepared_array);
	$sth = $db->prepare($statement);
	$sth->execute($where_prepared_array);
	$db_row = $sth->fetch(PDO::FETCH_BOTH);
	$count = $db_row['count'];
	if($debug) print("Count = $count\n");

	// Build update and insert statements
	$insert_value_clause = "";
	$insert_column_clause = "";
	$update_value_clause = "";
	$key_count = 0;

	foreach($row as $key=>$value) {
		if($insert_value_clause != "") {
			$insert_value_clause = $insert_value_clause . ", ";
			$insert_column_clause = $insert_column_clause . ", ";
			$update_value_clause = $update_value_clause . ", ";
		}
		$insert_column_clause = $insert_column_clause . "`$key`";
		$insert_value_clause = $insert_value_clause . "?";
		$insert_prepared_array[$key_count] = $value;

		$update_value_clause = $update_value_clause . "`$key`=?";
		$update_prepared_array[$key_count] = $value;
		$key_count++;
	}

	$insert_statement = "INSERT INTO `$table_name` (" . $insert_column_clause . ") VALUES(" . $insert_value_clause . ")";
	$update_statement = "UPDATE `$table_name` SET " . $update_value_clause . " WHERE " . $where_clause;

	/*
		3. If the result of the above function is equal to zero use the data to build an insert statement
				example query:
						INSERT INTO $table_name VALUES (?,?,?,...);
	*/
	if( $count == 0 ) {
		$statement = $insert_statement;
		$prepared_array = $insert_prepared_array;
	}
	/*
		4. If the result of the above function is greater than zero use the data to build an update statement
			example query:
					UPDATE $table_name
					SET `nonkey3`=?,`nonkey4`=?,...
					WHERE `key1`=? and `key2`=? ...;
	*/
	else {
		$statement = $update_statement;
		$prepared_array = array_merge($update_prepared_array, $where_prepared_array);
	}
	if($debug) print($statement . "\n");
	if($debug) print_r($prepared_array);
	$sth = $db->prepare($statement);
	if( $sth->execute($prepared_array) == false ) {
		$arr = $sth->errorInfo();
		throw new Exception($arr[2]);
	}
}