php mysql mysqli quries class

Below class file handle mysql and mysqli connection & quries. you can select all records as well as can place where clause also saving records , removing records , executing direct query with Mysql OR MySqli as defined in config.php file.

1. Your config.php file will be as below , you can change it and placed values accordingly as your mysql database. resource_type Mysql OR MySqli

$dbconfig['db_server'] = 'localhost';
$dbconfig['db_port'] = ':3306';
$dbconfig['db_username'] = 'root';
$dbconfig['db_password'] = '';
$dbconfig['db_name'] = 'test';
$resource_type='MySqli';

2. Below is MysqlDatabaseMysqli.php class which has functions , you can execute Queries with MySql or MySqli.

require_once 'config.php';
Class MysqlDatabaseMysqli {
    protected $connection = NULL;
    protected $connection_string = NULL;
    protected $associate_type = NULL;
    function __construct() {
        global $resource_type;
        $this->resource_type_string = strtolower($resource_type);
        switch ($resource_type) {
            case 'MySql':
                $this->associate_type = MYSQL_ASSOC;
                $this->msl_connect();
                break;
            case 'MySqli':
                $this->associate_type = MYSQLI_ASSOC;
                $this->msli_connect();
                break;
            default:
                echo "No resource type define";
                exit;
                break;
        }
    }
    /*
     *  msl->mysql
     */
    function msl_connect() {
        global $dbconfig;
        try {
            if ($link_identifier = @mysql_connect($dbconfig['db_server'], 
                $dbconfig['db_username'], $dbconfig['db_password'])) {
                $select_db = @mysql_select_db($dbconfig['db_name'], $link_identifier);
                if (!$select_db) {
                    throw new Exception(mysql_errno() . ' ' . mysql_error());
                }
                $this->connection = $link_identifier;
            } else {
                throw new Exception(mysql_errno() . ' ' . mysql_error());
            }
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }
    /*
     *  msli->mysqli
     */
    function msli_connect() {
        global $dbconfig;
        try {
            if ($link_identifier = @mysqli_connect($dbconfig['db_server']
                , $dbconfig['db_username'], $dbconfig['db_password'])) {
                $select_db = @mysqli_select_db($link_identifier, $dbconfig['db_name']);
                if (!$select_db) {
                    throw new Exception(mysqli_connect_errno() . ' Unable to select db');
                }
                $this->connection = $link_identifier;
            } else {
                throw new Exception(mysqli_connect_errno() . ' Unable to connect');
            }
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }
    /*
     *  Created By: Boy108zon
     *  @table_name: string
     *  @where: Array
     */
    function get_records($table_name, $where=NULL) {
        $connect = $this->resource_type_string . '_query';
        $num_rows = $this->resource_type_string . '_num_rows';
        $connect_close = $this->resource_type_string . '_close';
        /* if where included */
        $qwhere = '';
        if (is_array($where)) {
            $counter = 0;
            foreach ($where as $key => $value) {
                if ($counter > 0) {
                    $qwhere .= ' AND ';
                }
                $qwhere .= "$key = '$value'";
                $counter++;
            }
            $qwhere = 'WHERE ' . $qwhere;
        }
        $query = "SELECT * from $table_name $qwhere";
        if ($this->resource_type_string == 'mysqli') {
            $result_resource = $connect($this->connection, $query);
        } else {
            $result_resource = $connect($query, $this->connection);
        }
        $noOfrows = $num_rows($result_resource);
        if ($noOfrows > 0) {
            return $this->db_to_array($result_resource);
        } else {
            return NULL;
        }
    }
    /*
     *  Created By: Boy108zon
     *  @table_name: string
     *  @where: Array
     */
    function remove_records($table_name, $where=NULL) {
        $connect = $this->resource_type_string . '_query';
        $affected_rows = $this->resource_type_string . '_affected_rows';
        $connect_close = $this->resource_type_string . '_close';
        /* if where included */
        $qwhere = '';
        if (is_array($where)) {
            $counter = 0;
            foreach ($where as $key => $value) {
                if ($counter > 0) {
                    $qwhere .= ' AND ';
                }
                $qwhere .= "$key = '$value'";
                $counter++;
            }
            $qwhere = 'WHERE ' . $qwhere;
        }
        $query = "DELETE from $table_name $qwhere";
        if ($this->resource_type_string == 'mysqli') {
            $result_resource = $connect($this->connection, $query);
        } else {
            $result_resource = $connect($query, $this->connection);
        }
        $idds = $affected_rows($this->connection);
        //close connection
        @$connect_close($this->connection);
        if ($idds > 0) {
            return $idds;
        } else {
            return NULL;
        }
    }
    /*
     *  Created By: Boy108zon
     *  @result->mysql OR mysqli
     */
    function db_to_array($result) {
        $connection_string = $this->resource_type_string . '_fetch_array';
        $rows = array();
        while ($row = $connection_string($result, $this->associate_type)) {
            $rows[] = $row;
        }
        //close connection
        @$connect_close($this->connection);
        return $rows;
    }
    /*
     *  Created By: Boy108zon
     *  @table_name: String
     *  @post_array:Array
     *  @where:Array
     *  @mode:'save' OR edit
     *  Based on mode return type affected rows and latest insert id
     */
    function save_records($table_name, $post_array, $where=NULL, $mode='save') {
        $connect = $this->resource_type_string . '_query';
        $insert_id = $this->resource_type_string . '_insert_id';
        $affected_rows = $this->resource_type_string . '_affected_rows';
        $connect_close = $this->resource_type_string . '_close';
        $count = 0;
        $fields = '';
        foreach ($post_array as $col => $val) {
            if ($count > 0) {
                $fields .= ', ';
            }
            $fields .= "$col = '$val'";
            $count++;
        }
        $qwhere = '';
        if (is_array($where)) {
            $counter = 0;
            foreach ($where as $key => $value) {
                if ($counter > 0) {
                    $qwhere .= ' AND ';
                }
                $qwhere .= "$key = '$value'";
                $counter++;
            }
            $qwhere = 'WHERE ' . $qwhere;
        }
        if ($mode == 'edit') {
            $query = "UPDATE " . $table_name . " SET $fields $qwhere;";
        } else {
            $query = "INSERT INTO " . $table_name . " SET $fields;";
        }
        if ($this->resource_type_string == 'mysqli') {
            $result_resource = $connect($this->connection, $query);
        } else {
            $result_resource = $connect($query, $this->connection);
        }
        if ($mode == 'edit') {
            $idds = $affected_rows($this->connection);
        } else {
            $idds = $insert_id($this->connection);
        }
        //close connection
        @$connect_close($this->connection); 
        if ($idds > 0) {
            return $idds;
        } else {
            return NULL;
        }
    }
    /*
     *  Created By: Boy108zon
     *  @query: full query including inner , where
     *  if required we can also use it.
     */
    function get_direct_query_records($query) {
        $connect = $this->resource_type_string . '_query';
        $num_rows = $this->resource_type_string . '_num_rows';
        $connect_close = $this->resource_type_string . '_close';
        if ($this->resource_type_string == 'mysqli') {
            $result_resource = $connect($this->connection, $query);
        } else {
            $result_resource = $connect($query, $this->connection);
        }
        $noOfrows = $num_rows($result_resource);
        //close connection
        @$connect_close($this->connection); 
        if ($noOfrows > 0) {
            return $this->db_to_array($result_resource);
        } else {
            return NULL;
        }
    }
}

3. You can use it like below

require_once 'MysqlDatabaseMysqli.php'; 
$dbfun=new MysqlDatabaseMysqli(); 
 
$result=$dbfun->get_records('employee',array('name'=>'amit'));
//For Retrive records from employee table. 
$result=$dbfun->get_records('your_table_name');
//edit 
$result=$dbfun->save_records('your_table_name',$post_array,$where,$mode='Edit');
//save 
$result=$dbfun->save_records('your_table_name',$post_array);
//remove 
 $result=$dbfun->remove_records('your_table_name',array('id'=>6));
// direct query 
$result=$dbfun->get_direct_query_records($query='select * from employee');

4. You can download class from below

https://github.com/boy108zon/SingelMysqlMysqli

Custom save function vTiger module

You may also see read the post here http://yourlearn.in/posts/view/3/custom-save-function-vtiger-module

 

Generally vTiger provides save_module , save function for saving records.But let say you have a deposit module and you want custom function for saving records in vTiger (6.4). basically deposit module has relation with Account module. so in vTiger vtiger_crmentity table maintain every records or operation done and vtiger_crmentity_seq store max id or sequence number , so below are the functions which process save functionality by using custom queries for two modules.

 

function save_module_custom($module, $post_array) {
        return $this->insertIntoDepositTable('vtiger_deposit', $module, $post_array);
    }

function insertIntoDepositTable($table_name, $module, $post_array) {
        global $log, $adb;
        $log->info("in insertIntoDepositTable  " . $table_name . "    module is  " . $module);
        $payment_method = $post_array['payment_method'];
        $account_no = $post_array['account_no'];
        $amount = $post_array['amount'];
        $account_id = $post_array['account_id'];
        $order_id = $this->gen_uuid();
        $currency = 'USD';
        $createdtime = date('Y-m-d H:i:s');
        $assigned_user_id = getRecordOwnerId($post_array['customer_id']);
        $id = $this->getMaxId();
        $deposit_status = 'Pending';
        $comment = '';
        $sql = "INSERT INTO vtiger_deposit(depositid, currency, payment_method, amount, account_id, 
        deposit_status ,order_id,comment) ". "VALUES ($id,'" . $currency . "',
 '" . $payment_method . "','" . $amount . "','" . $account_id . "', 
 '" . $deposit_status . "',
 '" . $order_id . "','" . $comment . "')";
        $adb->pquery($sql, array());
        $sql = "INSERT INTO vtiger_depositcf(depositid) ". "VALUES ($id)";
        $adb->pquery($sql, array());
        $crmid = $this->custom_save_related_module($id, $account_no, $assigned_user_id);
        return array('depositid' => $id, 'crmid' => $crmid);
    }

function custom_save_related_module($relcrmid, $account_no, $assigned_user_id) {
        global $log, $adb;
        $log->info("in custom_save_related_module  vtiger_crmentity,vtiger_crmentity_seq");
        //First get seq no max
        $getCrmSqNo = $adb->pquery("SELECT id FROM vtiger_crmentity_seq", array());
        $norows = $adb->num_rows($res);
        $seq_id = $adb->query_result($getCrmSqNo, 0, 'id');
        $seq_id = $seq_id + 1;
        $adb->pquery("Update vtiger_crmentity_seq set id='$seq_id'", array());
        $liveaccountinfo = $adb->pquery("SELECT liveaccountid FROM vtiger_liveaccount 
 where account_no=?", array($mt4_account_no));
        $norows = $adb->num_rows($liveaccountinfo);
        $liveaccountid = $adb->query_result($liveaccountinfo, 0, 'liveaccountid');
        $module = 'Deposit';
        $with_module = 'Account';
        $adb->pquery("INSERT INTO vtiger_crmentityrel(crmid, module, relcrmid, relmodule) 
 VALUES(?,?,?,?)", Array($liveaccountid, $module, $relcrmid, $with_module));
        $adb->pquery("INSERT INTO vtiger_crmentity(crmid , setype, createdtime, modifiedtime,label 
 , smcreatorid , smownerid) 
 VALUES(?,?,?,?,?,?,?)", Array($seq_id, $module, date('Y-m-d H:i:s'), date('Y-m-d H:i:s'), 
 $liveaccountid, 
 $assigned_user_id, $assigned_user_id));
        return $seq_id;
    }

function gen_uuid() {
        global $adb;
        $order_id = NULL;
        $res = $adb->pquery("SELECT uuid() As order_id", array());
        $norows = $adb->num_rows($res);
        if ($norows > 0) {
            $order_id = $adb->query_result($res, 0, 'order_id');
            $order_id = str_replace('-', '', $order_id);
        }
        return $order_id;
    }

Below function get max id from your custom table because we can put this id to reference table for maintaining relationship.

function getMaxId() {
        global $adb;
        $id = NULL;
        $res = $adb->pquery("SELECT MAX(id) As depositid FROM vtiger_crmentity", array());
        $norows = $adb->num_rows($res);
        if ($norows > 0) {
            $id = $adb->query_result($res, 0, 'depositid');
            $id = $id + 1;
        }
        return $id;
    }

Add custom module myc customer portal

1. Generally in Vtiger 6.4 we create a module and just check / uncheck accordingly , to be able to see module in Customer Portal. sometime if module not exists in vTiger CRM and we want a custom module without creating it in Vtiger CRM.

 

2. For example let say i want to redirect a user after successful payment from getway / providers like Paypal, Neteller to Customer portal Transaction Module.Transaction module does not exits in Vtiger CRM.

 

3. Open your portal.php which will located in root directory of your MYC customer portal. there is a class class Router { and in this class there will be a function static function start() , inside this function $avmod = $GLOBALS["sclient"]->call('get_modules', $params); , $avmod array will contained all the module which are allowed from vTiger CRM. Below is code your need append , to display Transaction link in side bar of your MYC customer portal.

    $avmod = array_merge(array("Home", 'Transaction'), $avmod);

4. Generally , transaction module contained two function success and cancel , so we need to allowed these two function whenever a request come to access Transaction success and cancel functions.Below condition / Code allowed you to access these function in MYC customer portal. You need to placed these condition inside class Router {}.

 else if ($targetmodule == 'Transaction' && isset($_REQUEST['fun']) && $_REQUEST['fun'] == 'success')
       $mod->get_success();
 else if ($targetmodule == 'Transaction' && isset($_REQUEST['fun']) && $_REQUEST['fun'] == 'cancel')
     $mod->get_cancel();

5. Now create a folder customerportal\modules\Transaction and create a file index.php and place below code

class Transaction extends BaseModule {
 function get_success() {
  $root_directory = $GLOBALS['root_dir'];
  // Associated Transaction AS Deposit , Custom module Exists in vTiger CRM 6.4
  $data['module'] = ($this->module == 'Transaction') ? 'Deposit' : '';
  $data['contactid'] = $_SESSION["loggeduser"]['id'];
  $data['sessionid'] = $_SESSION["loggeduser"]['sessionid'];
  $getway_order_id = $_SESSION["getway_order_id"];
  $order_id = isset($_GET['orid']) ? $_GET['orid'] : '';
  $payment_method = isset($_GET['pm']) ? $_GET['pm'] : '';
  $sparams = array(
  'id' => $_SESSION["loggeduser"]['id'],
  'module' => ($this->module == 'Transaction') ? 'Deposit' : '',
  'sessionid' => $_SESSION["loggeduser"]['sessionid'],
  'getway_order_id' => $getway_order_id,
  'order_id' => $order_id,
  'payment_method' => $payment_method,
  'paypal_payer_id' => $payer_id
 );
 //first validate order id
 $order_info = $GLOBALS["sclient"]->call('get_order_info', array($order_id));
 if (isset($order_info) && count($order_info) > 0 && $order_info != "") {
  $result = $this->do_save_transation($payment_method, $sparams);
 }
 if (isset($uresult) && count($uresult) > 0 && $uresult != "") {
  unset($_SESSION["getway_order_id"]);
 }
 if (isset($_SESSION["getway_order_id"])) {
  unset($_SESSION["getway_order_id"]);
 }
 $redirectUrl = 'index.php?module=Deposit&action=index';
 header("Location: $redirectUrl");
 exit;
}
public function do_save_transation($payment_method, $sparams) {
 switch ($payment_method) {
  case 'Neteller':
   break;
  case 'Paypal':
   break;
  case 'Skrill':
   break;
  case 'Debit/CreditCard':
   break;
  default:
   break;
  }
  return $result;
}    
function get_cancel() {
 $data['module'] = ($this->module == 'Transaction') ? 'Deposit' : '';
 $data['contactid'] = $_SESSION["loggeduser"]['id'];
 $data['sessionid'] = $_SESSION["loggeduser"]['sessionid'];
 $getway_order_id = $_SESSION["getway_order_id"];
 $order_id = isset($_GET['orid']) ? $_GET['orid'] : '';
 $payment_method = isset($_GET['pm']) ? $_GET['pm'] : '';
 $sparams = array(
  'id' => $_SESSION["loggeduser"]['id'],
  'module' => ($this->module == 'Transaction') ? 'Deposit' : '',
  'sessionid' => $_SESSION["loggeduser"]['sessionid'],
  'getway_order_id' => $getway_order_id,
  'order_id' => $order_id,
  'payment_method' => $payment_method,
  'paypal_payer_id' => $payer_id
 );
 $result = $GLOBALS["sclient"]->call('do_cancel_transaction', array($sparams));
  if (isset($result) && count($result) > 0 && $result != "") {
  unset($_SESSION["getway_order_id"]);
 }
 if (isset($_SESSION["getway_order_id"])) {
  unset($_SESSION["getway_order_id"]);
 }
 $redirectUrl = 'index.php?module=Deposit&action=index&status=';
 header("Location: $redirectUrl");
 exit;
 }
} 

Validating api user-key vtiger customer portal

MYC Vtiger Customer Portal with vtiger 6.4 , if you put invalid api_user & api_pass , then you try to login in customer portal. You will see it will allowed you to logged in to customer portal without verifying your keys. Configuration file generated by MYC Vtiger Customer Portal is below.

 

 return array (
  'date_format' => 'd-m-Y',
  'portal_theme' => 'default',
  'admin_user' => 'admin',
  'admin_pass' => 'admin',
  'admin_email' => 'test@code2you.com',
  'vtiger_path' => $vtiger_path,
  'upload_dir' => $upload_dir,
  'default_timezone' => '',
  'default_charset' => 'UTF-8',
  'default_language' => 'en_us',
  'api_user' => 'admin',
  'api_pass' => 'KLkUoAKPbNsLEa6w',
  'google_api_key' => '',
  'hiddenmodules' => 
  );

So for fixing this type of security checked , you need to edit portal.php file which exists in root of MYC Vtiger Customer Portal installed folder. You can open file , there is a class call User::check_login() associated with static function check_login , generally Api::connect() is built in function provided by MYC customer portal , it is placed in index.php file in root folder. if some things goes wrong like api key or api password is not valid it will return constant define as NOT_CONFIGURED , API_LOGIN_FAILED. Below is the code which not only check for api_user and api_key for vtiger 6.4 , also validate username and password.

 class User {
/*****************************************************************************
 * Function: User::check_login()
 * *************************************************************************** */
public static function check_login() {
 global $opresult;
 /* Addd by code2you */
  $crm_api_status = Api::connect();
 /* End */
 //ADDED TO ENABLE THEME SWITCHING
 if (isset($_REQUEST['theme']) && $_REQUEST['theme'] != "" && is_dir("themes/" . $_REQUEST['theme']))
  $_SESSION["portal_theme"] = $_REQUEST['theme'];
  if (isset($_SESSION["portal_theme"]))
   $currtheme = $_SESSION['portal_theme'];
  else
   $currtheme = $GLOBALS["portal_theme"];
 //********************************
 if (isset($_REQUEST['logout'])) {
 session_unset();
 $_SESSION["portal_theme"] = $currtheme;
 header("Location: index.php");
 die();
}
if (!isset($_SESSION['loggeduser']) || $_SESSION["loggeduser"] == "ERROR") {
 $login = false;
 /*ORIGINAL LINES*/
 //if (isset($_REQUEST["email"]) && isset($_REQUEST["pass"]))
 //$login = User::portal_login($_REQUEST["email"], $_REQUEST["pass"]);
 /*ENDs*/
 /* Added condition for api keys issue code2you */
 if (isset($_REQUEST["email"]) && isset($_REQUEST["pass"])) {
    if ($crm_api_status == "NOT_CONFIGURED" || $crm_api_status == "API_LOGIN_FAILED") {
        $loginerror = API_LOGIN_FAILED;
    }else {
        $login = User::portal_login($_REQUEST["email"], $_REQUEST["pass"]);
    }
 }
/* End */
if (isset($_REQUEST["email"]) && isset($_REQUST["forgot"]))
    $lres = User::forgot_password($_REQUEST["email"]);
if (!$login || $login[0] == "INVALID_USERNAME_OR_PASSWORD") {
    if ($login[0] == "INVALID_USERNAME_OR_PASSWORD")
        $loginerror = $login[0];
    if (isset($lres) && $lres == "ERROR")
        $forgot_loginerror = "The Email you Request is not in our system!";
    else if (isset($lres) && $lres == "SUCCESS")
        $forgot_successmess = "We have send an email of your password at the address!";
    if (file_exists("themes/" . $currtheme . "/login.php"))
        require_once("themes/" . $currtheme . "/login.php");
    else
        require_once("themes/default/login.php");
    session_unset();
    die();
    }
} else
  User::portal_login($_SESSION['loggeduser']['user_name'], $_SESSION['loggeduser']['user_password']);
 if (isset($_SESSION['loggeduser']) && isset($_REQUEST['fun']) && $_REQUEST['fun'] == "changepassword")
  $GLOBALS["opresult"] = User::change_password();
 if (isset($_SESSION['loggeduser']) && isset($_REQUEST['fun']) && $_REQUEST['fun'] == "wevservice")
  $GLOBALS["opresult"] = User::callWebservice();
}