SQL and .csv

Started by jacoblin, Dec 26, 2022, 04:33 AM

Previous topic - Next topic

jacoblinTopic starter


My task is to query a table in a database, select all fields from it, and save the rows of the table to a .csv file. I need to create a folder for storing the saved files.

Here are a few questions I have:

1. Where do I add the SELECT statement?
2. How do I insert table rows into a .csv file?
3. How do I put the .csv file into the appropriate folder?

Currently, I have a starting point in PHP, but I'm not sure if it's correct.

'localhost'// Server address
$db_name 'database'// Database name
$user 'user'// Username
$password 'password'// Password
    // Create a connection to the database
$connection mysqli_connect($host$user$password$db_name);
// SQL query text that will be passed to the database
// Execute a database query
$result mysqli_query($connection$query);

// Create an array to hold the data from the query result
$fields = [];

// Enter data from the database into the created array
foreach ($row $result->fetch_assoc()) {
$fields[] = $row;

// Open a file pointer for writing to a .csv file
$fp fopen('path/to/folder/table.csv''w');

// Output the received data to the .csv file
foreach ($fields as $field) {

// Close the file pointer and release the database connection



You can export table data from a MySQL database into a .csv file using the SELECT statement with the INTO OUTFILE option. This can be achieved in one request, without using any PHP code.

Here's the basic structure of the SQL query:

SELECT * FROM USERS INTO OUTFILE '/path/to/file.csv'
The SELECT statement retrieves all data from the USERS table, while the INTO OUTFILE clause specifies the path where the .csv file should be saved. To use this feature, you need permission to write files on your MySQL server and make sure that the path specified in the query exists and that you have write permissions for it.

You can find more information about the SELECT statement options in the MySQL reference documentation.