Php脚本在mysql插入失败时，def类型中的元素数。字符串不匹配绑定变量的数目

Question

在完成此脚本时，我得到了一些帮助，该脚本从远程服务器上的克隆表中选择并将更新的记录插入到另一台服务器上相同的表中。但是，现在在powershell中运行这个程序时，我会得到消息：Number of variables doesn't match number of parameters in prepared statement和Number of elements in type definition string doesn't match number of bind variables。我得到了它试图插入的每一行的59行错误，所以我知道bind_param的所有59个元素都有问题，但我只知道这些。

我熟悉MYSQL，但不太熟悉php，我对此没有什么问题。我只是想要一种稳定的方式，从远程服务器读取最新的记录，并将它们插入到新服务器上。我使用的是最大的SESSIONID，这是一个AI主键，用于查看远程服务器上哪些行是新的。

请帮助我绕过这些错误。

      <?php

      ini_set('memory_limit', '256M');

              // Create connection
              $conn = new mysqli($servername, $username, $password);
              $conn2 = new mysqli($servername2, $username2, $password2);

              // Check connection
              if ($conn->connect_error) {
                  die("Connection failed: " . $conn->connect_error);
              }
              echo "Connected successfully";

              // Check connection2
              if ($conn2->connect_error) {
                  die("Connection failed: " . $conn2->connect_error);
              }
              echo "Connected successfully";


            //Start queries

            $latest_result = $conn2->query("SELECT MAX(`SESSIONID`) FROM `ambition`.`session`");
            $latest_row = $latest_result->fetch_row();
            $latest_session_id = $latest_row[0];

            //Select All rows from the source phone database
            $source_data = mysqli_query($conn, "SELECT * FROM `cdrdb`.`session` WHERE `SESSIONID` > $latest_session_id");

              // Loop on the results
              while($source = $source_data->fetch_assoc()) {

                  // Check if row exists in destination phone database
                  $row_exists = $conn2->query("SELECT SESSIONID FROM ambition.session WHERE SESSIONID = '".$source['SESSIONID']."' ") or die(mysqli_error($conn2));

                      //if query returns false, rows don't exist with that new ID.
                      if ($row_exists->num_rows == 0){

                            //Insert new rows into ambition.session
                            $stmt = $conn2->prepare("INSERT INTO ambition.session (SESSIONTYPE,CALLINGPARTYNO,FINALLYCALLEDPARTYNO,DIALPLANNAME,TERMINATIONREASONCODE,ISCLEARINGLEGORIGINATING,CREATIONTIMESTAMP,ALERTINGTIMESTAMP,CONNECTTIMESTAMP,DISCONNECTTIMESTAMP,HOLDTIMESECS,LEGTYPE1,LEGTYPE2,INTERNALPARTYTYPE1,INTERNALPARTYTYPE2,SERVICETYPEID1,SERVICETYPEID2,EXTENSIONID1,EXTENSIONID2,LOCATION1,LOCATION2,TRUNKGROUPNAME1,TRUNKGROUPNAME2,SESSIONIDTRANSFEREDFROM,SESSIONIDTRANSFEREDTO,ISTRANSFERINITIATEDBYLEG1,SERVICEEXTENSION1,SERVICEEXTENSION2,SERVICENAME1,SERVICENAME2,MISSEDUSERID2,ISEMERGENCYCALL,NOTABLECALLID,RESPONSIBLEUSEREXTENSIONID,ORIGINALLYCALLEDPARTYNO,ACCOUNTCODE,ACCOUNTCLIENT,ORIGINATINGLEGID,SYSTEMRESTARTNO,PATTERN,HOLDCOUNT,AUXSESSIONTYPE,DEVICEID1,DEVICEID2,ISLEG1ORIGINATING,ISLEG2ORIGINATING,GLOBALCALLID,CADTEMPLATEID,CADTEMPLATEID2,ts,INITIATOR,ACCOUNTNAME,APPNAME,CALLID,CHRTYPE,CALLERNAME,serviceid1,serviceid2)


                            VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)") or die(mysqli_error($conn2)) ;


                            $stmt->bind_Param("i,s,s,s,i,i,s,s,s,s,i,i,i,i,i,i,i,i,i,s,s,s,s,i,i,i,s,s,s,s,i,i,i,i,s,s,s,i,i,s,i,i,i,i,i,i,i,i,i,s,i,s,s,s,i,s,i,i"
                                                 ,$source['SESSIONTYPE']
                                                 ,$source['CALLINGPARTYNO']
                                                 ,$source['FINALLYCALLEDPARTYNO']
                                                 ,$source['DIALPLANNAME']
                                                 ,$source['TERMINATIONREASONCODE']
                                                 ,$source['ISCLEARINGLEGORIGINATING']
                                                 ,$source['CREATIONTIMESTAMP']
                                                 ,$source['ALERTINGTIMESTAMP']
                                                 ,$source['CONNECTTIMESTAMP']
                                                 ,$source['DISCONNECTTIMESTAMP']
                                                 ,$source['HOLDTIMESECS']
                                                 ,$source['LEGTYPE1']
                                                 ,$source['LEGTYPE2']
                                                 ,$source['INTERNALPARTYTYPE1']
                                                 ,$source['INTERNALPARTYTYPE2']
                                                 ,$source['SERVICETYPEID1']
                                                 ,$source['SERVICETYPEID2']
                                                 ,$source['EXTENSIONID1']
                                                 ,$source['EXTENSIONID2']
                                                 ,$source['LOCATION1']
                                                 ,$source['LOCATION2']
                                                 ,$source['TRUNKGROUPNAME1']
                                                 ,$source['TRUNKGROUPNAME2']
                                                 ,$source['SESSIONIDTRANSFEREDFROM']
                                                 ,$source['SESSIONIDTRANSFEREDTO']
                                                 ,$source['ISTRANSFERINITIATEDBYLEG1']
                                                 ,$source['SERVICEEXTENSION1']
                                                 ,$source['SERVICEEXTENSION2']
                                                 ,$source['SERVICENAME1']
                                                 ,$source['SERVICENAME2']
                                                 ,$source['MISSEDUSERID2']
                                                 ,$source['ISEMERGENCYCALL']
                                                 ,$source['NOTABLECALLID']
                                                 ,$source['RESPONSIBLEUSEREXTENSIONID']
                                                 ,$source['ORIGINALLYCALLEDPARTYNO']
                                                 ,$source['ACCOUNTCODE']
                                                 ,$source['ACCOUNTCLIENT']
                                                 ,$source['ORIGINATINGLEGID']
                                                 ,$source['SYSTEMRESTARTNO']
                                                 ,$source['PATTERN']
                                                 ,$source['HOLDCOUNT']
                                                 ,$source['AUXSESSIONTYPE']
                                                 ,$source['DEVICEID1']
                                                 ,$source['DEVICEID2']
                                                 ,$source['ISLEG1ORIGINATING']
                                                 ,$source['ISLEG2ORIGINATING']
                                                 ,$source['GLOBALCALLID']
                                                 ,$source['CADTEMPLATEID']
                                                 ,$source['CADTEMPLATEID2']
                                                 ,$source['ts']
                                                 ,$source['INITIATOR']
                                                 ,$source['ACCOUNTNAME']
                                                 ,$source['APPNAME']
                                                 ,$source['CALLID']
                                                 ,$source['CHRTYPE']
                                                 ,$source['CALLERNAME']
                                                 ,$source['serviceid1']
                                                 ,$source['serviceid2']);

                                              $stmt->execute();
                          }
              }

     ?>

Answer 1

您的表位于AI自动增量中，不需要在INSERT语句中添加主键列。

$stmt = $conn2->prepare("INSERT INTO ambition.session (SESSIONTYPE,CALLINGPARTYNO,FINALLYCALLEDPARTYNO,DIALPLANNAME,TERMINATIONREASONCODE,ISCLEARINGLEGORIGINATING,CREATIONTIMESTAMP,ALERTINGTIMESTAMP,CONNECTTIMESTAMP,DISCONNECTTIMESTAMP,HOLDTIMESECS,LEGTYPE1,LEGTYPE2,INTERNALPARTYTYPE1,INTERNALPARTYTYPE2,SERVICETYPEID1,SERVICETYPEID2,EXTENSIONID1,EXTENSIONID2,LOCATION1,LOCATION2,TRUNKGROUPNAME1,TRUNKGROUPNAME2,SESSIONIDTRANSFEREDFROM,SESSIONIDTRANSFEREDTO,ISTRANSFERINITIATEDBYLEG1,SERVICEEXTENSION1,SERVICEEXTENSION2,SERVICENAME1,SERVICENAME2,MISSEDUSERID2,ISEMERGENCYCALL,NOTABLECALLID,RESPONSIBLEUSEREXTENSIONID,ORIGINALLYCALLEDPARTYNO,ACCOUNTCODE,ACCOUNTCLIENT,ORIGINATINGLEGID,SYSTEMRESTARTNO,PATTERN,HOLDCOUNT,AUXSESSIONTYPE,DEVICEID1,DEVICEID2,ISLEG1ORIGINATING,ISLEG2ORIGINATING,GLOBALCALLID,CADTEMPLATEID,CADTEMPLATEID2,ts,INITIATOR,ACCOUNTNAME,APPNAME,CALLID,CHRTYPE,CALLERNAME,serviceid1,serviceid2)


                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)") or die(mysqli_error($conn2)) ;

需要更改您的bin_param

//example here i guess **SESSIONTYPE COLUMN IS INTEGER**
$sth->bindParam(':SESSIONTYPE', $source['SESSIONTYPE'], PDO::PARAM_INT);