Category: Coding

Some simple MySQL date-time functions

I often need the first of the month, the last of the month or how many days are in the month for reporting and forecasting purposes.

You can, of course, like myself in the past look up how these things are done every time you need them. But this gets frustrating and it’s often easier to surround yourself with your set of own SQL-functions that give you what you need and are easy to remember.

For this purpose I give you:

  1. First of the month date:

    2. drop function if exists fn_getFirstOfMonth;
    create function fn_getFirstOfMonth(date_ datetime) returns datetime
    begin
    set @dater = date_;
    set @dater = DATE_FORMAT(@dater ,’%Y-%m-01′);
    return @dater;
    end

  2. Last of the month date:

    drop function if exists fn_getLastOfMonth;
    create function fn_getLastOfMonth(date_ datetime) returns datetime
    begin
    set @dater = date_;
    set @dater = DATE_FORMAT(last_day(@dater) ,’%Y-%m-%d’);
    return @dater;
    end

  3. Days in the month:

    drop function if exists fn_getDaysInMonth;
    create function fn_getDaysInMonth(date_ datetime) returns int
    begin
    set @dater = date_;
    set @dater = day(last_day(@dater));
    return @dater;
    end

Do let me know if there are other MySQL date and time functions that you would like to know.
Happy to post them here…

Geo IP lookup – ipinfodb.com API integration using PHP

(this also explains the PHP DOM parser)

For one or the other reason, and be it that you want to determine the home country of your visitor for localisation and language options, you might find yourself looking at integrating an IP lookup through an external API.

I suppose they all work very similar, but I have just integrated my shopping cart with the API from IPInfoDB.com. How it works is explained below…

A word up-front, though: When using third party APIs – think! Do you really need to hit the API with every page request? Especially in this case, would it not be fair to assume that the whole sessions would be coming from the same IP and therefore the same country? To an extend, could you not assume that it is one user you are tracking here and that he would probably come back to your site from within the same country, even though his IP might have changed? Could you therefore store the result from the API in a browser session or in a cookie?
– I actually do store it in the DB per cookie, but that’s not important for this little tutorial.

First up, you will need to have access to the API, so you need an API key, token-code or something to identify yourself with the API. On the IPInfoDB.com site you can register and receive your free key within seconds.

Got the key? – off we go then: Thankfully this is a pretty simple API that doesn’t use SOAP, but requires a simple HTML request and returns a nicely formatted XML. Here is a result:

<?xml version=”1.0″ encoding=”UTF-8″?>
<Response>
<Status>OK</Status>
<CountryCode>GB</CountryCode>
<CountryName>United Kingdom</CountryName>
<RegionCode>P9</RegionCode>
<RegionName>Windsor and Maidenhead</RegionName>
<City>Hillingdon</City>
<ZipPostalCode></ZipPostalCode>
<Latitude>51.5167</Latitude>
<Longitude>-0.45</Longitude>
<Gmtoffset>0</Gmtoffset>
<Dstoffset>0</Dstoffset>
<TimezoneName></TimezoneName>
<Isdst></Isdst>
<Ip>217.42.247.86</Ip>
</Response>

So far so good… Let’s create a corresponding PHP class that will make the usage easy portable to different projects.

  1. I create a class and bunch of variables and add getter methods to the class, so I can return the variables when I need them:

    class geoIP {

    var $apiKey = “XXXXYOURKEYHEREXXXX”;
    var $city;
    var $country;

    function getCity() {
    return $this -> city;
    } 

  2. Now I know that API key will be available when I initialise the object. Moving on to making a call…
    It’s quite easy in this instance, because we don’t need to set SOAP headers or anything. It’s a simple API and all information we need to give IPInfoDB can be parsed into the request URL. Here is how it looks:

    http://api.ipinfodb.com/v2/ip_query.php?key=YOURKEY&ip=IPTOCHECK&timezone=false

    So I create a method, let’s say, “makeCityCall” that I can then send the IP to check to:

    function makeCityCall($ip) {

    $urler = ‘http://api.ipinfodb.com/v2/ip_query.php?key=’. $this -> apiKey .’&ip=’. $ip .’&timezone=false’;

    The “$this -> apiKey” will return the API key that we declared and initialised as a global variable for this class. The “$ip” is what I send to the method.

  3. I know when I load this URL, I get an XML response. XML is best parsed via a DOM-parser. The DOM parser comes standard with most versions of PHP and is an object in it’s own rights. We have to instantiate this and can then give it the URL we declared ($urler) to load:

    $parser = new DOMDocument();
    $parser  -> load($urler);

  4. As we saw by the result returned above, the interesting bit is wrapped in a XML node called Response. We only get one, of course, but I will still ask the DOM parser to repeat the process of getting the values from all nodes that it finds:

    $nodes = $parser -> getElementsByTagName(“Response”);
    foreach ($nodes as $node) {

    I now want to get the values of the nodes contained within the Response node:

    $cc  = $node -> getElementsByTagName(‘CountryCode’);
    $cc =  $cc -> item(0)->nodeValue;
    $this -> countryCode = $cc;

    $cty = $node -> getElementsByTagName(‘City’);
    $cty = $cty -> item(0)->nodeValue;
    $this -> city = $cty;

    $ctry = $node -> getElementsByTagName(‘CountryName’);
    $ctry = $ctry -> item(0) -> nodeValue;
    $this -> country = $ctry;

    Thus setting the variables that I later want to retrieve via the getter methods.

  5. Lastly, if I now want to make a call from a PHP application, I simply need to instantiate the new class, get the IP address from the site visitor (or from wherever) and use the method to make a request. I can then retrieve the results from the getter-methods:

    $ip = $_SERVER[‘REMOTE_ADDR’];
    include (‘PATHTOPHPFILE/geoIP.php’);
    $gIP = new geoIP();
    $countCall = $gIP -> makeCityCall($ip);

    I can then write this out onto the page or re-use it as I wish:

    echo $gIP -> getCountry() . ‘<br />’;
    echo $gIP -> getCity() . ‘<br />’;
    echo $gIP -> getCountryCode() . ‘<br />’;

I hope this gets you further…

Java Swing GUI builder for Eclipse

I often struggle to make Swing applications to look good, or even make them vaguely look like I intended. The different JFrame layout options and all the coordinates of where to place what and how long is that makes the creation of Swing interfaces difficult.

Since I don’t like using different development environments and got very used to Eclipse for anything I need to do, I was looking for a good graphical GUI builder that works as a plugin for Eclipse. I came across this really nice one: Jigloo from CloudGarden.

It’s pretty easy to install into Eclipse, via update site: http://cloudgarden1.com/update-site and then creates GUIs by drag and drop in a way that I thought wouldn’t have been possible. The commercial use of this product is prohibited without license, but this shouldn’t stop any hobby programmer, much like myself, from using this great product.

Here is a screenshot I leeched off their site (I might attach one or two myself):

Enjoy

BadgerComp / Cart & Opilion

Anyhow, so I decided to start using the Java code from Opilion for crawling tasks for BadgerComp.com.
I probably will continue building on the image getter first. I’m thinking that you will want to upload them straight somewhere. Probably via FTP. Or you could download them to your local hard disk first.

I’ll probably make the tool available via the BadgerCart Wiki, but it might be getting annoying to enter the user details all the time. Maybe I need to be able to load settings via XML?

MySQL problems: Starting MySQL. ERROR! Manager of pid-file quit without updating file.

I stumbled across this error when I tried to import an InnoDB backup into my existing MySQL server.
I have one application that collects data that I need to tie to another set of data that is recorded on a different server through a different application.
It’s been rather challenging, cause I had to work of an InnoDB backup and they aren’t easily imported to another MySQL server, especially if you want to keep the data on that other server.

Okay, so what I tried, and ultimately prevented me from restarting MySQL was:

  1. changing all tables on the MySQL server to MyISAM
  2. killing MySQL (yes, I couldn’t stop it)
  3. copying the files in the MySQL DataDir to a backup location
  4. Copying the InnoDB backup files that I want to import into my DataDir
  5. trying to restart MySQL
But this is were the trouble started. The files couldn’t be imported and I had to roll back and use my backup files. But:

# sudo /etc/init.d/mysql start
Starting MySQL. ERROR! Manager of pid-file quit without updating file.

So I deleted the pointer file in /var/lock/subsys/

# sudo rm -f /var/lock/subsys/mysql
# sudo /etc/init.d/mysql start
Starting MySQL. ERROR! Manager of pid-file quit without updating file.

This didn’t work…
After long fiddling, I found out that the files in my DataDir have changed it’s owner, since I copied them as root to the backup location and back. They were now owned by root. So after:

# sudo chown mysql:mysql *
# sudo chgrp mysql *

I could do:

# sudo /etc/init.d/mysql start
 Starting MySQL. SUCCESS!

Hope this helps you too!

Java & MySQL select query to return a two dimensional array of results

(vector of arrays actually)

When I started to do some Java again, I noticed that Java doesn’t really have an easy way of getting an array from a query, if you don’t always want to declare how many columns and rows you expect. To circumvent this problem I have a class in my toolkit that contains a method that returns a vector of result lines and each element contains an array of results…

This makes it really easy then to just write a query, like 

Vector  myResultVector = selectQuery("select * from customerdetails");

, and get the Vector of arrays back.

To illustrate, here is the method:

public Vector selectQuery(String query) throws SQLException {
  Statement st = connection.createStatement();
  ResultSet res = stat.executeQuery(query);
  
  ResultSetMetaData rsmd = result.getMetaData();
  numCols = rsmd.getColumnCount();
  
                // Optional print out:
  //System.out.println("The query was executed. The number of columns returned is: " + numCols);

         //We don't return this here in the same method, but this variable is declared earlier and can be returned through a different method.
  for (int i=1; i<=numCols; i++) {
            columnNames.add(i-1, rsmd.getColumnName(i));
           // System.out.println(columnNames.elementAt(i-1));
  }

  while (result.next()) {
   String[] arr1 = new String[numCols];
   for (int t=1; t <=numCols; t++) {
    arr1[t-1] = result.getString(t);
    //System.out.println(arr1[t-1]);
   }
   results.add(arr1);
  }
  return results;
 }

In the constructor of the actual class, I have the database connection, looking something like this:

 public mysqlConnector() {
  
  try {
   String userName = "YOURUSER";
   String passWord = "YOURPASSWORD";
   String url = "jdbc:mysql://YOURSERVER/YOURSCHEMA";
   Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            connection = DriverManager.getConnection (url, userName, passWord);
            System.out.println ("Database connection established!");
  }
  catch (Exception err1) {
   System.err.println ("Cannot connect to database server!");
   err1.printStackTrace();
  }
 }

Maybe worth mentioning the global variables that I declare in this class:

 public Vector results = new Vector ();
 public Vector  columnNames = new Vector  ();
 public int numCols  = 0;

The results, because there are other methods that are using the same Vector, the columnNames and the numCols, because there is a method that returns them when I need them:

public Vector  getColumnHeaders(){
  return columnNames;
 }
 
 public int getColumnNumber(){
  return numCols;
 }

It might also be good to close the database connection when you don’t need it anymore:

 public void disconnectionNotice() {
  if (connection != null) {
   try {
    connection.close ();
    System.out.println("Connection to DB closed!");
   }
   catch (Exception err2) {
    //err2.printStackTrace();
   }
  }
 }

Hope this helps some of you that might have struggled with the same problem. Email me if this isn’t all clear to you… Happy to help.

PHP function to exchange HTML characters

This is of course if htmlentities does not satisfy you

Ready for you to copy:

function html_strReplaceSymbols( $str) {
$str1 = str_replace( ‘&’, ‘&#38;’, $str);
$str1 = str_replace( ‘ ‘, ‘&#32;’, $str1);
$str1 = str_replace( ‘!’, ‘&#33;’, $str1);
$str1 = str_replace( ‘”‘, ‘&#34;’, $str1);
$str1 = str_replace( ‘$’, ‘&#36;’, $str1);
$str1 = str_replace( ‘%’, ‘&#37;’, $str1);
$str1 = str_replace( “‘”, ‘&#39;’, $str1);
$str1 = str_replace( ‘(‘, ‘&#40;’, $str1);
$str1 = str_replace( ‘)’, ‘&#41;’, $str1);
$str1 = str_replace( ‘*’, ‘&#42;’, $str1);
$str1 = str_replace( ‘+’, ‘&#43;’, $str1);
$str1 = str_replace( ‘,’, ‘&#44;’, $str1);
$str1 = str_replace( ‘-‘, ‘&#45;’, $str1);
$str1 = str_replace( ‘.’, ‘&#46;’, $str1);
$str1 = str_replace( ‘/’, ‘&#47;’, $str1);
$str1 = str_replace( ‘:’, ‘&#58;’, $str1);
$str1 = str_replace( ‘<', '&#60;', $str1); $str1 = str_replace( '=', '&#61;', $str1); $str1 = str_replace( '>‘, ‘&#62;’, $str1);
$str1 = str_replace( ‘?’, ‘&#63;’, $str1);
$str1 = str_replace( ‘[‘, ‘&#91;’, $str1);
$str1 = str_replace( ‘\’, ‘&#92;’, $str1);
$str1 = str_replace( ‘]’, ‘&#93;’, $str1);
$str1 = str_replace( ‘^’, ‘&#94;’, $str1);
$str1 = str_replace( ‘_’, ‘&#95;’, $str1);
$str1 = str_replace( ‘`’, ‘&#96;’, $str1);
$str1 = str_replace( ‘{‘, ‘&#123;’, $str1);
$str1 = str_replace( ‘|’, ‘&#124;’, $str1);
$str1 = str_replace( ‘}’, ‘&#125’, $str1);
$str1 = str_replace( ‘~’, ‘&#126’, $str1);
return $str1;
}