rseigel Posted September 11, 2015 Share Posted September 11, 2015 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 Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted September 11, 2015 Share Posted September 11, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
rseigel Posted September 11, 2015 Author Share Posted September 11, 2015 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. Quote Link to comment Share on other sites More sharing options...
rseigel Posted September 11, 2015 Author Share Posted September 11, 2015 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 Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted September 11, 2015 Share Posted September 11, 2015 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. Quote Link to comment Share on other sites More sharing options...
rseigel Posted September 12, 2015 Author Share Posted September 12, 2015 Yup...my host won't touch this one. Time for plan B.... Quote Link to comment Share on other sites More sharing options...
rseigel Posted September 12, 2015 Author Share Posted September 12, 2015 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..... Quote Link to comment Share on other sites More sharing options...
rseigel Posted September 12, 2015 Author Share Posted September 12, 2015 AHHHHHHHHHH..... False alarm. Doesn't work after all. I'll create a new thread to try to figure out how to make this work. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted September 12, 2015 Share Posted September 12, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
rseigel Posted September 12, 2015 Author Share Posted September 12, 2015 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"); } } Quote Link to comment Share on other sites More sharing options...
rseigel Posted September 12, 2015 Author Share Posted September 12, 2015 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 Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted September 12, 2015 Share Posted September 12, 2015 As you did last time, define \t as the delimiter when calling fputcsv. fputcsv($fp, $fields, "\t"); Quote Link to comment Share on other sites More sharing options...
rseigel Posted September 12, 2015 Author Share Posted September 12, 2015 LOL....I guess I figured it couldn't possibly be that easy. Turns out it was..... Thanks. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.