Export query result as SQL Insert Statement

To export specific rows from a MySQL table as SQL Insert Statements using PHP or Laravel, you can follow these steps. This solution involves querying the database, formatting the results into SQL Insert statements, and then writing them to a file.

Here's a simple example using Laravel:

1. Query the Database: Use Eloquent or the Query Builder to fetch the data you want to export.

2. Format the Data: Convert the query results into SQL Insert statements.

3. Write to a File: Save the formatted SQL statements to a file.

Here's a sample implementation:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

function exportToSqlFile($tableName, $conditions = [], $filePath = 'export.sql')
{
    // Step 1: Query the database
    $query = DB::table($tableName);

    // Apply conditions if any
    if (!empty($conditions)) {
        foreach ($conditions as $column => $value) {
            $query->where($column, $value);
        }
    }

    $rows = $query->get();

    // Step 2: Format the data into SQL Insert statements
    $sqlStatements = [];
    foreach ($rows as $row) {
        $columns = array_keys((array)$row);
        $values = array_map(function ($value) {
            return is_null($value) ? 'NULL' : "'" . addslashes($value) . "'";
        }, array_values((array)$row));

        $sql = sprintf(
            "INSERT INTO `%s` (%s) VALUES (%s);",
            $tableName,
            implode(', ', array_map(fn($col) => "`$col`", $columns)),
            implode(', ', $values)
        );

        $sqlStatements[] = $sql;
    }

    // Step 3: Write to a file
    File::put($filePath, implode("\n", $sqlStatements));

    return "Data exported to $filePath successfully.";
}

// Usage example
exportToSqlFile('your_table_name', ['column_name' => 'value'], 'path/to/your/export.sql');

Explanation:

• Query the Database: The DB::table($tableName) is used to create a query builder instance for the specified table. You can add conditions to filter the data as needed.

• Format the Data: Each row is converted into an SQL Insert statement. The addslashes function is used to escape special characters in the values.

• Write to a File: The File::put method writes the SQL statements to a specified file path.

This approach allows you to export data from a MySQL table into a SQL file with Insert statements using PHP and Laravel, without relying on command-line tools like mysqldump.