Tuesday, July 12, 2016

Export data as XLS using PHP

How to export data as xls using php ?

Need to export database fields as xls file using php

Make sure you are well connected

Configure following to start a Mysql Connection

$DB_Server = DB_HOST; //MySQL Server
$DB_Username = DB_USERNAME; //MySQL Username
$DB_Password = DB_PASSWORD;             //MySQL Password
$DB_DBName = DB_DATABASE;         //MySQL Database Name
$DB_TBLName = 'tablename'; //MySQL Table Name
$filename =   'data'.time();       //File Name for xls

//connect to db

$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
    or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
    or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());

// Make Query with required field in xls ,dont include fields not required in xls

$sql ='  Select  field1, field2, field4, field6 from tablename' ;

//execute query
$result = @mysql_query($sql , $Connect)
    or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
$file_ending = "xls";


//header info for browser
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename.xls");
header("Pragma: no-cache");
header("Expires: 0");

/*******Start of Formatting for Excel*******/
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character


//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo ucfirst(mysql_field_name($result,$i)) . "\t";
}
print("\n");
//end of printing column names


//start while loop to fetch data
  
  while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
           
if(!isset($row[$j])){
                            $schema_insert .= "NULL".$sep;
}else{
   $schema_insert .= "$row[$j]".$sep;
}
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
  $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
       print(trim($schema_insert));
       print "\n";
    }

Thanks

That's All

Function to Mask Numbers in PHP

Here is function to mask numbers

//////Code

arguments
$phonenumber : number to mask
$trim = number of digit to show
$maskCharacter = character to be displayed on masked digits

function maskNumber($phonenumber, $trim, $maskCharacter)
{
$str="";
$suffixNumber = substr($phonenumber, strlen($phonenumber)-$trim,$trim);
$prefixNumber = substr($phonenumber, 0, -$trim);

for ($x = 0; $x < strlen($prefixNumber); $x++):
    $str.= ( is_numeric($prefixNumber[$x]) )? str_replace($prefixNumber[$x], $maskCharacter, $prefixNumber[$x]) : $prefixNumber[$x];
endfor;

return  $str.$suffixNumber;    
}


HOW TO USE

Suppose you have large database of Mobile number of any other database which has to partially shown on the site or documents.then you can mask the number or string to protect your data

Example

IF I have an array for which i need to show only last four digits

$telarray = array('7383204940','7383323653','7878389652','8000306647','8000658282');

I would call it as

for($i=0;$i<count($telarray);$i++){
echo $masked = maskNumber($telarray[$i],4,'X');
echo '<br />';
}

OUTPUT would be

XXXXXX4940
XXXXXX3653
XXXXXX9652
XXXXXX6647
XXXXXX8282

Thanks

Cheers