PHP 脚本对于 mysql 插入失败，元素数量为 def 类型。字符串与绑定(bind)变量的数量不匹配

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，而且我除了这方面的问题之外什么也没有。我只是想要一种稳定的方式来从远程服务器读取最新记录并将它们插入到新服务器。我正在使用 max 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 STATMENT中添加主键列

$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);