php - 防止从 php mysql 数据库中一遍又一遍地选择相同的数据

Question

下面的代码应该从数据库中选择数据，然后使用变量发送短信。我正在使用 cron 作业来执行脚本。问题是当 cron 运行时，它也会选择已经处理过的数据。有人请指导我如何防止选择已经处理过的数据....

    //should select the fresh or new data after the first run
    $sql = "SELECT name, amount, trans_id, msisdn, time_paid FROM customer";


    $result1 = mysqli_query($conn, $sql);
    $resultarr = mysqli_fetch_assoc($result1); // fetch data


            $name = $resultarr['name'];
            $amount = $resultarr['amount'];
            $transaction_id = $resultarr['trans_id'];
            $date = $resultarr['time_paid'];

             //message template
            $message = "Dear $name we have received $amount from you. MPESA transaction Id $transaction_id on $date.";

        $mobilenumber = $resultarr['msisdn']; // get mobile number from array
        $message_sent = $message;

            $serviceArguments = array(
                            "mobilenumber" => $mobilenumber,
                            "message" => $message_sent
            );

            $client = new SoapClient("http://32.138.160.130:8080/smsengine/smsws?WSDL"); 

Answer 1

添加短信状态的新列。与 sms_sent 类似，此列的默认值为 0，当短信发送成功后，将 sms_sent 值从 0 更改为 1。然后sql查询是:$sql = "从客户那里选择姓名、金额、trans_id、msisdn、time_paid WHERE sms_sent= 0";

<小时/>

    //should select the fresh or new data after the first run
    $sql = "SELECT id, name, amount, trans_id, msisdn, time_paid FROM customer";


    $result1 = mysqli_query($conn, $sql);
    $resultarr = mysqli_fetch_assoc($result1); // fetch data


            $name = $resultarr['name'];
            $amount = $resultarr['amount'];
            $transaction_id = $resultarr['trans_id'];
            $date = $resultarr['time_paid'];

             //message template
            $message = "Dear $name we have received $amount from you. MPESA transaction Id $transaction_id on $date.";

        $mobilenumber = $resultarr['msisdn']; // get mobile number from array
        $message_sent = $message;

            $serviceArguments = array(
                            "mobilenumber" => $mobilenumber,
                            "message" => $message_sent
            );

            $client = new SoapClient("http://32.138.160.130:8080/smsengine/smsws?WSDL"); 
$sq = "UPDATE customer SET notification = 1 WHERE id = $resultarr['id']";