PHP脚本重构：新增SELECT查询整合至INSERT语句的实现方案

我有一个可正常运行的PHP脚本，目前通过SQL查询从不同表拉取数据，实现单表的INSERT/UPDATE操作。重构后已经简化为单SQL连接，但现在需要新增一条SELECT查询并整合到现有INSERT逻辑中。

现有$data是能正常工作的SELECT查询，$stmt是对应的INSERT语句；新增的$data3查询能返回正确结果，但因为共用同一连接，不知道怎么整合到现有INSERT里。另外，需要基于用户ID关联插入：目标表ambition_totals的ext_id字段要和$data3中的c.user匹配，也就是实现按c.user = ext_id的条件插入。

需要解决两个问题：

1. 如何修改新查询与INSERT语句，实现用单个INSERT整合两个查询的所有值；
2. 需要添加何种约束实现基于用户ID的关联插入。

相关代码如下：

// 现有可正常运行的查询，其值可通过下方$stmt成功插入
$data = mysqli_query($conn2, "SELECT distinct case when callingpartyno in (select extension from ambition.ambition_users) then callingpartyno when finallycalledpartyno in (select extension from ambition.ambition_users) then finallycalledpartyno end as extension , sum(duration) as total_talk_time_seconds , round(sum(duration) / 60,2) as total_talk_time_minutes , sum(if(legtype1 = 1,1,0)) as total_outbound , sum( case when(legtype1 = 1 and duration > 60) then 1 else 0 end) as credit_for_outbound , sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound , sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed , sum(if(legtype1 = 1, 1, 0)) + -- outbound calls sum(if(legtype1 = 2, 1, 0)) as total_calls , round((sum(if(legtype1 = 2,1,0) and answered = 1))/(sum(if(legtype1 = 1, 1, 0)) + -- outbound calls sum(if(legtype1 = 2, 1, 0))) * 100,2) as percent_answered , now() as time_of_report , curdate() as date_of_report FROM cdrdb.session a join cdrdb.callsummary b on a.notablecallid = b.notablecallid where date(a.ts) >= curdate() and ( callingpartyno in (select extension from ambition.ambition_users) or finallycalledpartyno in (select extension from ambition.ambition_users) ) group by extension") or die(mysqli_error( $conn2));

// 新增的SELECT查询，从不同schema（同服务器/连接）获取2个新值
$data3 = mysqli_query($conn2, "SELECT COUNT(*) AS Number_of_recorded_events, ROUND((al.NumberOfDealers / al.NumberOfDealerContacts) * 100 ,2) AS Percentage_up_to_date FROM jfi_dealers.contact_events c JOIN jackson_id.users u ON c.user = u.id JOIN jfi_dealers.dealers d ON c.dealer_num = d.dealer_num LEFT JOIN ( SELECT user_id, COUNT(*) AS NumberOfDealerContacts, SUM(CASE WHEN ( d.next_call_date + INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS NumberOfDealers FROM jackson_id.attr_list AS al JOIN jfi_dealers.dealers AS d ON d.csr = al.data WHERE al.attr_id = 14 GROUP BY user_id) AS al ON al.user_id = c.user -- WHERE c.created_at >= CURDATE() GROUP BY UserID") or die(mysqli_error( $conn2));

// 主INSERT语句，当前仅插入$data的值，需修改为同时包含$data3的值
$stmt = mysqli_prepare($conn2, "INSERT into ambition.ambition_totals (extension, total_talk_time_seconds, total_talk_time_minutes, total_outbound, credit_for_outbound, total_inbound, missed_calls, total_calls, percent_answered, date_of_report, time_of_report, dealers_contacted, percent_up_to_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON duplicate key update total_talk_time_seconds = values(total_talk_time_seconds), total_talk_time_minutes = values(total_talk_time_minutes), total_inbound = values(total_inbound), total_outbound = values(total_outbound), credit_for_outbound = values(credit_for_outbound), missed_calls = values(missed_calls), total_calls = values(total_calls), percent_answered = values(percent_answered), time_of_report = NOW(), dealers_contacted = values(dealers_contacted), percent_up_to_date = values(percent_up_to_date)) or die(mysqli_error( $conn2));

Hey there, let's break down how to fix this issue and get your combined INSERT working properly. Here's a step-by-step approach:

1. Merge Your Two SELECT Queries (Linking by User ID) #

The key here is to connect your existing call stats query ($data) with the new dealer stats query ($data3) using the user's extension (from ambition_users). We'll wrap both queries as subqueries and join them together so we get all the data in one result set.

First, here's the combined query that replaces both $data and $data3:

$combinedData = mysqli_query($conn2, "
SELECT 
    d.extension,
    d.total_talk_time_seconds,
    d.total_talk_time_minutes,
    d.total_outbound,
    d.credit_for_outbound,
    d.total_inbound,
    d.total_missed,
    d.total_calls,
    d.percent_answered,
    d.time_of_report,
    d.date_of_report,
    COALESCE(u_stats.Number_of_recorded_events, 0) AS dealers_contacted,
    COALESCE(u_stats.Percentage_up_to_date, 0.00) AS percent_up_to_date
FROM (
    -- Original call stats query (from $data)
    SELECT 
        DISTINCT CASE 
            WHEN callingpartyno IN (SELECT extension FROM ambition.ambition_users) THEN callingpartyno 
            WHEN finallycalledpartyno IN (SELECT extension FROM ambition.ambition_users) THEN finallycalledpartyno 
        END AS extension,
        SUM(duration) AS total_talk_time_seconds,
        ROUND(SUM(duration)/60,2) AS total_talk_time_minutes,
        SUM(IF(legtype1=1,1,0)) AS total_outbound,
        SUM(CASE WHEN(legtype1=1 AND duration>60) THEN 1 ELSE 0 END) AS credit_for_outbound,
        SUM(IF(legtype1=2,1,0) AND answered=1) AS total_inbound,
        SUM(IF(legtype1=2,1,0) AND answered=0) AS total_missed,
        SUM(IF(legtype1=1,1,0)) + SUM(IF(legtype1=2,1,0)) AS total_calls,
        ROUND((SUM(IF(legtype1=2,1,0) AND answered=1))/(SUM(IF(legtype1=1,1,0)) + SUM(IF(legtype1=2,1,0)))*100,2) AS percent_answered,
        NOW() AS time_of_report,
        CURDATE() AS date_of_report
    FROM cdrdb.session a 
    JOIN cdrdb.callsummary b ON a.notablecallid = b.notablecallid 
    WHERE DATE(a.ts) >= CURDATE() 
      AND (callingpartyno IN (SELECT extension FROM ambition.ambition_users) OR finallycalledpartyno IN (SELECT extension FROM ambition.ambition_users))
    GROUP BY extension
) d
-- Join with dealer stats, linking via ambition_users to connect extension and user ID
LEFT JOIN (
    -- Modified dealer stats query (from $data3) with extension mapping
    SELECT 
        au.extension,
        COUNT(*) AS Number_of_recorded_events,
        -- Handle division by zero if NumberOfDealerContacts is 0
        CASE WHEN al.NumberOfDealerContacts > 0 THEN ROUND((al.NumberOfDealers / al.NumberOfDealerContacts)*100,2) ELSE 0 END AS Percentage_up_to_date
    FROM jfi_dealers.contact_events c 
    JOIN jackson_id.users u ON c.user = u.id 
    JOIN ambition.ambition_users au ON u.id = au.ext_id -- Critical: maps user ID to extension
    JOIN jfi_dealers.dealers d ON c.dealer_num = d.dealer_num 
    LEFT JOIN (
        SELECT 
            user_id,
            COUNT(*) AS NumberOfDealerContacts,
            SUM(CASE WHEN (d.next_call_date + INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS NumberOfDealers
        FROM jackson_id.attr_list al 
        JOIN jfi_dealers.dealers d ON d.csr = al.data 
        WHERE al.attr_id =14 
        GROUP BY user_id
    ) al ON al.user_id = c.user 
    -- Uncomment below if you want to filter dealer events to today only
    -- WHERE c.created_at >= CURDATE()
    GROUP BY au.extension, c.user
) u_stats ON d.extension = u_stats.extension
") or die(mysqli_error($conn2));

Why this works: #

• We use LEFT JOIN so users without dealer stats still get their call stats (missing dealer fields default to 0 via COALESCE).
• The ambition.ambition_users join bridges the gap between the call stats' extension and the dealer stats' user ID.
• Added a CASE statement to avoid division by zero if a user has no dealer contacts.

2. Update Your INSERT Statement & Data Binding #

Your existing $stmt is almost ready, but we need to fix a small syntax error (extra closing parenthesis) and then bind data from the combined result set:

// Fixed INSERT statement (removed extra ) at the end)
$stmt = mysqli_prepare($conn2, "
INSERT INTO ambition.ambition_totals (
    extension, 
    total_talk_time_seconds, 
    total_talk_time_minutes, 
    total_outbound, 
    credit_for_outbound, 
    total_inbound, 
    missed_calls, 
    total_calls, 
    percent_answered, 
    date_of_report, 
    time_of_report, 
    dealers_contacted, 
    percent_up_to_date
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
ON DUPLICATE KEY UPDATE 
    total_talk_time_seconds = VALUES(total_talk_time_seconds),
    total_talk_time_minutes = VALUES(total_talk_time_minutes),
    total_inbound = VALUES(total_inbound),
    total_outbound = VALUES(total_outbound),
    credit_for_outbound = VALUES(credit_for_outbound),
    missed_calls = VALUES(missed_calls),
    total_calls = VALUES(total_calls),
    percent_answered = VALUES(percent_answered),
    time_of_report = NOW(),
    dealers_contacted = VALUES(dealers_contacted),
    percent_up_to_date = VALUES(percent_up_to_date)
") or die(mysqli_error($conn2));

// Bind parameters (match types to your table columns: i=integer, d=decimal, s=string)
mysqli_stmt_bind_param($stmt, "iiiiiiidssss", 
    $extension, 
    $total_talk_time_seconds, 
    $total_talk_time_minutes, 
    $total_outbound, 
    $credit_for_outbound, 
    $total_inbound, 
    $missed_calls, 
    $total_calls, 
    $percent_answered, 
    $date_of_report, 
    $time_of_report, 
    $dealers_contacted, 
    $percent_up_to_date
);

// Loop through combined data and execute inserts/updates
while ($row = mysqli_fetch_assoc($combinedData)) {
    // Map result columns to variables
    $extension = $row['extension'];
    $total_talk_time_seconds = $row['total_talk_time_seconds'];
    $total_talk_time_minutes = $row['total_talk_time_minutes'];
    $total_outbound = $row['total_outbound'];
    $credit_for_outbound = $row['credit_for_outbound'];
    $total_inbound = $row['total_inbound'];
    $missed_calls = $row['total_missed']; // Note: $data uses total_missed, table uses missed_calls
    $total_calls = $row['total_calls'];
    $percent_answered = $row['percent_answered'];
    $date_of_report = $row['date_of_report'];
    $time_of_report = $row['time_of_report'];
    $dealers_contacted = $row['dealers_contacted'];
    $percent_up_to_date = $row['percent_up_to_date'];
    
    mysqli_stmt_execute($stmt);
}

// Clean up resources
mysqli_stmt_close($stmt);
// mysqli_close($conn2); // Uncomment if you're done with the connection

Key note: #

Make sure the variable $missed_calls maps to $row['total_missed'] (your original query uses total_missed as the column name, but the table uses missed_calls).

3. Add Constraints for Data Integrity #

To make sure your ext_id association works correctly and avoid duplicate records, add these database constraints:

1. Unique Key (for ON DUPLICATE KEY UPDATE) #

This tells MySQL what defines a duplicate record (same user, same date):

ALTER TABLE ambition.ambition_totals 
ADD UNIQUE KEY idx_extension_date (extension, date_of_report);

2. Foreign Key (to enforce valid user IDs) #

If your ambition_totals table has an ext_id column, add this to ensure it only references existing users in ambition_users:

ALTER TABLE ambition.ambition_totals 
ADD CONSTRAINT fk_ambition_totals_ext_id 
FOREIGN KEY (ext_id) REFERENCES ambition.ambition_users(ext_id);

If you need to insert ext_id into the table, just add au.ext_id to the combined query's SELECT list and update the INSERT field list accordingly.

Quick Checks Before Deploying #

• Verify that ambition_users has a column linking extension to ext_id (we assumed au.ext_id matches u.id—adjust if your schema is different).
• Test the combined query directly in MySQL first to make sure it returns the expected data.
• Double-check parameter types in mysqli_stmt_bind_param to match your table's column types (e.g., percent_answered should be a decimal, so use d).

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