How to Import and Export CSV Files Using PHP and MySQL

PHP is widely used for building a wide range of products ranging from web apps to enterprise level applications. The key to efficient PHP code is to follow proper workflows and automate processes. The result is high quality and bug-free code.

In almost all PHP applications, data is stored, accessed and exchanged between various components of the app. To make sure that this exchange and access to data goes smoothly and without any issues, the development team must make sure that the databases and data dumps are in proper format.

Import and export of data to and from databases is a common enough procedure in PHP development. Another important activity is the backup and transfer of databases.

In this article, I will explain how to export csv file from mysql data table. You need to signup at Cloudways to launch a server and PHPstack application. Before signing up, looking at all the pricing options from the world-class hosting providers like AWS, DigitalOcean, Linode, Vultr and GCP is a good idea so you can find the one that perfectly fits your needs.

Create a Database in MySQL

The first step in this tutorial is the creation of a MySQL database. Since Cloudways provides the custom mysql manager in the platform which contains a database for app. you can create tables by running SQL queries. Create a table `employeeinfo` in the database using the following SQL query.

CREATE TABLE employeeinfo(
emp_id VARCHAR(50) UNSIGNED PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date VARCHAR(50)
)

This will create a new table `employeeinfo` in the database. I will use this table to insert data from the CSV file.

Create MySql Connection in PHP

For importing and exporting database in MySql will make a separate file `config.php`. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:

<?php
function getdb(){
$servername = "localhost";
$username = "huscqxzwaw";
$password = "2WWKxxxxHr";
$db = "huscqxzwaw";

try {
   
    $conn = mysqli_connect($servername, $username, $password, $db);
     //echo "Connected successfully"; 
    }
catch(exception $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
    return $conn;
}
?>

How To Connect MySQL Database With PHP Websites>

How to Import CSV to MySQL Using PHP?

After the database has been created, we next need an HTML file that could upload a CSV file. Let’s get started with simple steps to follow.

Step 1: Create a Database and Table
First, you need to create a database and a table in MySQL where you will store the CSV data. Here’s an example SQL query to create a table named employeeinfo:

CREATE TABLE employeeinfo (
    emp_id INT(11) NOT NULL,
    firstname VARCHAR(255) NOT NULL,
    lastname VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    reg_date DATE NOT NULL,
    PRIMARY KEY (emp_id)
);

Step 2: Design the Import Form
Now we need to create an HTML form to upload the CSV file. This form will allow users to select a CSV file from their computer and upload it to your server.

<form action="index.php" method="post" enctype="multipart/form-data">
    <input type="file" name="file" accept=".csv">
    <button type="submit" name="Import">Import CSV</button>
</form>

Step 3: Handle File Upload and Validation
In your PHP script, handle the file upload and validate that the uploaded file is a CSV file.

if (isset($_POST["Import"])) {
    $filename = $_FILES["file"]["tmp_name"];
    
    // Check if the file is a CSV file
    if (pathinfo($_FILES["file"]["name"], PATHINFO_EXTENSION) != "csv") {
        echo "Please upload a CSV file.";
        exit;
    }
    
    // Proceed with importing the CSV file
    importCSV($filename);
}

Step 4: Import CSV Data into MySQL
Create a function named importCSV() to handle the CSV import. This function will read the CSV file and insert its data into the MySQL database using prepared statements.

functions.php (Import Section)
Here’s how you can implement the importCSV() function securely:

function importCSV($filename) {
    // Get a secure database connection
    $conn = getConnection(); // Ensure this function returns a secure connection
    
    // Open the CSV file
    $file = fopen($filename, "r");
    
    // Skip the header row (if it exists)
    fgetcsv($file, 10000, ",");  // Adjust delimiter if needed
    
    // Prepare the SQL query
    $sql = "INSERT INTO employeeinfo (emp_id, firstname, lastname, email, reg_date) VALUES (?, ?, ?, ?, ?)";
    $stmt = $conn->prepare($sql);
    
    if ($stmt === false) {
        die("Error preparing statement: " . $conn->error); // Handle prepare error
    }
    
    // Bind parameters
    $stmt->bind_param("sssss", $emp_id, $firstname, $lastname, $email, $reg_date);
    
    while (($getData = fgetcsv($file, 10000, ",")) !== FALSE) {
        // Validate data (example validation)
        if (count($getData) != 5) {
            error_log("Invalid CSV row: " . implode(",", $getData));
            continue; // Skip to the next row
        }
        
        // Assign values
        $emp_id = $getData[0];
        $firstname = $getData[1];
        $lastname = $getData[2];
        $email = $getData[3];
        $reg_date = $getData[4];
        
        // Execute the query
        if (!$stmt->execute()) {
            error_log("Error inserting row: " . $stmt->error);
        }
    }
    
    // Close the statement and connection
    $stmt->close();
    fclose($file);
    $conn->close();
    
    echo "<script type=\"text/javascript\">
            alert(\"CSV File has been successfully Imported.\");
            window.location = \"index.php\"
          </script>";
}

// Example of a secure getConnection() function
function getConnection(): mysqli
{
    $servername = $_ENV["DB_HOST"] ?? "localhost"; // Default to localhost if not set
    $username = $_ENV["DB_USER"] ?? "your_db_user";   // Provide reasonable defaults
    $password = $_ENV["DB_PASSWORD"] ?? "your_db_password";
    $dbname = $_ENV["DB_NAME"] ?? "your_db_name";

    try {
        $conn = new mysqli($servername, $username, $password, $dbname);
        if ($conn->connect_error) {
            throw new Exception("Connection failed: " . $conn->connect_error);
        }
        return $conn;
    } catch (Exception $e) {
        error_log("Database connection error: " . $e->getMessage()); // Log the error
        die("Failed to connect to the database.  Check your configuration."); // Fatal error, stop execution
    }
}

Alternative: Using MySQL’s LOAD DATA INFILE

For large CSV files, consider using MySQL’s LOAD DATA INFILE command, which is much faster than processing the file line by line with PHP:

$loadSQL = "LOAD DATA LOCAL INFILE '$filename' INTO TABLE employeeinfo FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS";
mysqli_query($conn, $loadSQL);

This method requires the LOCAL keyword if you’re running the query from a remote server. Ensure that the MySQL user has the necessary permissions to use LOAD DATA INFILE.

Display the Saved Records

Once the CSV file has been imported, I will display the data through a simple function, `get_all_records()`, initialized in `index.php`. Copy this function to `function.php`.

function get_all_records(){
    $con = getdb();
    $Sql = "SELECT * FROM employeeinfo";
    $result = mysqli_query($con, $Sql);  


    if (mysqli_num_rows($result) > 0) {
     echo "<div class='table-responsive'><table id='myTable' class='table table-striped table-bordered'>
             <thead><tr><th>EMP ID</th>
                          <th>First Name</th>
                          <th>Last Name</th>
                          <th>Email</th>
                          <th>Registration Date</th>
                        </tr></thead><tbody>";


     while($row = mysqli_fetch_assoc($result)) {

         echo "<tr><td>" . $row['emp_id']."</td>
                   <td>" . $row['firstname']."</td>
                   <td>" . $row['lastname']."</td>
                   <td>" . $row['email']."</td>
                   <td>" . $row['reg_date']."</td></tr>";        
     }
    
     echo "</tbody></table></div>";
     
} else {
     echo "you have no records";
}
}

In this really simple method, I simply selected all the records and displayed these records on the index page through the method. Whenever the user uploads a CSV file, the records will get saved in the table and then displayed on the index page.

Export MySQL to CSV With PHP

Exporting data from MySQL database to a CSV file is similarly very easy. To demonstrate this, I will use the index.php that I created earlier.

Add the following code to the file.

 <div>
            <form class="form-horizontal" action="functions.php" method="post" name="upload_excel"   
                      enctype="multipart/form-data">
                  <div class="form-group">
                            <div class="col-md-4 col-md-offset-4">
                                <input type="submit" name="Export" class="btn btn-success" value="export to excel"/>
                            </div>
                   </div>                    
            </form>           
 </div>

After adding this HTML markup, the Export button will appear below the table. Now add the following condition in functions.php.

 if(isset($_POST["Export"])){
		 
      header('Content-Type: text/csv; charset=utf-8');  
      header('Content-Disposition: attachment; filename=data.csv');  
      $output = fopen("php://output", "w");  
      fputcsv($output, array('ID', 'First Name', 'Last Name', 'Email', 'Joining Date'));  
      $query = "SELECT * from employeeinfo ORDER BY emp_id DESC";  
      $result = mysqli_query($con, $query);  
      while($row = mysqli_fetch_assoc($result))  
      {  
           fputcsv($output, $row);  
      }  
      fclose($output);  
 }  

When the `Export` button is clicked, the headers `Content-Type: text/csv` with an attachement `data.csv` is sent.

Since `php://output` is a write-only stream that allows write access to the output buffer mechanism, I selected all data from table in the next line, and passed it to `fputcsv()` method. This method formats a line (passed as a fields array) as CSV and write it (terminated by a newline) to the specified file. Finally, the file with all the desired data is downloaded.

Finally, after integrating all the code, you will see the following final shape of application.

Conclusion

In this article, I discussed how you could export data from and to CSV files using PHP and MySQL. This is a simple example you can Add more complex logic and validations as per your requirements. You can also create test cases to verify the code and Integerate with GitHub using PHP Continuous Integeration Tools. If you wish to add to the discussion or would like to ask a question, leave a comment below and embark on your coding journey empowered by best PHP hosting service.

Shahzeb Ahmed

Shahzeb is a Digital Marketer with a Software Engineering background, works as a Community Manager — PHP Community at Cloudways. He is growth ambitious and aims to learn & share information about PHP & Laravel Development through practice and experimentation. He loves to travel and explore new ideas whenever he finds time. Get in touch with him at [email protected]

Get Connected on: Twitter Community Forum