<?php
/*_____________________________________________________________________________________
 Author of the Script: 				Shah  Hussain 
 Email: 								shahhussain305@gmail.com
 URL: www.esdn.com.pk
 -----------------------------------------------------------------------------------
 Use of PDO (PHP Data Objects: 
 1- Open your php.ini file and search for:
 ;extension=php_pdo_mysql.dll
 Remove ";" from starting of the line and it will enable the PDO for MySql Database
 Tested on IIS Version: 8.5.9600.16384 Windows 8.1 Professinal, Ubuntu 16.04, 18.04
 2- UnComment 
 //echo($ex->getMessage()); //Developer Machine: Display Exceptions in browser	
 _______________________________________________________________________________________*/

class CRUD
{
	private $db_user;
	private $db_pass;
	private $db_name;
	private $host;
	public $con;

	//To check sql query if needed
	public $sqlToReturn;
	public $tempVar;

	//default constructor to build the connection to db
	public function __construct($user = null, $key = null, $host = null, $db = null)
	{
		$this->db_user = $user;
		$this->db_pass = $key;
		$this->db_name = $db;
		$this->host = $host;
	}

	function __destruct()
	{
		try {
			$this->con = null;
		} catch (PDOException $exc) {

		}
	}
	/**
	 * connect() function to connect to database
	 */
	public function connect()
	{
		try {
			// If already connected, return it
			if ($this->con instanceof PDO) {
				return $this->con;
			}

			// Add error logging for connection attempts
			if (empty($this->host) || empty($this->db_name) || empty($this->db_user)) {
				return null;
			}

			$this->con = new PDO("mysql:host={$this->host};dbname={$this->db_name};charset=utf8", $this->db_user, $this->db_pass);
			$this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			return $this->con;
		} catch (PDOException $ex) {
			// Log the full connection error for debugging
			error_log('Database connection failed: ' . $ex->getMessage() . ' | Host: ' . $this->host . ' | DB: ' . $this->db_name . ' | User: ' . $this->db_user);
			return null;
		}
	}

	/**
	 * Get the connection, ensuring it is active
	 */
	public function getCon()
	{
		return $this->connect();
	}
	/**	___________________________________________________________________________________________
	 getRecordSet() function takes simple plain sql query or parameterized quwery 
	 //simple query = select * from table
	 //param query = select * from table where sno = :sno and col_2 = :col_2 AND name LIKE :name
	 //WHERE name LIKE :name should be $name = '%text' or '%text%'
	 ___________________________________________________________________________________________
	 Code Snippet: 
	 $sno = 1;
	 $user_name = '%hussain%';
	 $aray = array(':sno'=>$sno,':user_name'=>$user_name);
	 foreach($crud->getRecordSet("SELECT * FROM users WHERE sno = :sno AND user_name LIKE :user_name",$aray) as $row){
	 echo('<br>'.$row['user_name']);
	 echo('<br>'.$row['user_password']);
	 echo('<br>'.$row['date_reg']);
	 }
	 ____________________________________________________________________________________________ */

	public function getRecordSet($sql, $bindVars = array())
	{
		try {
			if (!$this->getCon()) {
				return [];
			}
			$statement = $this->con->prepare($sql);
			$statement->execute($bindVars);
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		} catch (PDOException $ex) {
			error_log('getRecordSet Error: ' . $ex->getMessage());
			return [];
		}
	}
	/**
	 * function to return the filled array/ResultSet according to the provided query, otherwise returns null array
	 * length of the array will be less than 1 
	 * Usage: 
	 * $rs = $db->getRecordSetFilled($sql,$bindVars=array());
	 * if($rs > 0){
	 *      foreach($rs as $r){
	 *          .......
	 *      }
	 * }
	 * else{
	 *      echo($method->errorMsg("","Sorry there was no record found"));
	 * }
	 */
	public function getRecordSetFilled($sql, $bindVars = array())
	{
		try {
			if (!$this->getCon()) {
				return [];
			}

			$statement = $this->con->prepare($sql);
			$statement->execute($bindVars);
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		} catch (PDOException $ex) {
			error_log('getRecordSetFilled Error: ' . $ex->getMessage());
			return [];
		}
	}

	/**
	 * Get a single record from the database
	 * 
	 * @param string $sql
	 * @param array $bindVars
	 * @return array|null
	 */
	public function getRecordFilled($sql, $bindVars = array())
	{
		try {
			if (!$this->getCon()) {
				return null;
			}

			$statement = $this->con->prepare($sql);
			$statement->execute($bindVars);
			$result = $statement->fetch(PDO::FETCH_ASSOC);
			return $result ?: null;
		} catch (PDOException $ex) {
			error_log('getRecordFilled Error: ' . $ex->getMessage());
			return null;
		}
	}

	public function getValue($sql, $bindVars = array())
	{
		try {
			if (!$this->getCon()) {
				return null;
			}
			$statement = $this->con->prepare($sql);
			$statement->execute($bindVars);
			return $statement->fetchColumn();
		} catch (PDOException $ex) {
			error_log('getValue Error: ' . $ex->getMessage());
			return null;
		}
	}

	/**_____________________________________________________________________________________________
	 dbQuery() function takes two parameters 
	 1-	$sql : A proper Sql Query that could be perameterized or non-perameterized
	 2-	$bindVars : An array() holds bindable values for where clause 
	 _________________________________________________________________________________________
	 Code Snippet: 
	 $ary = array(':user_name'=>'shahhussain');
	 $sql = "SELECT * FROM users WHERE user_name = :user_name";
	 //echo($crud->search($sql,$ary));   // will show 1 or 0
	 if($crud->dbQuery($sql,$ary)){
	 echo('Result Found');
	 }
	 else{
	 echo('Result was not found');
	 }
	 OR
	 $sql = "SELECT * FROM users";
	 //echo($crud->dbQuery($sql));   // will show 1 or 0
	 if($crud->search($sql)){
	 echo('Result Found');
	 }
	 else{
	 echo('Result was not found');
	 }
	 Note: This method will run INSERT , UPDATE , DELETE and Search Queries.
	 ___________________________________________________________________________________________*/

	public function dbQuery($sql, $bindVars = array())
	{
		try {
			if (!$this->getCon()) {
				return false;
			}
			$statement = $this->con->prepare($sql);
			return $statement->execute($bindVars);
		} catch (PDOException $exc) {
			error_log('dbQuery Error: ' . $exc->getMessage());
			$this->tempVar = $exc->getMessage();
			return false;
		}
	}

	public function getLastInsertId()
	{
		return $this->con->lastInsertId();
	}



	/** 
	 * ______________________________________________________________________________________________________
	 fillCombo() function takes 4 @params 
	 1- $sql : proper sql query 
	 2- $valueMember : column name i.e. "sno" (<option value='$valueMember'> $valueMember </option>)
	 3- $displayMember : column name i.e. "Title" (<option value='$valueMember'> $displayMember </option>)
	 4- $bindVars : array() with filled values if WHERE clause has used in query [Optional]
	 _____________________________________________________________________________________________________
	 Code Snippet:
	 Simple use:
	 <select name="combo" id="combo">
	 <php 
	 echo($crud->fillCombo("SELECT sno,studentName FROM students","sno","studentName"));
	 /php>
	 </select>
	 Complex Query use: 
	 <select name="combo" id="combo">
	 <php 
	 $ary = array(":sno"=>12,":classId"=>'10th');
	 echo($crud->fillCombo("SELECT sno,studentName FROM students WHERE sno = :sno AND classId = :classId","sno","studentName",$ary));
	 /php>
	 </select>
	 ____________________________________________________________________________________________________*/

	public function fillCombo($sql, $valueMember, $displayMember, $bindVars = array(), $showEmptyFld = '1')
	{
		try {
			$this->connect();
			$options = "";
			$statement = $this->con->prepare($sql);
			$statement->execute($bindVars);
			if ($statement->rowCount() > 0) {
				if ($showEmptyFld == 1) {
					$options .= '<option value=""></option>';
				}
				foreach ($statement as $row) {
					$options .= '<option value="' . $row[$valueMember] . '"> ' . $row[$displayMember] . ' </option>' . PHP_EOL;
				}
			}
			return $options;
			$this->con = null;
		} catch (PDOException $exc) {
			return $exc->getMessage(); //Developer Machine: Display Exceptions in browser
		}
	}


	/**
	 * ______________________________________________________________________________________________________
	 dateDif($futureDate,$pastDate) function takes two @params as 
	 1- $futureDate And $pastDate: coming next full date date('2014-01-02','2014-01-01') 
	 Result: "1" it is 1 day
	 This function return number of days between past and future date 
	 _____________________________________________________________________________________________________
	 Code Snippet:
	 Difference between two date are in days: <php echo(dateDif('2014-01-02','2014-01-01')); /php>
	 Output: 1 (day)
	 __________________________________________________________________________________________________*/

	public function dateDif($futureDate, $pastDate)
	{
		try {
			$this->connect();
			$sql = "SELECT DATEDIFF('" . $futureDate . "','" . $pastDate . "') AS diffirence";
			$statement = $this->con->prepare($sql);
			$statement->execute();
			return $statement->fetchColumn();
			$this->con = null;
		} catch (PDOException $exc) {
			return $exc->getMessage();
		}
	}

	/**
	 * 	getNumChars($txt,$numChars,$side='L') function takes three @params as 
	 1- $txt: sample text
	 2- $numChars : need to returns number of character from text
	 3- $side : 
	 1- 'L' = Read from left to right (Default @Param is from left to right)
	 2- 'R' = Read from right to left
	 _____________________________________________________________________________________________________
	 Code Snippet:
	 Read From Left to Right 10 chars: <php echo(getNumChars('This is my sample text in first param',10,'L')); /php>
	 Output: "This is my" (including spaces)
	 Read From Right to Left 10 chars: <php echo(getNumChars('This is my sample text in first param',10,'R')); /php>
	 Output: "irst param" (including spaces)
	 __________________________________________________________________________________________________*/

	public function getNumChars($txt, $numChars, $side = 'L')
	{
		try {
			$this->connect();
			$sql = "SELECT ";
			if ($side == 'L') {
				$sql .= " LEFT";
			} else {
				$sql .= " RIGHT";
			}
			$sql .= "('" . $txt . "'," . $numChars . ") AS txt";
			$statement = $this->con->prepare($sql);
			$statement->execute();
			return $statement->fetchColumn();
			$this->con = null;
		} catch (PDOException $exc) {
			return $exc->getMessage();
		}
	}

	/**
	 * get number of words from table column
	 */
	function numWords($table_name, $col_name, $num_words, $isWhere = '0', $sno = '')
	{
		try {
			$this->connect();
			$bindVarSno = array();
			$sql = "SELECT SUBSTRING_INDEX(" . $col_name . ",' '," . $num_words . ") as words FROM " . $table_name;
			if ($isWhere == '1' && $sno != '') {
				$bindVarSno = array(':sno' => $sno);
				$sql .= " WHERE sno = :sno";
			}
			return $this->getValue($sql, $bindVarSno);
			$this->con = null;
		} catch (PDOException $exc) {
			return $exc->getMessage();
		}
	}

	/**
	 * this function will initiate beginTransaction() and will commit after finishing querying 
	 the tables successfully, while it will not save / commit any changes made via any query if there
	 is any error occur in any query, means rollback() will occur in catch() block.
	 Here is one defeciancy in the dbTransaction() function, that is, it do not allow you to use this function in 
	 a scenario where you need to get last_inserted_id() etc or need anything else from the previous record.
	 */
	public function dbTransaction($sqlsArray = array())
	{
		$flag = false;
		try {
			if (isset($sqlsArray) && !empty($sqlsArray)) {
				$this->connect();
				$this->con->beginTransaction();
				foreach ($sqlsArray as $sql => $ary) { //loop over sql Array to get individval sql
					$stmt = $this->con->prepare($sql);
					$stmt->execute($ary);
					//$this->tempVar .= 'Sql = '.$sql.' param = '.$ary.'<hr>';
				}
				$this->con->commit();
				$flag = true;
			}
		} catch (PDOException $exc) {
			$this->con->rollBack();
			$this->tempVar = $exc->getTraceAsString();
			$this->tempVar .= '<hr>' . $exc->getMessage();
			$flag = false;
		}
		$this->con = null;
		return $flag;
	}

	/** ---------Start implicit transactions----------- 
	 * First start the code with : try{}catch(){}
	 * in the:
	 * 		try{ //call the beginTransactions()  
	 * 			$db->beginTransaction();
	 * 			//then call: 
	 * 				$db->dbQueryTrans() 
	 * 					or 
	 * 				$db->getValueTrans() 
	 * 			//then call:
	 * 				$db->commitTransaction()  
	 * 			}catch(PDOException $exc){
	 * 				$db->rollBackTransaction();
	 * 				$this->TempVar = $exc;
	 * 			}catch(Exception $ex){
	 * 				$db->rollBackTransaction();
	 * 				$this->TempVar = $ex;
	 * 			}
	 * 
	 */
	public function beginTransactions()
	{
		if ($this->getCon()) {
			$this->con->beginTransaction();
		}
	}
	public function commitTransaction()
	{
		$this->con->commit();
	}
	public function rollBackTransaction()
	{
		$this->con->rollBack();
	}

	public function getValueTrans($sql, $bindVars = array())
	{
		try {
			$statement = $this->con->prepare($sql);
			$statement->execute($bindVars);
			$this->sqlToReturn = $sql;
			return $statement->fetchColumn();
		} catch (PDOException $ex) {
			$this->tempVar = $ex->getMessage();
		}
	}

	public function dbQueryTrans($sql, $bindVars = array())
	{
		try {
			if (!$this->con) {
				return false;
			}
			$statement = $this->con->prepare($sql);
			$exec = $statement->execute($bindVars);
			$this->sqlToReturn = $sql;
			return $exec;
		} catch (PDOException $exc) {
			$this->tempVar = $exc->getMessage(); //for debugging purpose only
			return false;
		}
	}
	/*---------End Implicit Transactions--------*/


}
?>