转义字符串/在php脚本中插入

Question

我正在尝试完成一个脚本，该脚本连接到两个数据库，每个数据库位于不同的服务器上，并预先形成一个更新。基本上，从其中选择并插入的表是相同的:我前几天做了一个转储/导入。脚本需要将本地表从远程更新一次，因为将有每日记录插入到远程表中，并且我需要在本地更新它。

这里的关键是通过查看表共享的自动递增主键( SESSIONID )来确定远程服务器上的新行。我试图让下面的循环说，如果id存在于远程服务器而不是本地服务器中，那么将这些记录插入本地服务器中。

我在powershell中通过输入php 'filename‘运行下面的脚本，我得到两个成功的连接消息，然后得到以下消息：Incorrect datetime value: '' for column 'ALERTINGTIMESTAMP' at row 1。在该记录中，它试图插入的日期时间值为NULL，但表允许这个值，但我担心这是转义字符之类的问题。

如何修改这些以正确转义，或插入这些记录。

注意:在这种情况下，复制和大型转储/导入/表重新创建对我们来说不是一个选项。我们有几个类似的脚本来运行，我们希望在这里保持相同的进程。我只是想解决这些错误，或者让别人给我一种更有效的方法来编写这个脚本，也许是使用max id或者其他类似的方法。

下面是剧本：

      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";


    $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_item = $source_data->fetch_assoc()) {

          // Check if row exists in destination phone database
          $row_exists = $conn2->query("SELECT SESSIONID FROM ambition.session WHERE SESSIONID = '".$source_item['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
                    $conn2->query("INSERT INTO ambition.session (SESSIONID,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 ('".$source['SESSIONID']."' ,
                            '".$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']."')");
                  }
      }

Answer 1

就像Pankaj说的，试试这样的东西：

//Insert new rows into ambition.session
$statement = $conn2->prepare('INSERT INTO ambition.session (SESSIONID,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 (?, ?, ?, ...);');
$statement->bindParam(1, $source['SESSIONID']);
$statement->bindParam(2, $source['SESSIONTYPE']);
$statement->bindParam(3, $source['CALLINGPARTYNO']);
//...
$statement->execute();