php - 选择条件SQL语句

我正在将数据发布到URL以发送感谢消息。挑选的详细信息来自一个名为readtextfilejson的表。我从那里向用户发送“谢谢”消息。

但是，我无法选择仅未收到短信的用户的详细信息。我的代码是选择所有数据并一次又一次地循环发布到所有用户。因此，向用户发送多个ThankYouMessages。

我添加了一个名为ThankyouMessage的新列，该列默认为=“未发送”。
这样，当我的脚本运行时，它将ThankYouMessage列更新为='SENT'，以便我的脚本只能选择尚未收到Thankyoumessage的用户的详细信息。

因此，我不会一直重复发送相同的消息。请在下面查看我的脚本，并协助我解决此问题。

我的表结构：

 <?php

        $data = (string) file_get_contents($file);

        //echo $data;

        $data = str_replace('//Confirmation Respose', '', $data);
        $data = str_replace('// Validation Response', '', $data);
        $data = str_replace(' ', '', $data);
        $data = preg_replace('/\s+/S', " ", $data);
        $data = trim($data);
        $pattern = '/\s*/m';
        $replace = '';

        $testString = $data;

        $removedWhitespace = preg_replace( $pattern, $replace,$testString );
        $removedWhitespace2 = str_replace (' ', '', $testString);

        $getAllData = explode('}{', $removedWhitespace2);
        foreach ($getAllData as $row) {
           $row = str_replace('{', '', $row);

           $rowData = explode(',"', $row);
           $rowData = explode(',"', $row);
           $columnValues = array();
           $chkTransId = '';



           foreach ($rowData as $value) {
              $newVal = explode(':', $value);
              $key = str_replace('"', '', $newVal[0]);
              $val = str_replace('"', '', $newVal[1]);
              $val = trim($val);

              $columnValues[] = ($val) ? "'$val'": "''";
              if($key == 'TransID'){
                 $chkTransId = $val;
              }
           }
           if($chkTransId == ''){
              continue;
           }
           ////THIS IS THE SECTION AM HAVING PROBLEMS WITH - I WANT TO
           ////SELECT ONLY THE DATA WHERE THE COLUMN  WHERE thankyoumessage =
            ///// 'NOT SENT'
           $chkSql = "select * from `readtextfilejson`where TransID='$chkTransId'";

           $getResult = mysqli_query($con, $chkSql);
           $getCount = mysqli_num_rows($getResult);
           $row = mysqli_fetch_object($getResult);

$text = "Dear ". $row->FirstName ." Your Payment of ". $row->TransAmount ." to XXXXX was Received Succesfully. Confirmation Code: ". $row->TransID  ."";
   $destination = array("messageId"=>"$product_id","to"=>"$row->MSISDN");
   $product_id=uniqid();



 $notifyUrl = "URL";
    $notifyContentType = "application/json";
    $callbackData = 'eHostOnlineCodeCheck125690';
    $username = "USERNAME";
    $password = "PASSWORD";
    $postUrl = "POSTURL";

    $message = array("from" => "USERNAME",
            "destinations" => $destination,
            "text" => $text,
            "bulkId" => "",
        "notifyUrl" => $notifyUrl,
        "flash" => "false",
            "notifyContentType" => $notifyContentType,
        "callbackData" => $callbackData);
    $postData = array("messages" => array($message));
    $postDataJson = json_encode($postData);
    //Submit all data to SMS server
    $ch = curl_init();
    $header = array("Content-Type:application/json", "Accept:application/json");
    curl_setopt($ch, CURLOPT_URL, $postUrl);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
    curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
    curl_setopt($ch, CURLOPT_USERPWD, $username . ":" . $password);
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 2);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, TRUE);
    curl_setopt($ch, CURLOPT_MAXREDIRS, 2);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $postDataJson);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    // response of the POST request
    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $responseBody = json_decode($response);

    curl_close($ch);

echo "<pre>";
print_r($row);
print_r($responseBody);
echo "</pre>";



 $Sql = "UPDATE readtextfilejson SET thankyoumessage = 'SENT' WHERE thankyoumessage = 'not sent'";
  mysqli_query($con, $Sql) or die(mysqli_error($con));




   if($getCount > 0){
      continue;
   }

   $columnValues = implode(',', $columnValues);
   $sql = "INSERT INTO `readtextfilejson`(`TransactionType`, `TransID`, `TransTime`, `TransAmount`, `BusinessShortCode`, `BillRefNumber`, `InvoiceNumber`, `OrgAccountBalance`, `ThirdPartyTransID`, `MSISDN`, `FirstName`, `MiddleName`, `LastName`) VALUES (".$columnValues.")";

   mysqli_query($con, $sql) or die(mysqli_error($con));
}
echo 'Data inserted successfully';
?>

最佳答案

2个可能的问题。

1）无论thankyoumessage设置为什么，您都在选择交易。您可能需要在第一个SQL的where子句中添加条件

SELECT * FROM `readtextfilejson`
WHERE TransID = '$chkTransId' AND thankyoumessage = 'not sent'

2）当您将事务thankyoumessage更新为“已发送”时，您正在设置所有事务，因为您的更新语句缺少事务ID。您可能需要添加它。

UPDATE readtextfilejson
SET thankyoumessage = 'SENT'
WHERE thankyoumessage = 'not sent' AND TransID = '$chkTransId'

并且由于您希望将其设置为SENT，而不管它以前是什么，所以您可能也不需要thankyoumessage检查。

UPDATE readtextfilejson
SET thankyoumessage = 'SENT'
WHERE TransID = '$chkTransId'