Monday, December 2, 2013

Php Spreadsheet_Excel_Writer breaks header in excel files

RESOLVED: Corrupted Files: Excel found unreadable content...

  After generating xsl file with Pear Spread sheet , if you are getting error like this " Corrupted Files: Excel found unreadable content... ". 
   

The solution is change in Root.php \ line 623 :
fwrite($FILE, pack("V", 1));
to
 fwrite($FILE, pack("V", $num_sb_blocks));
 to be consistent with prĂ©vious RC : Root.php \ line 256 : .
pack("V", $iSBDcnt)



Monday, November 25, 2013

MySQL Cast NULL to integer 0 in select query

 MySQL Cast NULL to integer 0 in select query

How to cast a null value to 0 in MySql select  ?
     SELECT IF(column IS NULL ,0, column) FROM table;

Mysql select fetch only numeric or non-numeric values


Mysql select fetch only numeric or non-numeric values

Mysql Query to fetch only integer values from a particular column
     SELECT column FROM TABLE where column NOT REGEXP '^[0-9]+$' ;
Mysql Query to fetch only string values from a particular column
     SELECT column FROM TABLE where  column REGEXP '^[0-9]+$' ;

Wednesday, November 20, 2013

Mysql Updating a record with a value from the previous records

Mysql Updating a record with a value from the previous records
UPDATE refs_table t1
  JOIN (
    SELECT RefID, @s:=IF(Status IS NULL, @s, Status) Status
      FROM (SELECT * FROM refs_table ORDER BY RefID) r,
    (SELECT @s:=NULL) t
  ) t2
  ON t1.RefID = t2.RefID
SET t1.Status = t2.Status


An example to update total hits of users on specific date with prevous day.

UPDATE  USERS_HITS a LEFT JOIN ( SELECT USER_ID, HITS
  FROM USERS_HITS where DATE ='2013-11-13' ) b
ON (a.USER_ID = b.USER_ID) SET a.HITS = (a.HITS - b.HITS) 
WHERE a.DATE ='2013-11-14'

Tuesday, October 8, 2013

Mysql update table column with value from another table

Mysql update table column with value from another table
 
UPDATE  table1 a
        INNER JOIN table2 b
            ON a.id= b.id
SET     a.value = b.value
WHERE 
   [COND EXRESSION]