Laravel实现点击按钮将数据从一张表迁移至另一张表

Hey there! Let's walk through exactly how to build this feature—clicking a button to move a row from your cd table to a target table, then deleting it from the original. I'll tie this directly to the query code you shared, and fix a couple small syntax issues I noticed along the way.

Step 1: Fix Your Existing Query (Minor Syntax Tweaks) #

First, let's clean up the query you provided—there are a couple typos that'll cause errors:

// Corrected version
$user_input = $request->userInput; // Added missing semicolon
$scores = DB::table('cd')
    ->join('customers', 'cd.fk_lend_id', '=', 'customers.lend_id') // Fixed "customer .lend_id" to "customers.lend_id"
    ->select('cd.fk_lend_id','cd.serialnumber','customers.name', 'cd.created_at as lend_on') // Fixed "lend on" to "lend_on" for valid column alias
    ->where('cd.fk_lend_id', $request->userInput) // Removed extra $ before userInput
    ->get();

Step 2: Add a Migrate Button to Your Frontend Table #

In your Blade template where you're displaying the $scores data, add a form with a button for each row. This form will send a POST request to trigger the migration:

<table>
    <thead>
        <tr>
            <th>Lend ID</th>
            <th>Serial Number</th>
            <th>Customer Name</th>
            <th>Lend Date</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody>
        @foreach($scores as $score)
        <tr>
            <td>{{ $score->fk_lend_id }}</td>
            <td>{{ $score->serialnumber }}</td>
            <td>{{ $score->name }}</td>
            <td>{{ $score->lend_on->format('Y-m-d H:i:s') }}</td>
            <td>
                <form action="{{ route('migrate.row') }}" method="POST" onsubmit="return confirm('Are you sure you want to migrate this row?')">
                    @csrf <!-- Required for Laravel CSRF protection -->
                    <input type="hidden" name="lend_id" value="{{ $score->fk_lend_id }}">
                    <button type="submit" class="btn btn-primary">Migrate Row</button>
                </form>
            </td>
        </tr>
        @endforeach
    </tbody>
</table>

Step 3: Define a Route for the Migration #

Add this to your routes/web.php file to map the form's POST request to a controller method:

use App\Http\Controllers\YourController; // Replace with your actual controller name

Route::post('/migrate-row', [YourController::class, 'migrateRow'])->name('migrate.row');

Step 4: Build the Migration Logic in Your Controller #

This is the core part—we'll use a database transaction to ensure the move is atomic (either both the insert and delete succeed, or neither does, preventing data loss). Add this method to your controller:

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

public function migrateRow(Request $request)
{
    // Validate the incoming lend ID to make sure it exists in the cd table
    $validated = $request->validate([
        'lend_id' => 'required|integer|exists:cd,fk_lend_id',
    ]);

    // Start a database transaction
    DB::beginTransaction();

    try {
        // Fetch the exact row we need to migrate (matches your original query)
        $rowToMigrate = DB::table('cd')
            ->join('customers', 'cd.fk_lend_id', '=', 'customers.lend_id')
            ->select('cd.fk_lend_id', 'cd.serialnumber', 'customers.name', 'cd.created_at as lend_on')
            ->where('cd.fk_lend_id', $validated['lend_id'])
            ->first();

        // Insert the row into your target table (replace 'target_table' with your actual table name)
        // Adjust the columns to match your target table's schema
        DB::table('target_table')->insert([
            'fk_lend_id' => $rowToMigrate->fk_lend_id,
            'serialnumber' => $rowToMigrate->serialnumber,
            'customer_name' => $rowToMigrate->name,
            'lend_on' => $rowToMigrate->lend_on,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        // Delete the row from the original cd table
        DB::table('cd')->where('fk_lend_id', $validated['lend_id'])->delete();

        // If everything worked, commit the transaction
        DB::commit();

        // Redirect back with a success message
        return back()->with('success', 'Row migrated successfully!');
    } catch (\Exception $e) {
        // If something went wrong, roll back all changes
        DB::rollBack();

        // Redirect back with an error message
        return back()->with('error', "Migration failed: {$e->getMessage()}");
    }
}

Key Notes to Keep in Mind #

• Transaction Safety: Using DB::beginTransaction(), commit(), and rollBack() ensures that if the insert fails, the delete won't happen (and vice versa)—no orphaned data!
• Target Table Schema: Make sure the columns in the insert() method match exactly what's in your target table. Adjust names and data types as needed.
• CSRF Protection: The @csrf directive in the form is mandatory for Laravel POST requests—don't skip it!
• Confirmation Prompt: The onsubmit="return confirm(...)" adds a quick sanity check before the user triggers the migration.

内容的提问来源于stack exchange，提问作者Devi