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:


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 ( LIKE '%{$kw}%' 
OR 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 ( 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 ( LIKE '%{$kw}%' 
OR CONVERT( USING utf8) LIKE '%{$kw}%' 
OR pd.short LIKE '%{$kw}%'

Notice the CONVERT command on column.