Cody Taylor

I had to write some reports for some legacy software today and I was unpleasantly surprised with the results of my SQL queries. I was selecting dollar values and summing them to for the monthly spending of certain individuals. Easy enough right? I wrote a query something like this :

SELECT SUM(t.money_spent) as sum_of_spent,
c.customer_name 
from transactions t 
join customers c on t.customer_id=c.customer_id 
group by customer_name order by c.customer_name asc

I ended up getting numerical values that were 10 decimal places long with seemingly random numbers. After checking to make sure the database didn’t have any odd entries I stumbled on this bug report.
The ‘money_spent’ column had a data type of float, which is a waste, but I still don’t think that it should sum up incorrectly. When I select individual values I get proper two decimal results.
Apparently floats and doubles use floating point math, which deals with approximate values for numbers and can thus result in confusion like this. It seems that it isn’t really possible to store 0.1 in a column of type float. You can only store 0.00999999977648258. This behavior is a little silly but easily fixed by using the ROUND() function :

SELECT SUM(ROUND(t.money_spent)) as sum_of_money_spent,
c.customer_name from transactions t 
join customers c on t.customer_id=c.customer_id 
group by customer_name order by c.customer_name asc

PHP makes life a lot easier for quick or dirty maintenance scripts, cron jobs or web applications but how does it do for older, not so straight forward problems dealing with bits and bytes? I was surprised how easy it was to manipulate bits in a byte with php. Here is an function that made my life a fair amount easier when having to check for a specific bit in a byte.

This function checks whether a certain bit is set or not given a byte and an index. It returns true if the chosen bit is set. It casts the $value argument to a integer just in case. The index $n goes from left to right so the most significant bit is bit one and the least significant is bit eight. This function will only work for integers between 0 and 255 because that was all I needed at the time. It would be trivial to write either a function to separate bytes in an integer or to increase the amount of bits that this function checks. I originally had a different function here but the Internet quickly told me that there was an easier way to do this.

<?php

function check_bit($value,$n=8)
{
    $value = (int)$value;
    if($value & (1<<(8-$n))) { return true; }
    else { return false; }
}

//Check Bit Usage Example
$test_byte1 = "4";

if( check_bit($test_byte1,2))
  echo "Bit 2 is set in ".decbin($test_byte1);
else
  echo "Bit 2 is not set in ".decbin($test_byte1);

?>

Here are the php bitwise operator definitions from the php documentation. Look like C much?

$a & $b	And	Bits that are set in both $a and $b are set.
$a | $b	Or	Bits that are set in either $a or $b are set.
$a ^ $b	Xor	Bits that are set in $a or $b but not both are set.
~ $a	Not	Bits that are set in $a are not set, and vice versa.
$a << $b	Shift left	Shift the bits of $a $b steps to the left (each step means “multiply by two”)
$a >> $b	Shift right	Shift the bits of $a $b steps to the right (each step means “divide by two”)