PHP Database Class and it's use - Part 2

This is how you can use it for select.

 <?php  
   include_once('classes/class.database.php');  
   $db = new MySQLiDB;  
   $args = [  
     'table' => 'tblcity',  
     'data' => [  
       'idCity','lbCityName'  
     ],  
        'order' => 'lbCityName', // order by  
        'oType' => '' // ASC or DESC  
     ];  
   $fetch = $db->select($args);  
   $res;  
   ?>  

loading a dropdown list

 <select required="" id="pickup" name="pickup" class="form-control" >  
   <option>Please Select</option>  
   <?php   
    foreach ($fetch as $city)  
    {                                               
         ?>  
   <option value='<?php echo $city['idCity'];?>'> <?php echo htmlentities(utf8_encode($city['lbCityName'])); ?></option>  
   <?php  
    }  
    ?>     
 </select>  

I will discuss the insert on next post.

PHP Database Class and it's use - Part 1

Writing PHP is considerably harder than ASP.Net or Microsoft .Net.  One reason I kind of hate it is that I am a lazy programmer. So here is what I use for a PHP DEV at the moment. A Database class that make it easy to deal with the DB.

This is a copied class. The author is there in the class file as a comment. I will discuss how to use it.

 <?php  
 /**  
  * MySQLi Database Class  
  * @category Database Access  
  * @package  Database  
  * @author  AashikP  
  * @copyright Copyright (c) 2018 AashikP  
  * @license  https://opensource.org/licenses/MIT The MIT License  
  * @version  0.1  
  */  
 class MySQLiDB  
 {  
   // Mysqli instance.  
   private $mySqli;  
   // Save Prefix if defined.  
   private $prefix = '';  
   // Generate an array from given $data values for bindParam  
   private $bind_arr = array(''); // Create the empty 0 index  
   // Set type to use in bindPar function  
   private $type;  
   // Set table with prefix if exists  
   private $table;  
   // array to generate bind_results  
   private $result_arr = array('');  
   // array to catch multiple rows of results  
   private $multi_result_arr = array();  
   // array to fetch values  
   private $fetch = array();  
   public function __construct()  
   {  
     // Create a connection  
     $this->connect();  
     // Check if a database prefix is defined. If defined, set prefix value  
     defined('DB_PREFIX') ? $this->setPrefix(DB_PREFIX) : null;  
   }  
   // Connect using a mysqli instance  
   private function connect()  
   {  
     $this->mySqli = new \mysqli('localhost', 'root', '', 'prasanna2019april');  
     // Is there an issue connecting to the database?  
     if ($this->mySqli->connect_errno) {  
       echo '<br/>', 'Error: Unable to connect to Database.' , '<br>';  
       echo "Debugging errno: " . $this->mySqli->connect_errno , '<br>';  
       echo "Debugging error: " . $this->mySqli->connect_error , '<br>';  
       unset($this->mySqli);  
       exit;  
     }  
   }  
   // Set prefix for the table name if there's a prefix setup in the config file  
   private function setPrefix($value = '')  
   {  
     $this->prefix = $value;  
   }  
   // Function to insert data into table  
   public function insert($args)  
   {  
     // set type  
     $this->type = 'insert';  
     // set table and configure prefix, if available  
     $this->setTable($args['table']);  
     // generate insert query  
     $query = $this->genQuery($args);  
     // prepare query statement  
     $stmt = $this->mySqli->prepare($query);  
     if ($this->mySqli->errno) {  
       die('Unable to insert data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);  
     }  
     // generate the bind_arr to be used to bind_param  
     $this->bindPar($args);  
     // bind parameters for statement execution  
     call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));  
     // execute the statement (return error if execution failed)  
     if (!$stmt->execute()) {  
       die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);  
     }  
     // close statement  
     $stmt->close();  
     $this->reset();  
   }  
   // Function to update data  
   public function update($args)  
   {  
     // set type for use in query generator  
     $this->type = 'update';  
     // set table and configure prefix, if available  
     $this->setTable($args['table']);  
     // generate update query  
     $query = $this->genQuery($args);  
     // prepare query statement  
     $stmt = $this->mySqli->prepare($query);  
     if ($this->mySqli->errno) {  
       die('Unable to insert data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);  
     }  
     // generate the bind_arr to be used to bind_param  
     $this->bindPar($args);  
     // bind parameters for statement execution  
     call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));  
     // execute the statement (return error if execution failed)  
     if (!$stmt->execute()) {  
       die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);  
     }  
     // close statement  
     $stmt->close();  
     $this->reset();  
   }  
   // Function to select data from the table  
   public function select($args)  
   {  
     // set type for use in query generator  
     $this->type = 'select';  
     // set table and configure prefix, if available  
     $this->setTable($args['table']);  
     // generate select query  
     $query = $this->genQuery($args);  
     // prepare query statement  
     $stmt = $this->mySqli->prepare($query);  
     if ($this->mySqli->errno) {  
       die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);  
     }  
     // generate the bind_arr to be used to bind_param  
     $this->bindPar($args);  
     // bind parameters for statement execution if bind_arr is not empty  
     // bind_arr will be empty if you're trying to retrieve all the values in a row  
     if (!empty($this->bind_arr)) {  
       call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));  
     }  
     // execute the statement (return error if execution failed)  
     if (!$stmt->execute()) {  
       die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);  
     }  
     // if you've manually defined the data that you need to retrieve, generate result set  
     if (is_array($args['data'])) {  
       // generate the result set as an array to be  
       $this->genResultArr($args);  
       call_user_func_array(array($stmt, 'bind_result'), $this->returnRef($this->result_arr));  
       if ($this->mySqli->errno) {  
         die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);  
       }  
       $this->fetch = array(); // making sure the array is empty  
       $i=0;  
       while ($stmt->fetch()) {  
         $this->multi_result_arr = array_combine($args['data'], $this->result_arr);  
         // Get the values and append it to fetch array $i denotes the row number  
         foreach ($this->multi_result_arr as $arr => $val) {  
           $this->fetch[$i][$arr] = $val;  
         }  
         $i++;  
       }  
       // if there's just one row of results retrieved, just reset the array  
       // so that you can directly call the value by $fetch['column_name']  
       if (count($this->fetch) == 1) {  
         $this->fetch = $this->fetch[0];  
       }  
     } elseif ($args['data'] == '*') {  
       // Generate a result metadata variable to be used to fetch column names in the array  
       $res = $stmt->result_metadata();  
       // Copy the column tables as an array into the fields variable to generate bind_result later  
       $fields = $res->fetch_fields();  
       // Field count for iteration  
       $count = $res->field_count;  
       // row count to chose type of array (multidimensional if more than one row found)  
       $row = $res->num_rows;  
       for ($i = 0; $i < $count; $i++) {  
         $this->multi_result_arr[$i] = $this->result_arr[$i] = $fields[$i]->name;  
       }  
       call_user_func_array(array($stmt, 'bind_result'), $this->returnRef($this->result_arr));  
       if ($this->mySqli->errno) {  
         die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);  
       }  
       $this->fetch = array(); // making sure the array is empty  
       $i=0;  
       // create a fetch array that combines the required db column names with the retrieved results  
       while ($stmt->fetch()) {  
         $this->fetch[$i] = array_combine($this->multi_result_arr, $this->result_arr);  
         $i++;  
       }  
       // if there's just one row of results retrieved, just reset the array  
       // so that you can directly call the value by $fetch['column_name']  
       if (count($this->fetch) == 1) {  
         $this->fetch = $this->fetch[0];  
       }  
     }  
     $stmt->close();  
     // reset values for next query  
     $this->reset();  
     return $this->fetch;  
   }  
   // Function to delete values from a Database  
   public function delete($args)  
   {  
     // delete function must not be used to truncate tables  
     if (!isset($args['where'])) {  
       echo 'If you really want to delete all the contents, use truncate() method.';  
       return;  
     } elseif (isset($args['data'])) { // if you're just deleting fields, use update statement instead  
       echo 'If you want to delete certain column in a row, use the update statement instead';  
     }  
     // set type for use in query generator  
     $this->type = 'delete';  
     // set table and configure prefix, if available  
     $this->setTable($args['table']);  
     // generate delete query  
     $query = $this->genQuery($args);  
     // prepare query statement  
     $stmt = $this->mySqli->prepare($query);  
     if ($this->mySqli->errno) {  
       die('Unable to delete data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);  
     }  
     // generate the bind_arr to be used to bind_param  
     $this->bindPar($args);  
     // bind parameters for statement execution  
     call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));  
     // execute the statement (return error if execution failed)  
     if (!$stmt->execute()) {  
       die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);  
     }  
     // close statement  
     $stmt->close();  
     $this->reset();  
   }  
   // Deletes all the data and resets the table. Please use with caution  
   public function truncate($table)  
   {  
     // set table and configure prefix, if available  
     $this->setTable($table);  
     // query to truncate the entire table  
     // NOTE: This is irreversible  
     $query = 'TRUNCATE ' . $this->table;  
     // prepare query statement  
     $stmt = $this->mySqli->prepare($query);  
     // execute the statement (return error if execution failed)  
     if (!$stmt->execute()) {  
       die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);  
     }  
     // close statement  
     $stmt->close();  
     $this->reset();  
   }  
   // prefix table name if db prefix is setup  
   private function setTable($table)  
   {  
     $this->table = $this->prefix . $table;  
   }  
   // Generates the mysqli query statement  
   private function genQuery($args)  
   {  
     switch ($this->type) {  
       case 'insert':  
         $query = "INSERT INTO `" . $this->table .'` ';  
         $query .= $this->genInsert($args['data']);  
         $query .= " VALUES " . $this->genInsval($args['data']);  
         break;  
       case 'select':  
         $query = "SELECT " . $this->genSelect($args) . " FROM " . $this->table;  
         if (isset($args['where'])) {  
           $query .= $this->genWhere($args);  
         }  
         if (isset($args['order'])) {  
           $query .= $this->genOrder($args);  
         }  
         if (isset($args['group'])) {  
           $query .= $this->genGroup($args);  
         }  
         if (isset($args['limit'])) {  
           $query .= " LIMIT " . $args['limit'];  
         }  
         break;  
       case 'update':  
         $query = "UPDATE `" . $this->table . "` SET";  
         $query .= $this->genUpdate($args['data']);  
         if (isset($args['where'])) {  
           $query .= $this->genWhere($args);  
         }  
         break;  
       case 'delete':  
         $query ="DELETE FROM `" . $this->table . '` ';  
         if (isset($args['where'])) {  
           $query .= $this->genWhere($args);  
         }  
         break;  
       default:  
         $query ='';  
         break;  
     }  
     return $query;  
   }  
   // Generate insert query  
   private function genInsert($data)  
   {  
     $ins_query = '( ';  
     foreach ($data as $key => $value) {  
       if ($data[$key] == end($data)) {  
         $ins_query .= ' ' . $key . ' ';  
         continue;  
       }  
       $ins_query .= ' ' . $key . ', ';  
     }  
     $ins_query .= ')';  
     return $ins_query;  
   }  
   // generate the value part of the insert query to be used as a prepared statement  
   // Eg (? , ?, ?)  
   private function genInsVal($data)  
   {  
     $ins_value = '(';  
     foreach ($data as $k => $v) {  
       if ($data[$k] == end($data)) {  
         $ins_value .= '?';  
         continue;  
       }  
       $ins_value .= '?, ';  
     }  
     $ins_value .=')';  
     return $ins_value;  
   }  
   // generate update query  
   private function genUpdate($data)  
   {  
     $update_query = '';  
     foreach ($data as $key => $value) {  
       $update_query .= ' ' .$key .' =?,' ;  
     }  
     $update_query = rtrim($update_query, ',');  
     return $update_query;  
   }  
   // Generate select query  
   private function genSelect($sel_array)  
   {  
     $sel_string = '';  
     if (is_array($sel_array['data'])) {  
       foreach ($sel_array['data'] as $value) {  
         $sel_string .= $value . ', ';  
       }  
       $sel_string = rtrim($sel_string, ', ');  
     } elseif ($sel_array['data'] == '*') {  
       $sel_string = '*';  
     }  
     return $sel_string;  
   }  
   // Generate where condition for query generator (genQuery)  
   private function genWhere($where_arr)  
   {  
     $where_query = ' WHERE';  
     if (isset($where_arr['whereOp'])) {  
       $opr = $where_arr['whereOp'];  
     } else {  
       $opr = '=';  
     }  
     // Check if the given array is associative  
     if ($this->isAssoc($where_arr)) {  
       foreach ($where_arr['where'] as $key => $value) {  
         $where_query .= ' ' . $key . $opr . '? ';  
       }  
     } else {  
       foreach ($where_arr['where'] as $value) {  
         $where_query .= ' ' . $value . $opr . '? ';  
       }  
     }  
     if (isset($where_arr['and']) && !empty($where_arr['and'])) {  
       $where_query .= $this->andWhere($where_arr);  
     }  
     if (isset($where_arr['or']) && !empty($where_arr['or'])) {  
       $where_query .= $this->orWhere($where_arr);  
     }  
     return $where_query;  
   }  
   // Generate and condition for query generator (genQuery)  
   private function andWhere($and_arr)  
   {  
     $and_query = ' AND';  
     if (isset($where_arr['andOP'])) {  
       $opr = $where_arr['andOP'];  
     } else {  
       $opr = '=';  
     }  
     foreach ($and_arr['and'] as $key => $value) {  
         $and_query .= ' ' . $key . $opr . '? ';  
     }  
     return $and_query;  
   }  
   // Generate OR condition for query generator (genQuery)  
   private function orWhere($or_arr)  
   {  
     $or_query = ' OR';  
     if (isset($or_arr['orOP'])) {  
       $opr = $or_arr['orOp'];  
     } else {  
       $opr = '=';  
     }  
     foreach ($or_arr['and'] as $key => $value) {  
         $or_query .= ' ' . $key . $opr . '? ';  
     }  
     return $or_query;  
   }  
   // Generate order by condition  
   private function genOrder($args)  
   {  
     $order_query = ' ORDER BY ' . $args['order'] .' ';  
     if (isset($args['oType']) && (($args['oType'] == 'ASC') || ($args['oType'] == 'DESC'))) {  
       $order_query .= $args['oType'];  
     }  
     return $order_query;  
   }  
   // Generate group by conditions  
   private function genGroup()  
   {  
     $grp_query = ' GROUP BY ' . $args['group'] .' ';  
     if (isset($args['gType']) && (($args['gType'] == 'ASC') || ($args['gType'] == 'DESC'))) {  
       $grp_query .= $args['gType'];  
     }  
     return $grp_query;  
   }  
   // Check the input array and forward it to bindParam for further processing  
   private function bindPar($args)  
   {  
     if (isset($args['data']) && $this->type != 'select') {  
       $this->bindParam($args['data']);  
     }  
     if (isset($args['where'])) {  
       $this->bindParam($args['where']);  
     }  
     if (isset($args['and'])) {  
       $this->bindParam($args['and']);  
     }  
     if (isset($args['or'])) {  
       $this->bindParam($args['or']);  
     }  
     if ($this->type == 'select' && !isset($args['where']) && !isset($args['and']) && !isset($args['or'])) {  
       unset($this->bind_arr);  
     }  
   }  
   // Organize generation of bind_arr in the below method based on $data  
   private function bindParam($data)  
   {  
     if (is_array($data)) {  
       if ($this->isAssoc($data)) {  
         foreach ($data as $key => $value) {  
           $this->bindValues($value);  
         }  
       } else {  
         foreach ($data as $value) {  
           $this->bindValues($value);  
         }  
       }  
     } else {  
       $this->bindValues($data);  
     }  
   }  
   // Detect type and push values inside the bind_arr to be submitted as bind parameters  
   private function bindValues($value)  
   {  
     $this->bind_arr[0] .= $this->detectType($value);  
     array_push($this->bind_arr, $value);  
   }  
   // Detect value type to generate bind parameter  
   protected function detectType($value)  
   {  
     switch (gettype($value)) {  
       case 'string':  
         return 's';  
         break;  
       case 'integer':  
         return 'i';  
         break;  
       case 'blob':  
         return 'b';  
         break;  
       case 'double':  
         return 'd';  
         break;  
     }  
     return '';  
   }  
   protected function returnRef(array &$arr)  
   {  
     //Referenced data array is required by mysqli since PHP 5.3+  
     if (strnatcmp(phpversion(), '5.3') >= 0) {  
       $refs = array();  
       foreach ($arr as $key => $value) {  
         $refs[$key] = & $arr[$key];  
       }  
       return $refs;  
     }  
     return $arr;  
   }  
   // Generate a result array with selected values from database for given data  
   private function genResultArr($args)  
   {  
     $this->result_arr = array();  
     foreach ($args['data'] as $value) {  
       array_push($this->result_arr, $value);  
     }  
   }  
   // Check if an array is associative  
   private function isAssoc(array $array)  
   {  
     $keys = array_keys($array);  
     return array_keys($keys) !== $keys;  
   }  
   // Reset to default values after an operation  
   private function reset()  
   {  
     $this->type = null;  
     $this->table = '';  
     $this->bind_arr = array('');  
     $this->result_arr = array();  
     $this->multi_result_arr = array();  
   }  
   // Disconnects the active connection  
   private function disconnect()  
   {  
     if (isset($this->mySqli)) {  
       $this->mySqli->close();  
       unset($this->mySqli);  
     }  
   }  
   // Making sure we don't have open connections  
   public function __destruct()  
   {  
     if (isset($this->mySqli)) {  
       // if there's an active connection, close it  
       if ($this->mySqli->ping()) {  
         $this->disconnect();  
       }  
     }  
   }  
 }  

The next post is about how it use it.

Get selected value of a dropdown without ID

As the headline is short, the work is simple. Look at the following code

$('select').on('change', function () {         
            var ttMailActorTo = $('option:selected', this).attr('ttMailActorTo');
            if (isEmail(ttMailActorTo))
                $('#ttMailActorTo').val(ttMailActorTo);
});

It binds the change function to all select on the page and get the special attribute value "ttMailActorTo" and append it to a hidden field "ttMailActorTo".

Simple as that. Enjoy.

IRAJ - නොනැවතී Ft. Peshala

Custom Duration Reminder in C#

It's a common issue that most software developers sit for long time and forget to move around. So here is a timer created for reminding them with a ugly sound clip. :)
The existing features are;

1. Time is configurable
2. Minimize to System Tray

Download the Setup file here.
https://www.dropbox.com/s/4ew1y6w56yemkhn/TRDReminder.zip?dl=0

SQL Function to Split String into a List

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).

A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.

This explanation is copied from stackoverflow.com to understand the differences of using SQL Functions.

So let's get to the headline. Yes we sometimes need to pass a list of values and get them split from a given delimiter. Especially we can use such function to join with other queries to do the job easier and faster. Look at the following.

To do so, first we maintain a sequence table that has sequential numbers starting from 1.

 CREATE TABLE tblSequence (  
      [id] [int] NOT NULL  
 ) ON [PRIMARY]  
 GO  

And now let's add some sequential numbers to it. For the moment we assume the max would be 8000.

 Declare @i int  
 Select @i = 1  
 While @i <= 8000  
 Begin  
      insert into tblSequence(id) values (@i)  
      if @@error <> 0 Break  
      Select @i = @i + 1  
 End  
 go  

It's time to use this table and create our function.

 CREATE FUNCTION tblListSplit(@ListId varchar(8000),@d char(1))  
 RETURNS TABLE  
 AS  
 RETURN (  
      SELECT  
           row_number() OVER(ORDER BY id) id,  
           NullIf(rtrim(ltrim(SubString(@d + @ListId + @d, id, CharIndex(@d, @d + @ListId + @d, id) - id))), '') Val  
      FROM tblSequence (NOLOCK)  
      WHERE id <= Len(@d + @ListId + @d)  
      AND     SubString(@d + @ListId + @d, id - 1, 1) = @d  
      AND     CharIndex(@d, @d + @ListId + @d, id) - id > 0  
 )  
 GO  

Now the function is ready to serve us as needed. Let's try it.

 select * from tblListSplit('1,2,3,4,5,6,7,8,9,10',',')  

And the result will be like;

So, you can see the list is split according to the delimiter we pass and here we used a comma. Try it with all your needs in your procedures.
Happy Coding..!!!

Device based Software Licensing in C#

There are many ways that a software can be protected and mostly by introducing a licensing key. It could probably be the oldest way. So let's make it work on our application too.

This time we generate a key based on PC configuration. As the matching key we can use our own encryption to send back the valid key.

It's simple, look at below.

 static void Main(string[] args)  
     {  
       StringBuilder sbKey =new StringBuilder();  
       ManagementClass mc = new ManagementClass("win32_processor");  
       ManagementObjectCollection moc = mc.GetInstances();  
       foreach (ManagementObject mo in moc)  
       {  
         sbKey.Append(mo.Properties["processorID"].Value.ToString());  
       }  
       Environment.GetLogicalDrives().ToList().FirstOrDefault();  
       string hddrive = Environment.GetLogicalDrives().ToList().FirstOrDefault() ?? "";  
       ManagementObject hddkey = new ManagementObject(@"win32_logicaldisk.deviceid=""" + hddrive[0] + @":""");  
       hddkey.Get();  
       sbKey.Append(hddkey["VolumeSerialNumber"].ToString());  
       string keyEncrypted = Encryption.Encrypt(sbKey.ToString());  
       Console.WriteLine("****************** Licensing Key Generator ******************");  
       Console.WriteLine();  
       Console.WriteLine(" Key : " + sbKey.ToString());  
       Console.WriteLine();  
       Console.WriteLine(" Encrypted Key : " + keyEncrypted);  
       Console.ReadLine();  
     }  

The output looks like;

For encryption & decryption, you can refer the older post here.

You can add and remove any values and make it hard to crack. This also can introduce to station based licensing because each license will apply only to one computer.

Happy coding...