<?php
// 2017-04-12
// nvm
//
// Database wrapper function, v2
// Upgraded (after 7 long years) to support PDO
// and finally move on from the deprecated mysql_functions

define('MYSQL_TYPES_NUMERIC', 'int real');
define('MYSQL_TYPES_DATE', 'datetime timestamp year date time');
define('MYSQL_TYPES_STRING', 'string blob');

class db {

public $last_error;
public $last_query;
public $row_count;

public $host;
public $user;
public $pw;
public $db;

public $db_link;

public $charset;


/**
 * construct method
 * defines some class properties
 */
	public function __construct(){
		$this->host = '';
		$this->user = '';
		$this->pw = '';
		$this->db = '';
		$this->port = 3306;
		$this->charset = "utf8mb4";
	}


/**
 * connect method
 * defines some class prperties
 * instantiates PDO object and puts it to db_link property
 *
 * @param host obligatory
 * @param user obligatory
 * @param pw obligator 
 * @param db obligatory
 * @param persistant optional
 * @param port optional
 * 
 * @returns PDO object or false
 */
	public function connect($host='', $user='', $pw='', $db='', $persistant=false, $port=''){
		if(!empty($host)) $this->host = $host;
		if(!empty($user)) $this->user = $user;
		if(!empty($pw))   $this->pw = $pw;
		if(!empty($db))   $this->db = $db;
		if(!empty($port)) $this->port = $port;

		if($persistant) {
			$this->db_link = new PDO("mysql:host=$this->host;dbname=$db;port=$port;charset=$this->charset", $this->user, $this->pw, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING, PDO::ATTR_PERSISTENT => true));
			// after the instantiation of the object, $this->db_link->setAttribute(PDO::ATTR_PERSISTENT, true) //doesn't work
		} else {
			$this->db_link = new PDO("mysql:host=$this->host;dbname=$db;port=$port;charset=$this->charset", $this->user, $this->pw, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
		}

		if(!$this->db_link) {
			return false;
		}

		return $this->db_link;
	}


/**
 * select method with prepared statement
 * 
 * @param sql obligatory statement with substitutes instead of variables.  
 * @param array = variables which will replace substitutes in statement 
 * @param fetchMode defines format (object/array) of data to be returned 
 *
 * @returns prepared statement object
 */
	public function select($sql, $bindParams = []){
		$stmt = $this->db_link->prepare($sql);
		foreach($bindParams as $key => $value){
			$stmt->bindValue("$key", $value);
		}

		$stmt->execute();
		$this->last_query = $stmt->queryString;
		if( $stmt->errorCode() == 0 ) {
			$this->row_count = $stmt->rowCount();
			return $stmt;
		}else{
			$this->last_error = "prepareSelect method failed ";
			$this->print_last_error();
			return false;
		}
	}
	
	
/**
 * get_row returns a row of data from the query result
 */ 
	public function get_row($result, $type=PDO::FETCH_BOTH) { //TODO prove that $result is valid source
		if(!$result) {
			$this->last_error = "Invalid resource identifier passed to get_row() function. ";
			$this->print_last_error();
			return false;
		}
		if($type == 'MYSQL_ASSOC'){ $type = PDO::FETCH_ASSOC; }
		if($type == 'MYSQL_NUM'){ $type = PDO::FETCH_NUM; }
		if($type == 'MYSQL_BOTH'){ $type = PDO::FETCH_BOTH; }

		$row = $result->fetch($type);
		return $row;
	}


/**
 * select_one method
 * 
 * @param sql statement
 * @param bindParameters parameters to be binded to placeholders in sql statement. Not required
 *  
 * @returns 1.row of result set
 */
	public function select_one($sql, $bindParams=[]){
		//$r = $this->select($sql);
		$r = $this->select($sql, $bindParams);
		
		if($r === false){
			$this->last_error = "Invalid resource identifier passed to select_one() function. ";
			$this->print_last_error();
			return false;
		}

		if( $r->rowCount() < 1 ) { // no row found
			$this->last_error = "Query in function select_one() returned less than one result.";
			return false;
		}
		if( $r->rowCount() > 1 ) {
			$this->last_error = "Query in function select_one() returned more than one result.";
			return false;
		}
		
		$row = $r->fetch();
		return $row[0];
	}
	
	
/**
 * get_single_row method selects single row from result set
 * 
 * @param sql obligatory statement 
 * @param bindParameters parameters to be binded to placeholders in sql statement. Not required
 *
 * @returns assoc array containing one result set row.
 * Paradox: why this method expect to get just 1 result row??? 
 */
	public function get_single_row($sql, $bindParams=[]){		
		$stmt = $this->select($sql, $bindParams);
		if(!$stmt){
			$this->last_error = "Invalid resource identifier passed to get_single_row() function. ";
			$this->print_last_error();
			return false;
		}
		return $stmt->fetch(PDO::FETCH_ASSOC);
	}


/**
 * select_one method
 * 
 * @param sql statement
 * @param bindParameters parameters to be binded to placeholders in sql statement. Not required
 *  
 * @returns all rows of result set
 */
	public function select_many($sql, $bindParams=[]){
		$stmt = $this->select($sql, $bindParams);
		if(!$stmt){
			$this->last_error = "Invalid resource identifier passed to select_many() function. ";
			$this->print_last_error();
			return false;
		}
		$rows = $stmt->fetchAll();
		return $rows;
	}


/**
 * check_table_exist method checks if table with name $tbl exists
 * tested only for mysql database
 * @param tbl - table name
 * @returns true/false 
 */
	public function check_table_exist($tbl){
		$sql = "SHOW TABLES LIKE '".$tbl."'";
		$r = $this->db_link->query($sql);
		if( $r->rowCount() > 0){
			return true;
		} 
		return false;
	}


/**
 * create_table method
 * creates table according incoming sql statement
 * 
 * @param sql - sql statement
 * 
 * returns result of PDO exec method 
 */
	public function create_tbl($sql){ 
		$this->last_query = $sql;

		// if zero returned it means 0 rows affected
		$r = $this->db_link->exec($sql); //PDO::exec() returns the number of rows that were modified or deleted 
		if($r == 0){
			return true;
		}
		return false;
	}


/**
 * insert_sql inserts row/rows
 * 
 * @param sql - sql statement
 * @param bindParameters parameters to be binded to placeholders in sql statement. Not required
 * 
 * @returns id of last inserted row
 */
	public function insert_sql($sql, $bindParams=[]){		
		ksort($bindParams);
		$stmt = $this->db_link->prepare($sql);
		foreach($bindParams as $key => $value){
			$stmt->bindValue("$key", $value);
		}

		$stmt->execute();
		$this->last_query = $stmt->queryString;
		if( $stmt->errorCode() == 0 ) {
			return $this->db_link->lastInsertId();
		}else{
			$this->last_query = $stmt->queryString;
			$this->last_error = "insert_array function failed";
			$this->print_last_error();
			return false;
		} 
	}


/**
 * update_sql updates row/rows
 * 
 * @param sql - sql statement
 * @param bindParameters parameters to be binded to placeholders in sql statement. Not required
 * 
 * @returns number of affected rows
 */
	public function update_sql($sql, $bindParams=[]){
		ksort($bindParams);

		$stmt = $this->db_link->prepare($sql);
		foreach($bindParams as $key => $value){
			$stmt->bindValue(":$key", $value);
		}

		$stmt->execute();
		$this->last_query = $stmt->queryString;
		if( $stmt->errorCode() == 0 ) {
			return $stmt->rowCount();
		}else{
			$this->last_query = $stmt->queryString;
			$this->last_error = "insert_array function failed";
			$this->print_last_error();
			return false;
		}
	}


/** 
 * insert_array method insert array into table 
 * 
 * @param table - table name
 * @param data - associative array in colname=>data format
 * 
 * @returns id of last inserted row or false
 */
	/*
	public function insert_array($table, $data) {
		foreach($data as $val) {
			$tmp_arr[] = "?";
			$vals[] = $val;
		}
		$sql = "insert into $table (".implode(",", array_keys($data)).") values (".implode(",", $tmp_arr).")";
		error_log($sql);
		$stmt = $this->db_link->prepare($sql);
		if($stmt->execute(array_values($data))){
			return $this->db_link->lastInsertId();
		}
		return false;
	}
	*/
	
	public function insert_array($table, $data){

		ksort($data);

		$fieldNames = implode(',', array_keys($data));
		$fieldValues = ':'.implode(', :', array_keys($data));

		$stmt = $this->db_link->prepare("INSERT INTO $table ($fieldNames) VALUES ($fieldValues)");

		foreach($data as $key => $value){
			$stmt->bindValue(":$key", $value);
		}

		$stmt->execute();
		if( $stmt->errorCode() == 0 ) {
			return $this->db_link->lastInsertId();
		}else{
			$this->last_query = $stmt->queryString;
			$this->last_error = "insert_array function failed";
			$this->print_last_error();
			return false;
		} 

	}


/**
 * update_array method update array into table 
 * 
 * @param table - table name
 * @param data - associative array in colname=>data format
 * @where where condition TODO variables should be substituted
 * 
 * @returns number of affected rows
 */	
	public function update_array($table, $data, $where){ 
		ksort($data);

		$fieldDetails = NULL;
		foreach($data as $key => $value){
			$fieldDetails .= "$key = :$key,";
		}
		$fieldDetails = rtrim($fieldDetails, ',');
		
		$stmt = $this->db_link->prepare("UPDATE $table SET $fieldDetails WHERE $where");

		foreach($data as $key => $value){
			$stmt->bindValue(":$key", $value);
		}
		
		$stmt->execute(); 
		if( $stmt->errorCode() == 0 ) {
			return $stmt->rowCount();
		}else{
			$this->last_query = $stmt->queryString;
			$this->last_error = "update_array function failed";
			$this->print_last_error();
			return false;
		} 
	}
	
	
/**
 * delete method
 * performs required delete commands
 * 
 * @param obligatory statement with substitutes instead of variables.  
 * @returns true/false
 */
	function delete($sql){
		$stmt = $this->db_link->prepare($sql);
		$stmt->execute();
		if( $stmt->errorCode() == 0 ){
			return true;
		}else{
			$this->last_query = $stmt->queryString;
			$this->last_error = "delete function failed";
			$this->print_last_error();
			return false;
		}
	}


	public function get_fields_for_table($table){
		$rs = $this->db_link->query("SELECT * FROM $table LIMIT 0");
		if($rs){
			$columns = [];
			for ($i = 0; $i < $rs->columnCount(); $i++){
				$col = $rs->getColumnMeta($i);
				$col['Field'] = $col['name']; //application code expects index 'Field' = workaround
				$columns[] = $col;
			}
			return $columns;
		}
		return false;
	}
	
	
function print_last_error($show_query=true, $msg = ""){
	error_log("db.class error: ".$this->last_error." (query: ".$this->last_query.")");
}


function print_last_query(){
	error_log("db.class query: ".$this->last_query);
}
	
}
	
?>