Mysql GROUP BY context

November 23, 2016

Encountered this MYSQL challenge at work today.

Given a table called test_table like:

+—-+——-+
| id | value |
+—-+——-+
|  1 | 1     |
|  2 | 1     |
|  3 | 2     |
|  4 | 2     |
|  5 | 1     |
|  6 | 1     |
|  7 | 2     |
|  8 | 3     |
|  9 | 3     |
|  10 | 3     |
+—-+——-+

We wanted a result like:

+——-+——–+
| value | amount |
+——-+——–+
| 1     |      2 |
| 2     |      2 |
| 1     |      2 |
| 2     |      1 |
| 3     |      3 |
+——-+——–+

So basically, we wanted to keep the order, but GROUP BY when there appeared to be values that could be grouped together. So if the same value occured more than once they should be group together as one group without grouping every occurance of the value.

The usecase of this is typically a feed of events, like this:

  • The event with of type 1 occured 2 times.
  • The event with of type 2 occured 2 times.
  • The event with of type 1 occured 2 times.
  • The event with of type 2 occured 1 time.
  • The event with of type 3 occured 3 times.

Instead of:

  • The event with of type 1 occured 1 time.
  • The event with of type 1 occured 1 time.
  • The event with of type 2 occured 1 time.
  • The event with of type 2 occured 1 time.
  • The event with of type 1 occured 1 time.
  • etc.

Took some work, but ended on this query:

SET @next := 0, @last := 0;

SELECT test_table.value,
COUNT(test_table.id) AS amount
FROM test_table
LEFT JOIN (
SELECT test_table.*,
IF(@last != value, @next := @next+100, 0) AS xx,
IF(@last != value, @last := value, 0) AS xy,
IF(value, value+@next, 0) as group_value
FROM test_table
) AS test_table_b ON test_table_b.id = test_table.id
GROUP BY test_table_b.group_value

Now, this is not all fool proof. The value that is added to @next may be a constant that has to change depending on the value you want to group by. The last issue to consider is that working with variables like this is not an exact sience, but it work in my example.

Hopefully this was helpfull to some. It was to me.

 


Probability poll in PHP

March 20, 2012

I searched for a good way to make a poll in PHP, that made it easy to pick a user by X chance of probability in relation to other users. I did not find any easy and simple solution so I wrote one myself.

class poll {
    private $data = array();
    private $list = array();
            
    public function add($data, $value){
        $this->data[] = $data;
        end($this->data);
        for($i=0; $i < $value; $i++){
            list[] = &$this->data[key($this->data)];
        }
    }
    public function get(){
        return $this->list[rand(0, count($this->list)-1)];
    }
}

You add the items into the poll with specified probability, and then pick one item from the poll based on all the items in it. Example of two users in the poll both having the probability of 50% to be picked:

$poll = new poll();
$poll->add($user1, 50); //or 1
$poll->add($user2, 50); //and 1
$chosen = $poll->get();

There are some flaws and many ways to expand the class (like including the removal of the chosen from the poll – so that you can pick more and not the same twice), but I tried to make the most simple solution of a poll like this.

Hope you like it 🙂


Better use of a Getter method in PHP

February 25, 2012

You are probably familiar with getters and setters in object-oriented code. Usually they are something like this:

public function GetId(){
   return $this->id;
}
public function SetId($value=0){
   $this->id = $value;
}

Normally I improve these two methods by setting them into one method, like this:

public function Id($value=null){
   if($value == null){
      return $this->id;
   } else {
      $this->id = $value;
   }
}

But when doing this you have no place to add arguments into the getter method, based on the idea of a improved getter. Having an input argument as the fallback value if what you are getting is the false value. This is the new getter:

public function GetId($fallback=0){
   if(!$this->id || $this->id == 0)){
      return $fallback;
   } else {
      return $this->id;
   }
}

For an id this kind of fallback functionality may not make that much sense(but it might), but for a GetName or other it probably is. Consider this:

$html = 
'<input name="name" value="'.
($obj->GetName()?$obj->GetName():'Please enter your name')
.'" />';

Now you can replace it with this reducing unneccessary logic from the presentation:

$html = 
'<input name="name" value="'.
$obj->GetName('Please enter your name')
.'" />';

The evolution of how I code common objects

November 20, 2011

I always try to make my code as small as possible, as long as it is understandable to a degree – and simple code is for me often that. DRY is a good rule, sometimes hard to figure out how to not repeat myself, but sometimes I discover good techniques.

In my early programming days, in the case of objects, I always made every object like this:

class Student {
   private $name;
   private $address;
   
   public function __construct($name, $address){
      $this->setName($name);
      $this->setAddress($address);
   }
   
   public function setName($name){
      $this->name = $name;
   }
   public function getName(){
      return $this->name;
   }
   public function setAddress($address){
      $this->address = $address;
   }
   public function getAddress(){
      return $this->address;
   }
}

$student = new Student();
$student->SetName('John');
echo $student->GetName();

Then I discovered that I don’t need to have a getter and setter for every property, I wanted to get rid of them and it could be done like this:

class Student {
   private $name;
   private $address;
   
   public function __construct($name, $address){
      $this->Name($name);
      $this->Address($address);
   }
   
   public function Name($name=null){
      if($name===null){
         return $this->name;
      }
      $this->name = $name;
   }
   public function Address($address=null){
      if($address===null){
         return $this->address;
      }
      $this->address = $address;
   }
}

$student = new Student();
$student->Name('John');
echo $student->Name();

Notice, that the use of null here do that you cannot use null as a value to a property, but the properties are null if you get them and they are not set. Anyways, this became all too much code when you got like 10+ objects with 10+ properties each, repeating the syntax for every property. So I turned into something like this:

class Student {
   private $name;
   private $address;
   
   public function __construct($name, $address){
      $this->Name($name);
      $this->Address($address);
   }
   
   public function set_get($property, $value=null){
      if($value===null){
         return $this->$property;
      }
      $this->$property = $value;
   }
   
   public function Name($name=null){
      $this->set_get('name', $name);
   }
   public function Address($address=null){
      $this->set_get('address', $address);
   }
}

$student = new Student();
$student->Name('John');
echo $student->Name();

But why do one have to go through a method for each property? we can easily get rid of all that by simply using the set & get and then use an array as the property holder.

class Student {
   private $properties = array();
   
   public function set($property, $value){
      $this->properties[$property] = $value;
   }
   public function get($property){
      return (isset($this->properties[$property])
              ?$this->properties[$property]
              :false;
   }
}

$student = new Student();
$student->set('name', 'John');
echo $student->get('name');

Less code, easy to maintain, only drawback is to make som additional actions in every set/get, something that I seldom do anyways. Also, every set/get does not turn up in the list of methods that Eclipse/NetBeans find. If needed those can get own methods. Still, this is all too much for every single class when the code is the same on many – it is better to extend a class that has that code:

class Master {
   private $properties = array();
      
   public function set($property, $value){
      $this->properties[$property] = $value;
   }
   public function get($property){
      return (isset($this->properties[$property])
              ?$this->properties[$property]
              :false;
   }
}

class Student extends Master {}

$student = new Student();
$student->set('name', 'John');
echo $student->get('name');

This way every object get the same functionality and only one line is required to make one object. But why not go even further. Both in how values are set and get, and how classes are created. PHP has this magic function called __autoload(), and including this inside it make it so that any class can be created even if it does not exist:

class Master {
   private $properties = array();
   public function val($prop, $val=null){
      if($val===null){
         return (isset($this->properties[$prop])
                 ?$this->properties[$prop]
                 :false);
      } 
      $this->properties[$prop] = $val;
   }
}
function __autoload($class) {
   eval('class '.$class.' extends Master {}');
}

$student = new Student();
$student->val('name', 'John');
echo $student->val('name');

Now I can create any object with any name on the fly. It is like a stdClass, but with functionality and not anonymous. But isn’t the use of eval like… evil? Well, typically it should be avoided, especially if the content you put in is user-generated from a form. If this is the case, you have to strip away everything from the class name that does not belong in a class name before you evaluate it. Don’t do it if you don’t know what you are doing. There are some more stuff I want to do with this:

class Master {
   private $props = array();
   public function val($prop, $val=null){
      if($val===null){
         return (isset($this->props[$prop])
                 ?$this->props[$prop]
                 :false);
      } 
      $this->props[$prop] = $val;
      return $this;
   }
}
function __autoload($class) {
   eval('class '.$class.' extends Master {}');
}
function obj($name){
   return new $name();
}

echo obj('Student')->val('name', 'John')->val('name');

Like that! Now I don’t have to initiate a new object every time I want to make one, and I added chaining so that every value can be set more fluid. All of the code above is only a starting point, and need additional stuff to make them robust, secure, and stuff like that. I hope you get the idea, because in many projects it is unnecessary and dull to create bloated objects for every simple type of class you want to use. In more complex projects you may need to skip some of the simplifications, to get more flexibility in functionality for each class.


Debug slow code with ticks

October 24, 2011

I made this small snippet of code today, using my style of coding with a singleton class wrapped into a function. The idea is to time the differents parts of the code to find where the code is slowest, and thus, where to improve and optimize. The way you use the code is like this:

tick('Initialize');
/* CODE TO TEST */
tick('Code snippet one');
/* CODE TO TEST */
tick('Code snippet two');
tick()->show();

This will echo something like:

Msg: Initialize
Msg: Code snippet one
Time: 3.543 sec
Msg: Code snippet two
Time: 1.002 sec

Here is the full code:

function tick($msg = ''){
    $instance = ticks::inst();
    if($msg != ''){
        $instance->tick($msg);
    }
    return $instance;
}

class ticks {
    private $tick = array();
    private static $instance;
   
    public static function inst(){
        if(!isset(self::$instance)){
            self::$instance = new self();
        }
        return self::$instance;
    }
   
    public function tick($msg=''){
        $time = (string)microtime(true);
        $this->tick[$time] = $msg;
    }
   
    public function show(){
        $last = 0;
        foreach($this->tick as $time => $msg){
            $time = (float)$time;
            echo 'Msg: "'.$msg.'"';
            if($last_time != 0){
                $sec = round($time-$last, 3);
                echo 'Time: "'.$sec.' sec"';
            }
            $last = $time;
        }
    }
}

A tip when you are stuck

June 20, 2011

The other day at work I was making this algorithm, but did not get it to work. It looked so simple, but it was not – so I worked with it for a few hours without result.

When I took the time of for a few days, and decided to look at the problem again I fixed it in a few minutes. It was not a perfect fix, but as good as.

So, the tip when you are stuck:

Take some days off, and think of other things, and when you are back you may se an easy solution.


PHP $_GET in JavaScript

June 15, 2011

This other day I encountered a situation where I needed to pass in variables to a javascript. I had built the javascript in a PHP file before, and could easily use PHP to inject the variables like this:

var name = "<?php echo $name ?>"

But when I wanted to separate the javascript from PHP I could not access the PHP variables anymore, the solution was to do it like this:

<script src=”my_script.js?name=<?php echo $name ?>” ></script >

and then access the variable from the script by $_GET.name

The code on jsfiddle:
http://jsfiddle.net/ctncP/


A better way to organize localhost

June 12, 2011

I have a lot of projects going on, and I’m doing them all on my home computer, using a xampp server @ locaholst.

Since I have a lot of projects, the folder structure is like this:

c:\xampp\htdocs\project1 => http://localhost/project1
c:\xampp\htdocs\project2 => http://localhost/project2
etc.

The problem was that when the sites goes live, they are like:

http://localhost/project1 => http://project1.com
http://localhost/project2 => http://project2.com
etc.

This is messed up my code because the root folder changed. On localhost, the root folder is http://localhost/ and the projects are inside subfolders. My solution was for a long time to have a method named getRoot() that returned /projectX/ subfolder as root when localhost and / as root on live server. But I had to use this method every single time I make a link. This is not really a problem until you start making URLs pretty, and some other occasions.

Well, then, one bright and sunny day I though, why am I doing this? Shouldn’t there be an easier way? And of course there was – I just never really bothered to check.

Add lines into c:/windows/system32/drivers/etc/hosts file:

127.0.0.1 project1.local
127.0.0.1 project2.local

And then configure your local httpd.conf file, with adding the lines and restarting apache:
<VirtualHost *:80>
ServerName project1.local
DocumentRoot “c:/xampp/htdocs/project1”
DirectoryIndex index.php index.html index.html index.htm index.shtml
</VirtualHost>
<VirtualHost *:80>
ServerName project2.local
DocumentRoot “c:/xampp/htdocs/project2”
DirectoryIndex index.php index.html index.html index.htm index.shtml
</VirtualHost>

Now, when you want to test your site locally, you can use the addresses:

http://project1.local and http://project2.local

You don’t need to have your site inside c:/xampp/htdocs/ anymore either, by changing the VirtualHost configuration you can easily put them into c:/web/ or a folder of your choice.


The curious case of a textarea

May 19, 2011

I came across this little problem today, where I wanted the modified content of a textarea.

This is the textarea:

<textarea id="area">Some text</textarea>

I wanted to edit it, and then alert the result. But guess what, the alerted text said “Some text” and not “Edited text”

The following code was tried:

alert($('#area').text());
alert($('#area').html());
alert(document.getElementById('area').innerHTML);

The answer was really simple after some minutes of trying, but the question why still remains.

This does the trick:

alert(document.getElementById('area').value);
alert($('#area').val());


try to catch this if else

May 1, 2011

How should you write “try catch” and “if else”?

I’ve seen a lot of this:

if() {
}
else {
}

try {
}
catch {
}

But it really should be like:

if() {
} else {
}

try {
} catch {
}

Why? It’s really a little detail, but in the first examples it looks like you could add code between the try and the catch, because they are two blocks of code. But the catch belongs to the try, so it should not be seperated by a new line.