Unable to download/process sql to excel using PHP

Question

I got this weird issue. I want to run a script which fetches data from a table and download to excel format using a php script. It runs OK on local machine but does not run on the linux server. I tried on 2 different servers but none worked. Here is the code.

      <?php 
    include "config.php";

$count = 0;

$sqlquery = "select * from contact_us" ;
$result = mysql_query($sqlquery) or die(mysql_error());  
$count = mysql_num_fields($result);

for ($i = 0; $i < $count; $i++) {
    $header .= mysql_field_name($result, $i)."\t";
}

while($row = mysql_fetch_row($result))  {
  $line = '';
  foreach($row as $value)   {
    if(!isset($value) || $value == "")  {
      $value = "\t";
    }   else  {
# important to escape any quotes to preserve them in the data.
      $value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
      $value = '"' . $value . '"' . "\t";
    }
    $line .= $value;
  }
  $data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
  $data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
  $data = "\nno matching records found\n";
}

$count = mysql_num_fields($result);



# This line will stream the file to the user rather than spray it across the screen
 header("Content-type: application/vnd.ms-excel");
//header("Content-type: text/plain");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");

header("Pragma: no-cache");
header("Expires: 0");

//echo $header."\n".$data;
echo $header."\n".$data."\n";
?>

Another code i tried but shows the value instead of downloading to excel format

    <?php 
    include "config.php";

header('Content-Type: application/vnd.ms-excel');
header('Content-disposition: attachment; filename="MasterEntryList.xls"');
    ?>   
    <?php


            $i=0;
        if(isset($_POST['dsubmit']))
            {
                $pro=$_POST['project'];
                if(($_POST['fromdate']=="") || ($_POST['todate']=="")) {
                $fromdate="";
                $todate="";
                $date = "";
                } else {
                $fromdate=$_POST['fromdate'];
                $todate=$_POST['todate'];
                $date = "yes";
                }
//Start Single Condition                
                if( ($pro!="") && ($date=="") )
                {
                 $cond="WHERE project_ LIKE '$pro'";
                }else 
                if(($pro=="") && ($date!="") )
                {
                 $cond="WHERE  (date_ BETWEEN '$fromdate' AND '$todate')";
                }else
                if( ($pro!="") && ($date!="") )
                {
                 $cond="WHERE  project_ LIKE '$pro' AND (date_ BETWEEN '$fromdate' AND '$todate')";
                }else
             if( ($pro=="") && ($date=="") )
                {
                 $cond=" ";
                }
                $sql="SELECT * FROM contact_us {$cond}";
            $query=mysql_query($sql);
             }
            else
            {
            $query=mysql_query("SELECT * FROM contact_us order by id DESC Limit 100 ");
            }

        ?>

                           <table class="table table-striped table-bordered table-hover" id="dataTables-example" border="1">
                                    <thead>
                                        <tr>
                                             <th> Sr.No.</th>

                                             <th>Client Name</th>
                                              <th>Country Code</th>
                                             <th>Contact</th>
                                             <th>City</th>
                                                <th>UTM Campaign</th>
                                              <th>UTM Source</th>
                                             <th>UTM medium</th>
                                             <th>Date</th>


                                          </tr>
                                    </thead>
                                    <tbody>
                                    <?php 
                                    $follow=date("Y-m-d");

                                     while( $result_data=mysql_fetch_assoc($query) )
                                    {       
                                    $i++;
                                    ?> 

                                          <tr>
                                            <td><?php  echo $i; ?></td>

                                            <td><?php if($result_data['name_']==""){echo "NA";}else {echo $result_data['name_'];}?> </td>
                                            <td><?php if($result_data['country_']==""){echo "00";}else {echo $result_data['country_'];}?></td> 
                                            <td><?php if($result_data['number_']==""){echo "0000000000";}else {echo $result_data['number_'];}?></td>                                            <td><?php if($result_data['city_']==""){echo "NA";}else {echo $result_data['city_'];}?></td> 
                                                      <td><?php if($result_data['utm_campaign']==""){echo "NA";}else {echo $result_data['utm_campaign'];}?></td>
                                              <td><?php if($result_data['utm_source']==""){echo "NA";}else {echo $result_data['utm_source'];}?></td>


                                              <td><?php if($result_data['utm_medium']==""){echo "NA";}else {echo $result_data['utm_medium'];}?></td>

                                          <td><?php if($result_data['date_']==""){echo "0000-00-00";}else {echo $result_data['date_'];}?></td>


                                        </tr>
                                                <?php   }    ?>                                     

                                    </tbody>
                                </table>

Akshay Hegde · Accepted Answer · 2015-06-08 12:31:23Z

This may help you

Please use mysqli_* functions since mysql_* functions are old now.

function export_to_excel($array, $filename="test.xls", $delim = "\t", $newline = "\n", $enclosure = '"' )
 {
       $string_to_export = "";
       $c = 0;
       foreach( $array as $row)
       {
          // Header
          if(!$c)
          { 
             $string_to_export .= $enclosure.implode($enclosure.$delim.$enclosure, array_keys($row) ).$enclosure.$newline;
          }
             $string_to_export .= $enclosure.implode($enclosure.$delim.$enclosure, array_values($row) ).$enclosure.$newline;

          $c++;
       }

       header('Content-type: application/vnd.ms-excel;charset=utf-8');
       header('Content-Disposition: attachment; filename='.$filename);
       header("Cache-Control: no-cache");
       echo $string_to_export;
       die();
 }

$link = mysqli_connect("host", "user", "password", "db");

if (mysqli_connect_errno())
{
 printf("Connect failed: %s\n", mysqli_connect_error());
 exit();
}

$sqlquery = "select * from contact_us" ;

if ($result = mysqli_query($link, $sqlquery))
{
   // php 5.3 + if you have then
   // use mysqli_fetch_all function otherwise mysqli_fetch_assoc

   while ($row = mysqli_fetch_assoc($result))
   {
        $array[] = $row;
   }    
    export_to_excel($array);
}

Collectives™ on Stack Overflow

Unable to download/process sql to excel using PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related