Member Avatar for lloydsbackyard

I have this code below for export mysql database to excel..however, if i run this it will only export the html code of the page not the sql query..please help..thank you.

<html>
     <head><title>Exports</title>           
     <link rel="stylesheet" href="css/css.css" />
      <h2>EXPORT DATE/YEAR</h2></head>
<form action="" method="post">
    <table>
        <tr>
            <td><input size="40"  name="searchString" type="text"class="ed"  />
                <select name="by" class="ed">
                        <option>Date</option>
                        <option>Year</option>
                </select>
                <input type="submit" name="export" value="Export" id="button2" /></td>
        </tr>
    </table>
</form>


 <?php
  if(isset($_POST['export']))
    {
         //sanitize the input
                function clean($str)
                {
                        $str = @trim($str);
                        if(get_magic_quotes_gpc())
                                {
                                $str = stripslashes($str);
                                }
                        return mysql_real_escape_string($str);
                }
        $searchString = strtoupper(clean($_POST['searchString']));
        $by=clean($_POST['by']);


        if ($by=='Date')
        {
        $host="localhost";
        $uname="root";
        $pass="Unknown";
        $database = "dcde"; 

        $connection=mysql_connect($host,$uname,$pass); 
        echo mysql_error();
        //or die("Database Connection Failed");
        $selectdb=mysql_select_db($database) or 
        die("Database could not be selected"); 
        $result=mysql_select_db($database)
        or die("database cannot be selected <br>");

        // Fetch Record from Database
        $output = "";
        $table = "po"; // Enter Your Table Name 
        $sql = "Select poNo,date,supplier,tAmount,code from $table WHERE  date like '%$searchString%'";
        $columns_total = mysql_num_fields($sql);
        // Get The Field Name
        for ($i = 0; $i < $columns_total; $i++) {
        $heading = mysql_field_name($sql, $i);
        $output .= '"'.$heading.'",';
        }
        $output .="\n";
        // Get Records from the table
        while ($row = mysql_fetch_array($sql)) {
        for ($i = 0; $i < $columns_total; $i++) {
        $output .='"'.$row["$i"].'",';
        }
        $output .="\n";
        }
        // Download the file
        $filename = "myfile.csv";
        header('Content-type: application/csv');
        header('Content-Disposition: attachment; filename='.$filename);
        echo $output;
        exit;           
    }
    }
 ?>
  • 2 Contributors
  • 3 Replies
  • 1K Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for urtrivedi

One way is to
keep 2 separate files
one part line number 1-16
1) export.html

<html>
     <head><title>Exports</title>           
     <link rel="stylesheet" href="css/css.css" />
      <h2>EXPORT DATE/YEAR</h2></head>
<form action="export.php" method="post" target='_blank'>
    <table>
        <tr>
            <td><input size="40"  name="searchString" type="text"class="ed"  />
                <select name="by" class="ed">
                        <option>Date</option>
                        <option>Year</option>
                </select>
                <input type="submit" name="export" value="Export" id="button2" /></td>
        </tr>
    </table>
</form>

</html>

2) export.php (dont keep any blank lines before <?php or after ?>

<?php
  if(isset($_POST['export']))
    {
         //sanitize the input
                function clean($str)
                {
                        $str = @trim($str);
                        if(get_magic_quotes_gpc())
                                {
                                $str = stripslashes($str);
                                }
                        return mysql_real_escape_string($str);
                }
        $searchString = strtoupper(clean($_POST['searchString']));
        $by=clean($_POST['by']);


        if ($by=='Date')
        {
        $host="localhost";
        $uname="root";
        $pass="Unknown";
        $database = "dcde"; 

        $connection=mysql_connect($host,$uname,$pass); 
        echo mysql_error();
        //or die("Database Connection Failed");
        $selectdb=mysql_select_db($database) or 
        die("Database could not be selected"); 
        $result=mysql_select_db($database)
        or die("database cannot be selected <br>");

        // Fetch Record from Database
        $output = "";
        $table = "po"; // Enter Your Table Name 
        $sql = "Select poNo,date,supplier,tAmount,code from $table WHERE  date like '%$searchString%'";
        $columns_total = mysql_num_fields($sql);
        // Get The Field Name
        for ($i = 0; $i < $columns_total; $i++) {
        $heading = mysql_field_name($sql, $i);
        $output .= '"'.$heading.'",';
        }
        $output .="\n";
        // Get Records from the table
        while ($row = mysql_fetch_array($sql)) {
        for ($i = 0; $i < $columns_total; $i++) {
        $output .='"'.$row["$i"].'",';
        }
        $output .="\n";
        }
        // Download the file
        $filename = "myfile.csv";
        header('Content-type: application/csv');
        header('Content-Disposition: attachment; filename='.$filename);
        echo $output;
        exit;           
    }
    }
 ?>
Member Avatar for lloydsbackyard

i tried to revised based on the code above.with 2 separate files.no blank lines before <?php or after ?>.but i think what will come in the xcel file is the html code of the new blank window that after clicking export button. thanks..

Edited by lloydsbackyard
Member Avatar for urtrivedi

Change export.php as following

Problem is in your clean function, it expects database connection. so I have put connection first then function then rest of code.

<?php
    $host="localhost";
    $uname="root";
    $pass="Unknown";
    $database = "dcde"; 
    $connection=mysql_connect($host,$uname,$pass); 

    function clean($str)
    {
        $str = @trim($str);
        if(get_magic_quotes_gpc())
                {
                $str = stripslashes($str);
                }
        return mysql_real_escape_string($str);
    }

    if(isset($_POST['export']))
    {
         //sanitize the input
        $searchString = strtoupper(clean($_POST['searchString']));
        $by=clean($_POST['by']);
        if ($by=='Date')
        {
            echo mysql_error();
            //or die("Database Connection Failed");
            $selectdb=mysql_select_db($database) or 
                die("Database could not be selected"); 
            $result=mysql_select_db($database)
                or die("database cannot be selected <br>");
            // Fetch Record from Database
            $output = "";
            $table = "po"; // Enter Your Table Name 
            $sql = "Select poNo,date,supplier,tAmount,code from $table WHERE  date like '%$searchString%'";
            $columns_total = mysql_num_fields($sql);
            // Get The Field Name
            for ($i = 0; $i < $columns_total; $i++) {
                $heading = mysql_field_name($sql, $i);
                $output .= '"'.$heading.'",';
            }
            $output .="\n";
            // Get Records from the table
            while ($row = mysql_fetch_array($sql)) {
                for ($i = 0; $i < $columns_total; $i++) {
                   $output .='"'.$row["$i"].'",';
                }
                $output .="\n";
            }
            // Download the file
            $filename = "myfile.csv";
            header('Content-type: application/csv');
            header('Content-Disposition: attachment; filename='.$filename);
            echo $output;
            exit;           
        }
    }
 ?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.