Grouping data by dates

May 15, 2013

In MySQL, if you want to group data by year, month, week and day, it can be possible by using YEAR,MONTH,WEEK and DAY functions. Suppose you have following table structure of users table.

users: (id, name, created_date)

Following queries will group records.

-- group by day
SELECT DATE_FORMAT(created_date, '%Y-%m-%d'), COUNT(*) FROM users
GROUP BY YEAR(created_date),MONTH(created_date),DAY(created_date)

-- group by month
SELECT DATE_FORMAT(created_date, '%Y-%m-%d'), COUNT(*) FROM users
GROUP BY YEAR(created_date),MONTH(created_date)

-- group by year
SELECT DATE_FORMAT(created_date, '%Y-%m-%d'), COUNT(*) FROM users
GROUP BY YEAR(created_date)

The DATE_FORMAT(created_date, '%Y-%m-%d') is only used for display purpose.

How to avoid Agile disadvantages

April 25, 2012

The agile methodology has many advantages such as you are always getting feedback and quickly do the changes and show the product but with coding point of view, there is very bad impact. There is no uniformity in code.
If the developers are not enough experienced, than the resultant code is a piece of junk, even experience one’s can’t maintain the code well. The advice to those developers is take some time and think that after doing a new changes, is the quality of code still good. Can it be re-written to short the code as there is rule that less code is equal to less bugs. Ofcourse there is a complexity in problem domains, but often we make the software complex by developing from incomplete requirement and often managers/clients require changes to be done at eleventh hour and immediately.

Developers are always afriad of changing code that is working on production without any problem. Due to this approach, there comes a stage where to understand code is very difficult and to change is risky. In addition to IDE, there are also tools available for code refactoring from which you refactor or clean code very quickly.

start – stop application pool fast

May 30, 2011

During development, we often have to restart the application pool and most of the time, it’s very easy to just execute command : iisreset, which restart all processess belong to IIS.

Instead of IISRESET.EXE, there is a utility named appcmd, located at %windir%\system32\inetsrv, so first step is to add this into your PATH variable, so that you can run the following command directly in your console, instead of giving full paths to appcmd.exe.

If i have application pool named test, the following command will start the application pool.

appcmd start apppool /apppool.name:test

and to stop this, use the above command with /stop switch

appcmd stop apppool /apppool.name:test

Also note that, there should be no space between apppool.name
and application pool name (in our example, it is test), otherwise you may get this error.

ERROR ( message:The attribute “apppool.name” is not supported in the current com
mand usage. )

you can see the list of application pool by using the following command.

appcmd list apppool

PHPMyAdmin users, beware of hackers attempts

May 30, 2011

While checking request logs on my domains, i have noticed that most of hackers try to request these URLs to get access of database. They are directly targeting mysql, as it has mostly used. Following is the list of URIs:

/admin/phpmyadmin/scripts/setup.php
/typo3/phpmyadmin/scripts/setup.php
/phpmyadmin1/scripts/setup.php
/phpmyadmin2/scripts/setup.php
/pma/scripts/setup.php
/web/phpMyAdmin/scripts/setup.php
/xampp/phpmyadmin/scripts/setup.php
/phpMyAdmin-2/scripts/setup.php
/phpMyAdmin-2.2.3/scripts/setup.php
/phpMyAdmin-2.2.6/scripts/setup.php
/phpMyAdmin-2.5.1/scripts/setup.php
/phpMyAdmin-2.5.4/scripts/setup.php
/phpMyAdmin-2.5.5-rc1/scripts/setup.php
/phpMyAdmin-2.5.5-rc2/scripts/setup.php
/phpMyAdmin-2.5.5/scripts/setup.php
/phpMyAdmin-2.5.5-pl1/scripts/setup.php
/phpMyAdmin-2.5.6-rc1/scripts/setup.php
/phpMyAdmin-2.5.6-rc2/scripts/setup.php
/phpMyAdmin-2.5.7/scripts/setup.php
/phpMyAdmin-2.5.7-pl1/scripts/setup.php
/phpMyAdmin-2.6.0-alpha/scripts/setup.php
/phpMyAdmin-2.6.0-alpha2/scripts/setup.php
/phpMyAdmin-2.6.0-beta1/scripts/setup.php
/phpMyAdmin-2.6.0-beta2/scripts/setup.php
/phpMyAdmin-2.6.0-rc1/scripts/setup.php
/phpMyAdmin-2.6.0-rc2/scripts/setup.php
/phpMyAdmin-2.6.0-rc3/scripts/setup.php
/phpMyAdmin-2.6.0/scripts/setup.php
/phpMyAdmin-2.6.0-pl1/scripts/setup.php
/phpMyAdmin-2.6.0-pl2/scripts/setup.php
/phpMyAdmin-2.6.0-pl3/scripts/setup.php
/phpMyAdmin-2.6.1-rc1/scripts/setup.php
/phpMyAdmin-2.6.1-rc2/scripts/setup.php
/phpMyAdmin-2.6.1/scripts/setup.php

and various others with different versions.

so if you have similar path and you have setup.php file in a directory, remove or move it to another location. Also block that IP either by using .htacces or ask web master to do that. I think this bug has been fixed in latest version, but if you have old one, please do the necessary steps to sure that your website is safe. Also remove default users and passwords from mysql too.

how to upgrade your exisitng asp.net mvc 2 project to asp.net mvc 3

January 20, 2011

To migrate, first delete the existing MVC2 DLL reference. Add MVC3 DLL reference. You need to also update your assemblies in web.config in assemblies section. For example, my project was in .NET framework 3.5 and MVC 2, the project web.config was:

<add assembly="System.Web.Mvc, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, processorArchitecture=MSIL" />
<add assembly="System.Web.Abstractions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
<add assembly="System.Web.Routing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
<add assembly="System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />

Replace it with the following.

<add assembly="System.Web.Abstractions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
<add assembly="System.Web.Helpers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
<add assembly="System.Web.Routing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
<add assembly="System.Web.Mvc, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
<add assembly="System.Web.WebPages, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />

update MVC 2 references in web.config in Views folder too. Also update the targetFramwork version to 4.0 like below.

<compilation debug="false" targetFramework="4.0">

After these changes, you need to update, Site, application pool to .NET Framework version 4.0, using IIS manager.

Update solution file too

When I done the above steps, I see that clicking on Add New Item still shows the MVC 2 project template. so I have modified the project type in solution file manually by replacing

{F85E285D-A4E0-4152-9332-AB1D724D3325};

with the below GUID

{E53F8FEA-EAE0-44A6-8774-FFD645390401};

The former GUID shows the project type is ASP.NET MVC 2 while the later shows ASP.NET MVC 3.

REMOTE_HOST vs REMOTE_ADDR

September 29, 2010

REMOTE_ADDR and REMOTE_HOST is not same, although they often return same results on both apache/IIS. The main difference is REMOTE_ADDR returns IP Address of server while REMOTE_HOST returns the internet host name of server. so the question still remains that why both return IP. The answer to this question is on IIS and in apache too, getting host name lookups are turnedoff by default. you need to enable that but enabling can cause your site slow.

Using Amazon S3 SDK Pre-signedURL function

January 7, 2010

Today i was just exploring the amazon sdk. While using the presignedurl function of amazon sdk(C#), i was just curious that what is the server response time , zone. The amazon s3 returns the time in UTC. so simple but i was just curious that if we input time in GMT and it automatically convert it according to requestee IP time zone. i have tried to confirm but first i checked with UTC and it works 100% correctly. The second try i did is that i sent a GMT time and it works too. The amazon convert GMT to UTC according to client location. The sample code look like:

	public string GetPreSignedURL(string bucketName, string key)
	{
            string url = string.Empty;
            try
	    {
		AmazonS3 client = Amazon.AWSClientFactory.CreateAmazonS3Client("ur access key", "secret key");      
                GetPreSignedUrlRequest request = new GetPreSignedUrlRequest().WithProtocol(Protocol.HTTPS).WithBucketName(bucketName)
.WithKey(key)
.WithExpires(DateTime.UtcNow.AddMinutes(1));  
               url = client.GetPreSignedURL(request);
	    }
            catch { }            
	    return url; 
        }

Increase IIS 5 connection limit on windows

September 30, 2009

On a non server edition of window operating system, we often got the error.
There are too many people accessing the Web site at this time.
OR
HTTP 403.9 – Access Forbidden: Too many users are connected
Internet Information Services

To increase the connection limit, go to :
1 – Go to directory c:\inetpub\AdminScripts
2 – Execute the following command.
adsutil set w3svc/MaxConnections 40

Read or Write on Registry in C#

August 26, 2009

Registry is a special place where applications stores its setting. Although Isolated storage for .net is a preferred way for storing
application settings yet registry has its own benefit. Normally it is difficult to find application created registry entries(if created carefully).
Below is a sample C# code.

//It will create a new subkey if not already created. otherwise it opens the existing one with write access.
RegistryKey objRegistryKey = Registry.CurrentUser.CreateSubKey("App_Name");
//Suppose i want to save last open time when application opened.
objRegistryKey.SetValue("LastOpenTime", DateTime.Now.ToString());

To get the value from registry, you use

string lastOpenDT = objRegistryKey.GetValue("LastOpenTime") as string;

The GetValue return the object type. you can than cast to the appropriate type.

Don’t forget to add Microsoft.Win32 namespace.

For further reading,you can see the following two classess at msdn.
Registry,RegistryKey class.

multiple submit buttons and the enter key

May 21, 2009

As you know it is the limitation of asp.net that it has only one form, so if you have another section which needs enter key submission, than you can use default button as used below.
The defaultButton property of Panel or Form is used to ensure Enter Key Submission. Typically people used to Enter Key submission and if it is disabled, it might be annoying certain times.

The defaultButton really provides a way to handle Enter Keys for respective portions of the page when the focus is there.

Example:


Switch to our mobile site

 
Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org