digimanpk's Blog: February 2013

Tuesday, 5 February 2013

CORS - Cross Domain Resource Sharing With Ajax and JSON - Example

Recently, I was ask to build a prototype to demonstrate cross-domain resource sharing through AJAX and JSON. For those who dont know, cross-domain AJAX request are not allowed by default until the web server handling the request allows it. This is done by sending the "Access-Control-Allow-Origin" header. Following is an example of a client and server peforming CORS.

Server Code:

 <?php  
 header('Access-Control-Allow-Origin: *'); 
 $Response = array(1,2,3); 
 echo json_encode($Response);      
 ?>

Ajax Client Code:

 <html> 

 <head> 

 <title>test</title> 

 <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.0/jquery.min.js"></script> 

 </head> 

 <body> 

 <input type="button" value="test" onclick="testajax()" /> 

 </body> 

 <script> 

 function testajax() 

 { 

  $.ajax({ 

   crossdomain : true, 

   type : "text/plain", 

   type : "GET", 

   url : "http://www.testerzone.com/test/test.php", 

   success : function (data){ 

    alert(data); 

   }, 

   error: function (xhr, ajaxOptions, thrownError) { 

     alert(xhr.status); 

     alert(thrownError); 

     } 

  }); 

 } 

 </script> 

 </html>

Sunday, 3 February 2013

PHP How To : Creating date ranges for filtering in PHP for MySQL query - Part 2

In Part 1, I provided the code snippets and explanation for building dynamic date ranges in PHP that can be used with MySQL queries with BETWEEN keyword. The snippets were

 // For last six months, 
     $start = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days -6 month

')); 
     $end = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days')); 
 // For last month, 
           $start = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days -1

month')); 
           $end = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days ')); 
 // For last year, 
           $start = date('Y-m-d', strtotime('1 jan last year')); 
           $end = date('Y-m-d', strtotime('31 dec last year +1 day '));

However, it still has a problem. Since, we are including an extra day in order to include results for the whole day of the end date (see Part 1 for explanation), this also leads to the inclusion of results with timestamp right at the start of the extra day. Lets take the "Last Six Month" case as an example. If I execute this statement today (3rd of Feb), it will get me "2013- 02-01". So if I use this as end date, then MySQL will return me a result set that will also include records with timestamp ="2013-02-01 00:00:00" which is undesirable. Therefore, in order to get rid of these undesirable records from the result set, I simply subtract a second from the end date. So, the code snippets now become

 // For last six months, 
     $start = date('Y-m-d H:i:s', strtotime('today -' . (date('j') - 1) . ' days

-6 month')); 
     $end = date('Y-m-d H:i:s', strtotime('today -' . (date('j') - 1) . ' days

-1 second')); 
 // For last month, 
           $start = date('Y-m-d H:i:s', strtotime('today -' . (date('j') - 1) .

' days -1 month')); 
           $end = date('Y-m-d H:i:s', strtotime('today -' . (date('j') - 1) . '

days -1 second ')); 
 // For last year, 
           $start = date('Y-m-d H:i:s', strtotime('1 jan last year')); 
           $end = date('Y-m-d H:i:s', strtotime('31 dec last year +1 day -1

second'));

PHP How To : Creating date ranges for filtering in PHP for MySQL query - Part 1

MYSQL query for querying results between a date range is not that difficult at all. It goes like

 SELECT * FROM my_table WHERE timestamp BETWEEN '2013-01-01' AND '2013-02-01'

However, things get a bit interesting when the date range becomes dynamic. For example, in one of the projects that I have worked on , I was required to provide the feature of filtering results for last month, last 6 months and so on. You can see the problem clearly here. The date ranges need to be made only the fly.
In order to filter data by date ranges, for example, for Last Six Months, Last Month or Last Year, PHP's date() and strtotime() function can be used in combination with each other. Let me provide snippets for them.

 // For last six months,  
     $start = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days -6 month '));  
     $end = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days'));  
 // For last month,  
           $start = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days -1 month'));  
           $end = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days '));  
 // For last year,  
           $start = date('Y-m-d', strtotime('1 jan last year'));  
           $end = date('Y-m-d', strtotime('31 dec last year +1 day '));

Here, the code for getting date range covering whole of the last year is easier to understand. The range starts from 1 Jan of last year and ends on 1 Jan of this year. But why 1st Jan of this year?? After all, we only want to get records till 31st of Dec of last year. The reason for this is that if you just provide a date in the SQL statement mentioned above , MySQL takes it as start of that date. Therefore, if we were just to write '31 dec last year', then the results would not include those records with timestamp greater than "2012-12-31 00:00:00" and we will miss results for a whole day. Adding "+ 1 day " allows us to include those results as well.

The snippets for last month and last 6 months are a bit difficult to understand at first , however, they are quite easy once you know what happening in them. Let me explain one of them. For the last six months case, the start date is computed by first getting the start of the current month ( the (date('j') - 1) part). Then it further subtracts 6 months to get the start of the six month period. For end date, it just compute the start of the current month. The case for the last month is identical to this one except that only 1 month is subtracted instead of 6 in computing the start date.
So there you have it.............. but we are not done yet. There is a slight problem with date range , which may result in unwanted records being included in the result. I have explained this in part 2 and tweaked the code to fix it.