Thursday, March 28, 2013

How to use find_in_set() mysql function in Codeigniter?

How to use find in set mysql function in Codeigniter active records?

I was very upset and frustrated when I couldn't find any good function and solution in the Codeigniter Active Record Class for FIND_IN_SET type queries.

So I have used custom tweak to use find_in_set in Codeigniter active record.

$where = "FIND_IN_SET('".$searchterm."', columnName)";  

$this->db->where( $where ); 

where columnName holds an array of values.

And  it worked.  :)

When to use FIND_IN_SET()?

FIND_IN_SET() : This function is useful where you have comma separated list of values stored in database and want to check if a value exist in that comma seperated list.

Example: WHERE FIND_IN_SET( ‘yellow’, colorlist )

One of the very important mysql string function is FIND_IN_SET and its returns the position ofa string value if it is available (as a substring) within a string. String contain comma separated characters or values.

This function returns 0 when search string does not exist in the string
FIND_IN_SET is useful when you are using explode OR implode to store multiple value.
If data stored in database with comma separated value like you have cat_id and you stored it as (1,2,3,5 ) within one field , you can use FIND_IN_SET in select query to match particular value in whole field set.

FIND_IN_SET (find string, stringList)

Tuesday, March 19, 2013

Optimize site performance utilizing Caching in PHP Codeigniter Framework

Hi , all as web is taking it's new hieght our web application's database should be optimized and new techniques are required to handle the large records. Here is a concept used by CodeIgniter where you can save your query result and avoid your mysql query execute every time your page loads. 
CodeIgniter provides Database Caching Class where you can cache your query results and use later without executing query all again and crawling through tables.

Enabling Caching

1. Caching is enabled in three steps:
2. Create a writable directory on your server where the cache files can be stored. 
3. Set the path to your cache folder in your application/config/database.php file. 
4. Enable the caching feature, either globally by setting the preference in your application/config/database.php file, or manually as described below. 

Once enabled, caching will happen automatically whenever a page is loaded that contains database queries.

The caching works as follows .

The database caching uses text file on your server which will be created after caching is enabled.CodeIgniter's query caching system happens dynamically when your pages are viewed. When caching is enabled, the first time a web page is loaded, the query result object will be serialized and stored in a text file on your server. The next time the page is loaded the cache file will be used instead of accessing your database. Your database usage can effectively be reduced to zero for any pages that have been cached. 
Only the queries which collects data from tables are cached. So only read-type (SELECT) queries can be cached, the queries which write to database are not cached because they don't generate any results.

So till now, you must be wanted to know how caching files are stored here how it works , 

CodeIgniter places the result of EACH query into its own cache file. Sets of cache files are further organized into sub-folders corresponding to your controller functions. To be precise, the sub-folders are named identically to the first two segments of your URI (the controller class name and function name).

For example, let's say 
you have a controller called blog with a function called comments that contains three queries. The caching system will create a cache folder called blog+comments, into which it will write three cache files.

If you use dynamic queries that change based on information in your URI (when using pagination, for example), each instance of the query will produce its own cache file. It's possible, therefore, to end up with many times more cache files than you have queries.
The caching does not work with following query results 

Hope this article helps you enjoy optimized and zero load website.

Read more (with all the source codes).

Thursday, March 14, 2013

Fetching json data from password protected url using php and curl

From PHP, you can access the useful cURL Library to make requests to URLs using a variety of protocols such as HTTP, HTTPS, FTP, LDAP etc.

If you simply try to access a HTTPS (SSL or TLS-protected resource) in PHP using cURL, you’re likely to run into some difficulty. The problem is that cURL has not been configured to trust the server’s HTTPS certificate.

Real time example and solution

When fetching json data from password protected url using php and curl, I had faced a problem.

The problem i got is

"error:14077458:SSL routines:SSL23_GET_SERVER_HELLO:reason(1112) "

I searched many websites for the solution to this particular problem. After 2 hours of a long research, i found one working solution.


"The problem has been solved.  When using curl CURLOPT_SSLVERSION must be set to 3."

Following is the source code that I used and it worked like a charm.

function fetch_json_url(){

   $url = 'https:/';

   $username = 'your username';

   $password = 'your password';

// Initialize session and set URL.

$ch = curl_init();

// Set URL to download

curl_setopt($ch, CURLOPT_URL, $url);

// Include header in result? (0 = yes, 1 = no)

curl_setopt($ch, CURLOPT_HEADER, 0);

//Set content Type

curl_setopt($ch, CURLOPT_HTTPHEADER,array('Content-type: application/json'));

// Should cURL return or print out the data? (true = return, false = print)

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

curl_setopt($ch, CURLOPT_USERPWD, "$username:$password");

//to blindly accept any server certificate, without doing any verification //optional

   curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

   curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);

//The most important is

   curl_setopt($ch, CURLOPT_SSLVERSION, 3);

// Download the given URL, and return output

    $result = curl_exec($ch);

// Close the cURL resource, and free system resources


   $data = json_decode($result, true);



I just wanted to share the problem and how i solved it. May be you will solve the problem when you faced this problem without spending a lots of hours.


Tuesday, March 12, 2013

PHP function to check date or time between the given range

Simply use strtotime php function to solve the problem.

strtotime — "The function expects to be given a string containing an English date format and will try to parse that format into a Unix timestamp"

The function to check if date/time is within the range:

function check_date_is_within_range($start_date, $end_date, $todays_date)

  $start_timestamp = strtotime($start_date);
  $end_timestamp = strtotime($end_date);
  $today_timestamp = strtotime($todays_date);

  return (($today_timestamp >= $start_timestamp) && ($today_timestamp <= $end_timestamp));


Call function with parameters start date/time, end date/time, today's date/time. Below parameters gets function to check if today's date/time is between 2012-12-31 and 2013-11-26.

if(check_date_is_within_range('2012-12-31', '2013-11-26', date("Y-m-d"))){
    echo 'In range';
} else {
    echo 'Not in range';

Wednesday, March 6, 2013

Fixed: “The URI you submitted has disallowed characters.” CodeIgniter (CI) Error

In Codeigniter 2.1.x version if you find “The URI you submitted has disallowed characters.” message then you can solve it easily by following the below step.
Follow the following way to fix this error. (assuming codeigniter 2.1.x)
In Codeigniter application/config/config.php file. Go to the line 129
$config['permitted_uri_chars'] = 'a-z 0-9~%.:_-';
(As a security measure you are STRONGLY encouraged to restrict URLs to as few characters as possible.By default only these are allowed: a-z 0-9~%.:_-) change this code by 
If you want to allow the comma(,) in your url
$config['permitted_uri_chars'] = 'a-z 0-9~%.:_,\-'; 
Leave blank to allow all characters -- but only if you are insane.
After changes the code, save the file and refresh your browser.