Month: December 2016

29 Dec

Fix for Codeigniter WHERE IN query issue due to comma

In the below code we have an array of ids named $list_ids. We would like to mark the status of rows with ‘my_id’ = 1, my_id = 2 and my_id = 3 as Completed.

            $data['status'] = "Completed";
            $list_ids = array(1,2,3);
            $this->db->where_in('my_id', $list_ids);
            $this->db->update('my_table', $data);
            echo $this->db->last_query();

But after executing the above query, you might be surprised. Only row with ‘my_id’ = 1 gets updated. You might notice no change to the other two rows? Why?

Look at the generated query below.

UPDATE `my_table` SET `status` = ‘Completed’ WHERE `my_id` IN(‘1,2,3’)

There is a comma before 1 and after 3. The query should be
UPDATE `my_table` SET `status` = ‘Completed’ WHERE `my_id` IN(1,2,3)
to work it as expected.

For that we need to pass an extra parameter to the where_in function. The working code is given below.

            $data['status'] = "Completed";
            $list_ids = array(1,2,3);
            $this->db->where_in('my_id', 
                   $list_ids, FALSE);
            $this->db->update('my_table', $data);
            echo $this->db->last_query();

You can see that, we changed the where_in code from
$this->db->where_in(‘my_id’, $list_ids) to
$this->db->where_in(‘my_id’, $list_ids, FALSE);

Hope it’s helpful.

26 Dec

Prevent bootstrap affix overlapping with footer

Fixing a div to the right side of your web page using
bootstrap’s affix method is easy.

Steps required are
1)Add data-spy=”affix” to the div you would like to fix in the right side. Also Add a class to the div, in the example i added the class as my-affix-div.
The whole affix div code is given below.

<div class="col-sm-5 pull-right my-affix-div" 
        data-spy="affix"  >
        <div class = "panel 
                panel-primary 
                panel-transparent affix_div" >
            <div class ="panel-heading">
                <h3 class = "panel-title">Summary</h3>
            </div>

            <div class = "panel-body">
                <div class="row">
                    <div class="col-sm-4">
                        <b>Name</b>
                    </div>

                    <div class="col-sm-3">
                        <b>Place</b>
                    </div>

                    <div class="col-sm-3">
                        <b>Phone</b>
                    </div>                  
                </div>
  
            </div>

            <div class = "panel-footer">
                <form role="form" action="#"
                     method="get">
                    <button type="submit" 
                          class="btn btn-default"
                    >
                        Clear
                    </button>
                </form>
            </div>

        </div>
    </div> 

2)Then add the below Javascript code.

$('.my-affix-div').affix(
   {offset:{top: 75, bottom: 240}}
);

In the code we specified when the div should be fixed.
If the scroll top is more than 75 pixel, then the div with the class name my-affix-div will be fixed. If we scroll to the bottom and if the distance from bottom to the div is less than 240,
the div’s position becomes absolute.

At any moment the div with data-spy=”affix” , will be in one of the three states.
1)affix-top: If your scroll top is less than 75px.
2)affix: (If your scroll top is more than 75 px
and scroll bottom is more than 240 ).
3)affix-bottom: if your scroll bottom is less than 240.
We can specify styles of those states. Below CSS code is added to specify that the position should be absolute in both
affix-bottom and affix-top states. in affix state, the div will be fixed to the right side and distance to the top of the div will be
200px.

.affix-bottom{
    position: absolute;
    right: 0;  
}
.affix-top{
    position: absolute;
    right: 0;  
}
.affix {
        top: 200px;
        right: 0;  

 }
19 Dec

How to add popover to awesome font?

If you like to display a popover as shown in the above image,
You should include following files in your html head.

    <head>
        <script src="js/jquery.min.js"></script>
        <script src="js/bootstrap.min.js"></script>
        <link rel="stylesheet" href="css/font-awesome.min.css">
        <link rel="stylesheet" href="css/bootstrap.min.css">

    </head>
                 

Below is the html body content. The popover should come when placing mouse over the awesome font with class name “fa fa-question”. The awesome font is written as an inner html of a tag. Two important attributes we added to the tag are 1)data-toggle=”popover” and 2)data-content=”[popover message]”.

<body>
<div class="container">
    <h2>Sample Popover Code</h2>
    <div class="panel-group">
        <div class="panel panel-default">
            <div class="panel-heading">Popover with awesome font</div>
            <div class="panel-body">
                <div class="row form-group">

                    <div class="col-sm-6">
                        <input class="form-control" type="text"
                               placeholder="Name"  name="name" />
                    </div>

                </div>

                <div class="row form-group text-center">

                    <div class="col-sm-6">
                        <div class="input-group">
                            <input placeholder="Address"
                                   class="form-control pooja-star"
                                   type="text" name="address"/>
                            <span class="input-group-addon">
                                <a 
                                    data-toggle="popover"
                                    data-content="Enter Address as
                                    Street,City,State">
                                    <i class="fa fa-question"></i>
                                </a>
                            </span>
                        </div>
                    </div>

                </div>
                <div class="row form-group text-center">
                    <div class="col-sm-6">
                <button type="submit" class="btn btn-primary">
                Save changes
            </button>
                </div></div>
            </div>
        </div>

    </div>
</div>
</body>

Below javascript code is required, where we set when the popover should be displayed, and also we are defining the selector.

As we set selector: ‘[data-toggle=”popover”]’ and
trigger: ‘hover’, when we place mouse over an element with attribute data-toggle=”popover” the popover function will be called.

<script type="text/javascript">
    var popOverSettings = {
        trigger: 'hover',
        container: 'body',
        html: true,
        selector: '[data-toggle="popover"]', 
 
    }
 
    $('body').popover(popOverSettings);
</script>

You can customize the look of awesome font and the background box
by adding your style to the below classes.

.input-group-addon {
    background-color: #your-color-here;
    border:0px
}

.fa-question {
    //style here
}
5 Dec

WHMCS Database Queries

WHMCS use Laravel framework’s database component.
So Insert, Select, Update and Delete queries should be written in
Laravel style.

If you are planning to do the database query from a custom WHMCS page, add the below code
at the top of the page.

require_once("init.php");
use Illuminate\Database\Capsule\Manager as Capsule;

If you are going to do query from a module page, just the below code is enough.

use Illuminate\Database\Capsule\Manager as Capsule;

Select rows

In order to select all rows from the table tblinvoices,
we can use the below code.

//get all rows
try {
    $data = Capsule::table('tblinvoices')
        ->get();
    echo "<pre>"; print_r($data);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

To select just paid invoices, we can use the below query.

//get all rows
try {
    $data = Capsule::table('tblinvoices')
        ->where("status", "=", "Paid") // we can add multiple where conditions
        ->get();
    echo "<pre>"; print_r($data);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Select a single row

To select a single row, we can use the function first instead of the function get as shown below.

//get a single row, note we added a where condition too
try {
    $data = Capsule::table('tblinvoices')
        ->where("id", "=", 3)
        ->first();
    echo "<pre>"; print_r($data);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Get value of a single field.

Assume that we just need to know paymentmethod of an invoice with id=3,
we can use the below query. Note that we used ->value(‘paymentmethod’) instead of first().

try {
    $data = Capsule::table('tblinvoices')
        ->where("id", "=", 3)
        ->value('paymentmethod');
    echo $data;
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Add a row

Now let’s add a row to the table tblconfiguration.

//insert a row
try {
    //key value pair.
    $insert_array = [
        "setting" => "Discount",
        "value" => "60",
        "created_at" => date("Y-m-d H:i:s", time()),
        "updated_at" => date("Y-m-d H:i:s", time()),
    ];
    Capsule::table('tblconfiguration')
        ->insert($insert_array);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Add a row and get the last insert id.

To get the last insert id, we have to use insertGetId function instead of the insert.

//get insert id
try {
    $insert_array = [
        "date" => date("Y-m-d H:i:s", time()),
        "description" => "Testing insert id stuff",
        "user" => "test",
        "userid" => 1,
        "ipaddr" => "127.0.0.1"
    ];
    $new_log_id = Capsule::table('tblactivitylog')
        ->insertGetId($insert_array);
    echo $new_log_id;
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Update a table

To update a table, we can write the query as given below.

//update a table
try {
    $update_data =  [
        'status' => 'Paid',
        'paymentmethod' => 'paypal'
    ];
    Capsule::table('tblinvoices')
       ->where('id', '=', 9)
       ->update($update_data);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Delete from a table

#delete from a table
try {
    Capsule::table('tblactivitylog')
        ->where('id', '=', 12)
        ->delete();
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Sample Join Query

If we have to join two tables tblinvoices and tblinvoiceitems on a condition tblinvoices.id = tblinvoiceitems.invoiceid, then we can write the join query as written below. For any select query we can just select interested field’s values. In the below query we are just selecting three field values, they are tblinvoices.id, tblinvoiceitems.description, and tblinvoiceitems.amount.

    //Join query
try {
    $invoice_details = Capsule::table('tblinvoices')
         ->join(
             'tblinvoiceitems',
             'tblinvoices.id',
                  '=', 'tblinvoiceitems.invoiceid'
         )
         ->select(
               'tblinvoices.id',
               'tblinvoiceitems.description',
               'tblinvoiceitems.amount'
        )
        ->get();
        echo "<pre>"; print_r($invoice_details);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

To know more about the query,
Go to Laravel Query Tutorial

5 Dec

CRUD Using Codeigniter

Db operations using codeigniter is explained briefly with codes.

Add a new row
A row can be inserted as shown below. To get the unique id of the row inserted, we have to use the function: $this->db->insert_id().

$data['user_gid'] = $user_gid;
$data['username'] = $user_name;
$data['email'] = $email;
$this->db->insert("mytable", $data);
$user_id = $this->db->insert_id();

Update a row
To update a row, we have to pass a where condition.
mutiple where conditions are possible. Below sample code just used one where condition.

$data['user_gid'] = $user_gid;
$data['username'] = $user_name;
$data['email'] = $email;
$this->db->where('user_id', $user_id);
$this->db->update('mytable', $data);

Select a row
To select a row that matches a where condition, we have to pass a
where condition just like we passed for update.
$this->db->select(“*”) will select all fields of the table.
To select just one field or specific fields we can specify the field names.

//get all fields
$this->db->select("*");
//to get  email and username
//$this->db->select("username,email");
$this->db->from("mytable");
$this->db->where('user_id', $user_id);
$query = $this->db->get();
$row = $query->row_array();
return $row;

Select all rows

$this->db->select("*");
$this->db->from("mytable");
$query = $this->db->get();
$rows = $query->result();
return $rows;

Delete a row

$where = array(
    'user_id' => $user_id
);
$this->db->delete("mytable", $where);