防止从php mysql数据库反复选择相同的数据

Question

My code below should select data from db and then use the variables to send an SMS. I'm using a cron job to execute the script. The problem is that when cron runs, it also picks the already processed data. Someone please guide me on how to prevent selecting already worked on data....

    //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

Add new column for status of sms. Like sms_sent and default value of this column is 0 and when sms is successfully send then change the sms_sent value 0 to 1. ANd then sql query is: $sql = "SELECT name, amount, trans_id, msisdn, time_paid FROM customer 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']";