PHPExcel lock cells

I’ve been using wonderful PHPExcel library for exporting my mysql data to excel via PHP and encountered a little setback trying to lock particular cells to prevent my client from editing values I’d need to process when uploading the file back to the system so I tried the most straightforward method I found in the official documentation but nothing seemed to work until I came across this solution:

$obj_Sheet->getProtection()->setPassword('pass_to_remove_protection');
$obj_Sheet->getProtection()->setSheet(true);
$obj_Sheet->getStyle('B2:J5')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

First I set the password to remove protection (‘pass_to_remove_protection’) that anyone can use to remove protection (as long as they know the password), then I turn on sheet protection and finally set all cells in a range from B2 to J5 to unprotected. This basically locked EVERYTHING on selected sheet and unlocked the fields I specified as unlocked.

Searching for mySQL data in case insensitive mode

I’ve been working on search engine on one of the sites and everything worked just fine until I found this rather strange bug, where my content wouldn’t show up in search results, even if my target article clearly had the searched term (‘discounts’) in its title (I had 4 objects with the same ‘Crazy Discounts now’).

I was using rather classic search method, like this:

AND (md.name LIKE '%{$kw}%' 
OR pd.name LIKE '%{$kw}%' 
OR pd.short LIKE '%{$kw}%'

And 3 out of 4 queries worked just fine, but one didn’t. So I tried searching for ‘Discounts’ (capitalized) and suddenly got all 4 results.

So I tried casting my search term and table column names to lowercase and comparing that, but that didn’t work too. After a bit of testing I’ve found out that the nonworking query actually returned blob as the result (pd.name column), which is case sensitive, opposite of text, varchar and char data types. So all I did then was to cast blob type as regular string and suddenly everything worked:

AND (md.name LIKE '%{$kw}%' 
OR CONVERT(pd.name USING utf8) LIKE '%{$kw}%' 
OR pd.short LIKE '%{$kw}%'

Notice the CONVERT command on pd.name column.