Tuesday, 1 January 2013

Insert data in to table from excel file in php


Step -1 :

Open excel file and save as "TAB DELIMITED" text file.
in below sample code, "example.txt" is tab-delimited excel file.

Step -2 : 

Now, follow the below instruction for create code for insert data into table in mysql using php.


<?php

include 'config.php';
// Connect to your database

$fp = fopen("example.txt", "r");    // Open the file('Tab Delimited') for reading

while($line = fgets($fp))      // Loop through each line
{
 
     list ($name, $first) = explode("\t", $line);

     // Split the line by the tab delimiter and store it in our list...

     $sql = "insert into user (name,phone) values ('$name', '$first')";

     // Generate sql string...

     mysql_query($sql) or die ( mysql_error() );        // Execute the sql

}

?>

Download/get data into excel file using php



<?php
//your code for to create your sql statement, we'll call it $query...

 $query="SELECT `candidate_id`, `fullname`,`email`,`alternate_email`, `location`, `mobile`, `landline`, `expyear`, `expmonth`, `keyskill`, `headlineprofile`, `currcmp`, `fuctionalarea`, `basicedu`, `masteredu`, `docedu`, `create_date`, `current_salary`, `current_industry`, `date_of_birth`, `gender`, `marital_status`, `address`,  `city`, `country`, `pincode`,`specialization`, `university`, `edu_year`, `role` FROM `candidate_reg`";


//get the data that we need...

$Result=mysql_query($query);


//fetching each row as an array and placing it into a holder array ($aData)

while($row = mysql_fetch_assoc($Result)){
 $aData[] = $row;
}


//feed the final array to our formatting function...

$contents = getExcelData($aData);

$filename = "myExcelFile.xls";


//code for Save/Open dialog box...

header ("Content-type: application/octet-stream");
header ("Content-Disposition: attachment; filename=".$filename);


//setting the cache expiration to 30 seconds ahead of current time. an IE 8 issue when opening the data directly in the browser without first saving it to a file

$expiredate = time() + 30;
$expireheader = "Expires: ".gmdate("D, d M Y G:i:s",$expiredate)." GMT";
header ($expireheader);


//final output in contents

echo $contents;
exit;
?>

<?php
 function getExcelData($data){
    $retval = "";
    if (is_array($data)  && !empty($data))
    {
     $row = 0;
     foreach(array_values($data) as $_data){
      if (is_array($_data) && !empty($_data))
      {
          if ($row == 0)
          {
              // write the column headers
              $retval = implode("\t",array_keys($_data));
              $retval .= "\n";
          }
           //create a line of values for this row...
              $retval .= implode("\t",array_values($_data));
              $retval .= "\n";
              //increment the row so we don't create headers all over again
              $row++;
       }
     }
    }
  return $retval;
 }
?>

Wednesday, 26 December 2012

Show/hide div on click event


Step-1 :

Use below javascript code with you div ID. like "myDivBox".


<script language="javascript" type="text/javascript">
       function toggleDiv() 
       { 
         if (document.getElementById("myDivBox").style.display == "block") 
         { 
           document.getElementById("myDivBox").style.display = "none"; 
         } 
         else 
         { 
           document.getElementById("myDivBox").style.display = "block"; 
         } 
       } 
</script>


Step-2 :

Add onclick() event on you button or whatever you use to show/hide div.
and make sure that..id of your div is same with javascript code use above.


<input type="button" id="button1" name="button1" OnClick="toggleDiv()"/>


    
    <div>
    
       <div id="myDivBox" style="display:none; width :300px">

              Content or your coding to hide in between this div.

       </div>

    </div>

Submit form on page load event


Step-1 :

Add below code into your <head> section. and change the form id as per your <form> tag.


<script type="text/javascript" language="javascript">

 function submitform(){

 document.getElementById('myForm').submit();

 }

</script>


Step-2 :


Add onload event in to body tag as per below.

<body onload="submitform()">

</body>

Friday, 31 August 2012

convert min into hours using php


for converting the min into Hours you can use the following PHP function.

step for using PHP function :
1) call the function min2hours with parameter total min
2) and print the return value.


function min2hours($mins) { 

            if ($mins < 0) { 
                $min = Abs($mins); 
            } else { 
                $min = $mins; 
            } 

            $H = Floor($min / 60); 
            $M = ($min - ($H * 60)) / 100; 
            $hours = $H +  $M; 

            if ($mins < 0) { 
                $hours = $hours * (-1); 
            } 

            $expl = explode(".", $hours); 
            $H = $expl[0]; 

            if (empty($expl[1])) { 
                $expl[1] = 00; 
            } 

            $M = $expl[1]; 
            if (strlen($M) < 2) { 
                $M = $M . 0; 
            } 

            $hours = $H . ":" . $M; 
            return $hours; 
    } 

OUTPUT :

<?php echo min2hour(120); ?>

-- 2:00