export mysql query to excel in php

Question

lloydsbackyard 0 Light Poster

9 Years Ago

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 9 Years Ago Latest Post by urtrivedi

All 3 Replies

urtrivedi 276 Nearly a Posting Virtuoso

9 Years Ago

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;           
    }
    }
 ?>

Reply to this topic

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.

lloydsbackyard 0 Light Poster · Answer 1 · 2014-07-09T09:06:44+00:00

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..

urtrivedi 276 Nearly a Posting Virtuoso · Answer 2 · 2014-07-09T09:38:02+00:00

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;           
        }
    }
 ?>

export mysql query to excel in php

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers