INTO OUTFILE issue

[rseigel]

I'm trying to run the following:

mysqli_query($con,"SELECT * FROM amazon INTO OUTFILE 'amazon.csv'");

It's running from a php file in the root directory.

 

Not sure why it won't create the csv file.

 

Any ideas?

 

Thanks,

 

Ron

[Ch0cu3r]

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 September 11, 2015 by Ch0cu3r

[rseigel]

Thanks for the tip in #2. Much appreciated.

 

After that change all is exactly as you have stated.

 

It still doesn't create the file. 

[rseigel]

I take it back....

 

There's no FILES option in my hosting.

 

I've emailed my host to see what they can do about this....

 

Thanks,

 

Ron

[Ch0cu3r]

Your host may not allow your mysql user to have FILES privileges. A work around is to have PHP create the csv file. Check out Barands post here using the sql2csv function.

[rseigel]

Yup...my host won't touch this one.

 

Time for plan B....

[rseigel]

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. 

 

Off to the next adventure.....

[rseigel]

AHHHHHHHHHH.....

 

False alarm. Doesn't work after all.

 

I'll create a new thread to try to figure out how to make this work.

[Ch0cu3r]

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 September 12, 2015 by Ch0cu3r

[rseigel]

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"); 
        } 
}

[rseigel]

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

[Ch0cu3r]

As you did last time, define \t as the delimiter when calling fputcsv.

fputcsv($fp, $fields, "\t");

[rseigel]

LOL....I guess I figured it couldn't possibly be that easy.

 

Turns out it was.....

 

Thanks.