Jump to content

how i can i let user to download a data from query in mySQL


VanillaRose

Recommended Posts

Hi, I have a non-profit website that I've developed with PHP. I'm trying to make the logged in users can output letters in Word with selected data from a query in MySQL. I've tried to use PHPWord, the letters outputted with no data in the letters. Also I tried used PHP COM, but Hostforlife won't let me restart the server because it is shared. 

Lastly I wrote a SELECT query with INTO OUTFILE '$file' to create a CSV which the users could download and use to merge into Word, but because the database user does not have FILE access, it produces this error:

 

Error: Access denied for user 'blabla'@'localhost' (using password: YES)

 

I think Hostforlife will not allow FILE privileges on a shared server. So, how can I get around this. Somehow I want to be able to at least write the query and download the data. Javascript? jQuery? or Something else? Any of you can help me?

 

Thanks in advance :)

Link to comment
Share on other sites

This is the download function that I use.

function sql2csv($mysqli, $sql, $filename='', $headings=1)
        /**
        * Parameters
        * $mysqli   -   connection
        * $sql      -   the sql query to be executed
        * $filename -   name of download file (default "download_yymmddhhii.csv")
        * $headings -   1 if fieldname headings required (default), 0 if not required
        */
{
    if (!$filename)
        $f = 'download_' . date('ymdhi') . '.csv';
    else 
         $f = $filename;
    $fp = fopen('php://output', 'w');        // so you can fputcsv to STDOUT
    if ($fp) {
        $res = $mysqli->query($sql);
        if ($res) {
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename="'.$f.'"');
            header('Pragma: no-cache');
            header('Expires: 0');
            $row = $res->fetch_assoc();
            if ($headings) {
                fputcsv($fp, array_keys($row));
            }
            do {
                fputcsv($fp, $row);
            } while ($row = $res->fetch_assoc());
            
        }
        else echo "Error in query";
        fclose($fp);
    }
    
}

Use it in a php file that you link to with a download link.

Link to comment
Share on other sites

  • 2 months later...

apologies, this was my first function, I have the function working now, but still the excel file contains the echo's from the webpage + the data from the the query..

 

like so

 

<head>               <title>Newfies Page</title>             </head>                               <body>               <h2>Lets take a look at a table :)</h2>                                                           exporting!</br>created newfies table</br>prepared 1000 numbers7415354264   7428357984               7428748979               7500332817               7504051767               7504299246               7504822797               7505149835               7506677762               7506878021               7510330942               7510935466               7510941052               7511362543               7511732442               7511889806               7512215911               7512575544               7513832701              

 

Here is my code:

require('test.php');

sql2csv($db_mysqli, $sql, $filename='myNewfile.csv', $headings=0);

}
?>
<?php

$sql = 'SELECT * FROM newfies';

function sql2csv($db_mysqli, $sql, $filename='', $headings=0)

{
    if (!$filename)
        $f = 'download_' . date('ymdhi') . '.csv';
    else 
         $f = $filename;
    $fp = fopen('php://output', 'w');        // so you can fputcsv to STDOUT
    if ($fp) {
        $res = $db_mysqli->query($sql);
        if ($res) {
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename="'.$f.'"');
            header('Pragma: no-cache');
            header('Expires: 0');
            $row = $res->fetch_assoc();
            if ($headings) {
                fputcsv($fp, array_keys($row),'|');
            }
            do {
                fputcsv($fp, $row);
            } while ($row = $res->fetch_assoc());
            
        }
        else echo "Error in query";
        fclose($fp);
    }
    
}



?>
Link to comment
Share on other sites

File 1

<a href="file2.php">Download newfies</a>

File2.php (the download code goes in a file of its own)

    <?php
     
    $sql = 'SELECT * FROM newfies';
    sql2csv($db_mysqli, $sql, $filename='myNewfile.csv', $headings=0);

 
    function sql2csv($db_mysqli, $sql, $filename='', $headings=0)
     
    {
        if (!$filename)
            $f = 'download_' . date('ymdhi') . '.csv';
        else 
             $f = $filename;
        $fp = fopen('php://output', 'w');        // so you can fputcsv to STDOUT
        if ($fp) {
            $res = $db_mysqli->query($sql);
            if ($res) {
                header('Content-Type: text/csv');
                header('Content-Disposition: attachment; filename="'.$f.'"');
                header('Pragma: no-cache');
                header('Expires: 0');
                $row = $res->fetch_assoc();
                if ($headings) {
                    fputcsv($fp, array_keys($row),'|');
                }
                do {
                    fputcsv($fp, $row);
                } while ($row = $res->fetch_assoc());
                
            }
            else echo "Error in query";
            fclose($fp);
        }
        
    }
     
     
     
    ?>
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.