PHP While循环仅执行一次及动态多表查询问题求助

Hey there! Let's break down your problem step by step and tackle both your core concerns: handling the dynamic table lookups (since pre-joining isn't feasible here) and fixing that stubborn one-time while loop.

You're totally right that pre-joining won't work here—since the target table is determined per row after your initial query, we need a dynamic approach. Here are a few practical options depending on your environment:

1. Dynamic SQL in a Stored Procedure #

This is the most common database-side solution. You'll first fetch your 200 records, then loop through each to build and execute the appropriate query. Critical note: Always validate table names to avoid SQL injection risks.

Example (SQL Server):

-- Create a temp table to hold your initial 200 records
CREATE TABLE #InitialRecords (
    ID INT PRIMARY KEY,
    TargetTable NVARCHAR(50), -- Column that decides which table to query
    -- Add other columns from your source table here
);

-- Insert your 200 records
INSERT INTO #InitialRecords
SELECT TOP 200 ID, TargetTable, OtherColumns
FROM YourSourceTable;

-- Declare variables for looping
DECLARE @ID INT, @TargetTable NVARCHAR(50), @SQL NVARCHAR(MAX);

-- Use a cursor to iterate through records
DECLARE recordCursor CURSOR FOR
SELECT ID, TargetTable FROM #InitialRecords;

OPEN recordCursor;
FETCH NEXT FROM recordCursor INTO @ID, @TargetTable;

-- Loop through each record, validate target table first
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Only allow the 5 approved tables (security check!)
    IF @TargetTable IN ('TableA', 'TableB', 'TableC', 'TableD', 'TableE')
    BEGIN
        -- Build your dynamic query (use QUOTENAME to avoid syntax issues)
        SET @SQL = N'
            SELECT ir.*, dt.*
            FROM #InitialRecords ir
            JOIN ' + QUOTENAME(@TargetTable) + N' dt ON ir.ID = dt.LinkingID
            WHERE ir.ID = ' + CAST(@ID AS NVARCHAR(10));
        
        -- Execute the query (you can insert results into another temp table if needed)
        EXEC sp_executesql @SQL;
    END
    ELSE
    BEGIN
        -- Handle invalid table names (log error, skip, etc.)
        PRINT 'Skipping invalid target table: ' + @TargetTable;
    END

    -- Don't forget to fetch the next record!
    FETCH NEXT FROM recordCursor INTO @ID, @TargetTable;
END

-- Clean up
CLOSE recordCursor;
DEALLOCATE recordCursor;
DROP TABLE #InitialRecords;

2. Scripting Language (Python/PowerShell) #

If you're comfortable with external scripts, this can be more flexible and easier to debug. The workflow is straightforward:

• Fetch the 200 records from your source table.
• Loop through each record in the script.
• Run the corresponding SELECT query based on the TargetTable value.
• Aggregate or export results as needed.

Example snippet (Python with pyodbc):

import pyodbc

# Connect to your database
conn = pyodbc.connect('your_connection_string_here')
cursor = conn.cursor()

# Fetch initial 200 records
cursor.execute("SELECT TOP 200 ID, TargetTable FROM YourSourceTable")
initial_records = cursor.fetchall()

# Define allowed tables to prevent invalid queries
allowed_tables = {'TableA', 'TableB', 'TableC', 'TableD', 'TableE'}

for record in initial_records:
    record_id, target_table = record
    if target_table not in allowed_tables:
        print(f"Skipping invalid table: {target_table}")
        continue
    
    # Run the dynamic query (use parameterized queries for security!)
    query = f"SELECT * FROM {target_table} WHERE LinkingID = ?"
    cursor.execute(query, (record_id,))
    results = cursor.fetchall()
    
    # Process results (print, save to file, etc.)
    print(f"\nResults for record {record_id} from {target_table}:")
    for row in results:
        print(row)

conn.close()

This almost always boils down to a small oversight in your loop logic. Here are the most common fixes:

• Cursor-based loops: You forgot to call FETCH NEXT at the end of the loop. Without this, the cursor stays on the first record, and the loop condition fails after the first iteration (like in the SQL Server example above, we explicitly fetch the next record before closing the loop).
• Counter-based loops: You aren't incrementing/decrementing your counter variable inside the loop. For example:

  DECLARE @Counter INT = 1;
  DECLARE @TotalRecords INT = (SELECT COUNT(*) FROM #InitialRecords);
  
  WHILE @Counter <= @TotalRecords
  BEGIN
      -- Your loop logic here
      
      -- Critical: Update the counter!
      SET @Counter = @Counter + 1;
  END
  

• Early exits: Check for accidental BREAK or RETURN statements inside the loop that are cutting execution short.
• Verify your result set: Double-check that your initial query actually returns 200 records—if it only returns 1, the loop will naturally run once.

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