PHP，SELECT 在同一脚本中插入后不返回值

Question

这是一个简单的方法，但无法弄清楚为什么不起作用。需要另一双眼睛。3 个查询。 1 次插入，1 次选择，然后 1 次再次插入。我的选择查询应该从最近插入的数据中获取一个值，然后将该值存储在一个变量中，然后为另一个表运行第二个插入。嗯，一切正常，除了选择。我什至尝试添加一个 mysql_query("COMMIT",$conn) 但仍然没有。非常感谢任何帮助。谢谢!!!

$customerQ = "INSERT INTO
customerData(fname, mname, lname, email, homePhone, cellphone, address, 
city, county, state, zip, gascompany, eleccompany, addedBy)
VALUES('$fname', '$mname', '$lname', '$emailCustomer', '$hphone', '$cellphone',
  '$address', '$city', '$county', '$state', '$zip', '$gas', '$electric', 
  '$userName'
) ";             
//General WO# calculation
$calcWO = "SELECT 
              auditRequest.workOrderNum
            FROM
              auditRequest
            ORDER BY
               auditRequest.workOrderNum DESC
            LIMIT 1
        ";  

//General Customer number calculation. For the Work Order Insert
$calc = "SELECT
              customerData.custnumber
           FROM
              customerData
           WHERE
              fname = '$fname' and
              mname = '$mname' and
              lname = '$lname'
           ORDER BY
           customerData.custnumber DESC
           LIMIT 1
     "; 

    //Customer Information Insert (from Insert Query)
    $resultCustQ = mysql_query($customerQ, $connection);
    mysql_query("COMMIT", $connection); //make sure to commit to be able to query new records
    // Test the query
    if(!$resultCustQ) die ("error 1". mysql_error());

    //Customer Information Insert (from Insert Query)
    $resultCustQ = mysql_query($customerQ, $connection);
    mysql_query("COMMIT", $connection); //make sure to commit to be able to query new records
    // Test the query
    if(!$resultCustQ) die ("error 1". mysql_error());

    //Calculate new customer number
    $calc_result = mysql_query($calc, $connection);
    // Test the query
    if(!$calc_result) die ("error 1". mysql_error());
    $row=mysql_fetch_array($calc_result);
    $custnum = $row['custnumber']; // This is the new Customer.

    //Work Order Information (run SQL Insert)
    $resultWOQ = mysql_query($workOrderQ, $connection);
// Test the query
    if(!$resultWOQ) die ("error 1". mysql_error());

Answer 1

我会避免使用 INSERT 后接 SELECT 来完全找到插入的记录，并使用 MySQL 的功能返回最近插入的行的 ID(前提是您的表作为 AUTO INCREMENT 主键)通过 PHP 的 mysql_insert_id , 也可通过 PDO 通过 lastInsertId 获得.