title: How to synchronize my Perl script with MySQL query
body: <p><img src="https://i.stack.imgur.com/eCzzn.png" alt="SYNCORDERRELATION TABLE"></p>
  
  <p><img src="https://i.stack.imgur.com/dTvpw.png" alt="OUTGOING TABLE"></p>
  
  <p>I am new in coding in every sense of the word so kindly bear with me.</p>
  
  <p>I have created a Perl script that communicates with my PHP application but am stuck on the final hurdle of synchronizing them with MySQL queries. I want the Perl script to read table <code>Syncorderrelation</code> in my database, and to auto respond only once, sending a text message by posting the 'Auto Response' on my 'Outgoing Table') to any new entries that meets the following criteria:</p>
  
  <ol>
  <li><p>The entry to have an <code>updatetype</code> of 1, which means the new entry is a new individual who has subscribed to a product.</p></li>
  <li><p>The Perl script to read the new entry of <code>DEST_MSISDN</code> which is a field on the same <code>Syncorderrelation</code> table (just a phone number) and to reply to the number with an automated response (just a welcome message). This is done by only posting the 'Auto Response' on the 'Outgoing Table', where my PHP application takes over and sends the response on its own.</p></li>
  </ol>
  
  <p>So my question is this: how do I go about creating a query whereby the Perl script will auto reply only to new entries in the <code>Syncorderrelation</code> table from The current time onwards. As currently, there are hundreds of other entries of which I don't want to trigger a response to.</p>
  
  <p>But only send a reply to the New <code>DEST_MSISDN</code> (phone number) and only reply to this phone number and only do so once, and thus not keep resending the same welcome message, to the same number again and again.</p>
  
  <p><strong>Note</strong>: The only way that the script can know it is a new entry, will be through the <code>ID</code> Field. This is a unique field that is auto-generated every time a new entry enters into the <code>Syncorderrelation</code> table (which will now have increased from its current number of '440' to '441'). </p>
  
  <p>The script I have created posts correctly all the requisite fields from <code>Syncoderrelation</code> table to <code>Outgoing</code> table, but am unable to read the new <code>Dest_Msisdn</code> (phone number) from <code>Syncorderrelation</code> table and post the phone number to the <code>Outgoing</code> table. Without it the PHP application will not know where to send the welcome message to.</p>
  
  <pre class="lang-perl prettyprint-override"><code>use strict;
  
  use LWP::UserAgent;
  use HTTP::Request::Common qw(POST);
  use XML::Simple;
  use Digest::MD5 qw(md5_hex);
  use Time::Piece;
  use DBI;
  use Time::HiRes qw(usleep nanosleep);
  
  my $dest_msisdn             = '';
  my $link_id                 = '';
  my $ID                      = '';
  my $subscription_auto_reply = '';
  my $user                    = '';
  my $send_time               = '';
  my $now                     = '';
  my $correlator              = '2345';
  my $sender_name             = '30045';
  my $source                  = '30045';
  my $text_message            = 'Dear Customer Welcome to our service';
  my $ua                      = new LWP::UserAgent(keep_alive =&gt; 1);
  
  #get all the configs
  
  #get sms to be  sent
  #set the connection
  
  my $dbh = DBI-&gt;connect('DBI:mysql:Database404', 'Kapo', 'XXXXXXXXX') || die "Could not connect to database: $DBI::errstr";
  my $j = 0;
  
  while ($j &lt; 1) {
    print "AutoResponse....\n";
  
    my $sql = "SELECT * FROM syncorderrelation WHERE updatetype = '1'";
    my $results = $dbh-&gt;selectall_hashref($sql, 'id');
  
    foreach my $id (keys %$results) {
      print "AutoResponder!!!.....\n";
  
      #get all the required columns
      #get all the required columns
      my $textMessage  = $results-&gt;{$id}-&gt;{text_message};    #$results-&gt;{$id}-&gt;{dest_msisdn};
      my $correlator   = $results-&gt;{$id}-&gt;{id};
      my $senderName   = $results-&gt;{$id}-&gt;{sender_name};
      my $linkID       = $results-&gt;{$id}-&gt;{link_id};
      my $textID       = $results-&gt;{$id}-&gt;{id};
      my $client       = $results-&gt;{$id}-&gt;{client};
      my $productId    = $results-&gt;{$id}-&gt;{product_id};
      my $dest_msisdn  = $results-&gt;{$id}-&gt;{dest_msisdn};
      my $text_message = $results-&gt;{$id}-&gt;{text_message};
      my $autoReply    = $results-&gt;{$id}-&gt;{autoreply};
  
  #####send service
  
      my $user = 5;
  
      my $send_time = time;
      my $now       = time;
  
      #catch error
  
      my $sql1 = "INSERT INTO outgoing_sms(dest_msisdn, text_message, sender_name, link_id, correlator, send_time, client,  user_id, timestamp) VALUES ('$dest_msisdn', 'Dear Customer Welcome to our service', '$sender_name', '$link_id', '1161', '$now', '1',  '$user', '$now')";
      $dbh-&gt;do($sql1);
  
      #print $sql1;
  ###send
      print $sql1;
  
      #remove from outgoing table
      my $sql2 = "UPDATE incoming_sms SET sent_flag = 1 WHERE id='$correlator'";
      $dbh-&gt;do($sql2);
  
      print $sql2;
  
    }
  
    usleep(1000000);
  }
  </code></pre>