export database table php

Exporting database tables is a common task for developers who work with databases. In this blog, we will discuss how to export database tables using PHP.

PHP provides several functions for exporting database tables. We will use the MySQLi extension in PHP to export database tables.

Step 1: Connect to the database
Before exporting database tables, you need to connect to the database. Here is an example code for connecting to a MySQL database using PHP:

<?php

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

Step 2: Export database tables
After connecting to the database, you can export database tables. Here is an example code for exporting a database table using PHP:

<?php

$table_name = "table_name";

// Execute SQL query
$sql = "SELECT * FROM $table_name";
$result = $conn->query($sql);

// Export data to CSV file
$filename = $table_name . ".csv";
$fp = fopen('php://output', 'w');
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        fputcsv($fp, $row);
    }
}
fclose($fp);

// Download CSV file
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
readfile($filename);
exit;

In this code, we first execute an SQL query to select all data from the table. Then we export the data to a CSV file using the fputcsv() function. Finally, we download the CSV file using the header() function.

Step 3: Export multiple tables To export multiple tables, you can modify the code to loop through each table and export its data to a CSV file. Here is an example code for exporting multiple tables using PHP:

<?php

$tables = array("table1", "table2", "table3");

foreach ($tables as $table_name) {
    // Execute SQL query
    $sql = "SELECT * FROM $table_name";
    $result = $conn->query($sql);

    // Export data to CSV file
    $filename = $table_name . ".csv";
    $fp = fopen('php://output', 'w');
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            fputcsv($fp, $row);
        }
    }
    fclose($fp);

    // Download CSV file
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '";');
    readfile($filename);
    exit;
}

In this code, we define an array of table names and loop through each table to export its data to a CSV file. We also download each CSV file using the header() function.

Conclusion Exporting database tables using PHP is a straightforward process. By following the steps outlined in this blog, you can easily export database tables and download them in CSV format.