Jump to content

INTO OUTFILE issue


rseigel

Recommended Posts

1) Make sure the user you are using to connect to MySQL has FILES privileges

 

2) You need to specify the path for where you want the file be created. If you do not specify the path it will save the file in mysql's data directory (set by the  datadir  directive in mysql's my.ini config file).

$dir = $_SERVER['DOCUMENT_ROOT'];
mysqli_query($con,"SELECT * FROM amazon INTO OUTFILE '$dir/amazon.csv'");

3) Set the necessary file permissions on the directory you are writing the csv file to. Otherwise mysql wont be able to create the file.

Edited by Ch0cu3r
Link to comment
Share on other sites

Just to complete this...

 

Here's the code I used to create the csv file:

$result = mysqli_query($con,"SELECT * FROM amazon'");
$num_fields = mysqli_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++)
{
    $headers[] = mysqli_fetch_field_direct($result, $i)->name;
}
$fp = fopen('php://output', 'w');
if ($fp && $result)
{     
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="amazon.csv"');
    header('Pragma: no-cache');    
    header('Expires: 0');
    fputcsv($fp, $headers); 
    while ($row = mysqli_fetch_row($result)) 
        {
            fputcsv($fp, array_values($row)); 
        } 
}

No idea if this is the correct way to do it or not but it works.  :birthday:

 

Off to the next adventure.....

Link to comment
Share on other sites

You have a stray single quote in your query, this will cause an SQL error

$result = mysqli_query($con,"SELECT * FROM amazon'");

 

By the way the code provided by Barand forces the file to be downloaded. If you want the file to be saved to the server. Then delete the header() lines and replace  $fp = fopen('php://output', 'w');  to be

 $fp = fopen('amazon.csv', 'w');

Edited by Ch0cu3r
Link to comment
Share on other sites

PERFECT! Thank you!

 

Here's the updated code. I needed it to be tab delimited so I added that as well.

$result = mysqli_query($con,"SELECT * FROM amazon");
$num_fields = mysqli_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++)
{
    $headers[] = mysqli_fetch_field_direct($result, $i)->name;
}
$fp = fopen('amazon.csv', 'w');
if ($fp && $result)
{     
    fputcsv($fp, $headers, "\t"); 
    while ($row = mysqli_fetch_row($result)) 
        {
            fputcsv($fp, array_values($row), "\t"); 
        } 
}
Link to comment
Share on other sites

One last thing and this code is complete.

 

I'm trying to add a header above the field names in the csv.

$result = mysqli_query($con,"SELECT * FROM amazon");
$num_fields = mysqli_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++)
{
    $headers[] = mysqli_fetch_field_direct($result, $i)->name;
}
$fp = fopen('amazon.csv', 'w');
if ($fp && $result)
{
    
    $list = array (
        array('TemplateType=Offer', 'Version=2014.0703')
    );
    
    foreach ($list as $fields) {
        fputcsv($fp, $fields);
    }
    
    fputcsv($fp, $headers, "\t");    
    
    while ($row = mysqli_fetch_row($result))
        {
            fputcsv($fp, array_values($row), "\t");
        }
}

Problem is I need a Tab separating the fields - not a comma.

 

So:

array('TemplateType=Offer', 'Version=2014.0703')

needs to be something else.

 

Maybe I'm overcomplicating this?

 

Any ideas?

 

Thanks

 

Ron

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.