Topic: mysql query

[thorpe]

Pretty hard to tell whats going on because you didn't fix the indentation, but that exit() you have within your while() will stop the script executing alltogether after the first keyword.

[daveh33]

sorry I dont no what you mean about the indentation? whats wrong with it?

without the exit() how can I stop the code from processing more than 1 keyword?

This is a SMS bot - for each keyword it sends a reply - I don't want it to send 3 messages if it matches 3 keywords

[thorpe]

I want to run the eregi statement only once per keyword

Do you want it to run once per keyword or not?

[daveh33]

I want it to: -

1. check if the 1st keyword it finds has already been actioned in (checking if its in bot_sent)
2. if its not - process that one
3. if it has - skip it and look for next keyword to have the same loop repeated

[thorpe]

Try...

Code: [Select]

<?php
 
require_once("config.php");
$senda =$_REQUEST['sender'];
$sender = str_replace("+", "", $senda);
$sms = $_REQUEST['sms'];
$value1 = substr($sender, 3);
$value1 = "07" . $value1;
$dts=date("Y-m-d H:i:s");
$rand=rand(61,121);
$addrtime = $rand + time() ;
$newdate=date("YmdHi", $addrtime) ;

$block_keyword = array();
// to delay by random time beetn 61 & 121 seconds - add &DelayUntil=$newdate

$sql = "SELECT DISTINCT(keyword) FROM bot_sent WHERE mobile='$value1'";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_array($result)) {
      $block_keyword[] = $row['keyword'];
    }
  }
} else {
  die(mysql_error() ."<br />$sql");
}

$sql = "SELECT * FROM bot_keyword WHERE keyword NOT IN('" . implode(',', $block_keyword) . "')";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    echo "<br /><br>Your Mobile: $value1<br />";
    while ($row = mysql_fetch_array($result)) {
      $keyword = $row['keyword'];
      $eregi = $row['eregi'];
      $dbsms = $row['sms'];

      if (eregi ($eregi, $sms)) {
        echo "If your SMS was: <b>$sms</b> <br />
        The reply SMS would be: <b>$dbsms</b>
        <br />
        <br />
        Keyword: <b>$keyword</b> marked - no messages related to this keyword will be sent again to this number <br />";

        $sql = "SELECT * FROM bot_sent WHERE keyword = '$keyword'"
        if ($result1 = mysql_query($sql)) {
          if (!$numrows1 = mysql_num_rows($result1))
            $ssid=$numrows1+1;
            mysql_query("INSERT INTO bot_sent (id,mobile,keyword,dts) VALUES ('$ssid','$value1','$keyword','$dts')") or die(mysql_error());
          }
        }
      }
    }
  }
} else {
  die(mysql_error() . "<br />$sql");
}

?>


[daveh33]

OK I have run a test & the first keyword it picks up - but it gives an error

Duplicate entry '' for key 1

thats for this mysql_query

Code: [Select]

  mysql_query("INSERT INTO bot_sent (id,mobile,keyword,dts) VALUES ('$ssid','$value1','$keyword','$dts')") or die(mysql_error());
before I did

Code: [Select]

$sql=mysql_query("SELECT * FROM bot_sent");
$numrows=mysql_num_rows($sql);
$ssid=$numrows+1;

[thorpe]

Your really much better off making use of an auto incrementing field rather than trying to create your own id's.

[daveh33]

yes your right. Ok I have changed that.

I don't get any errors now - but if the $sms string has more than 1 keyword match it repeats the loop.

I want it so that it only processes one & adds it to the bot_sent - so that next time its ran, it will ignore that keyword - then process the next.

Is that an exit(); that I need to add? I Know you said to remove that before..

[thorpe]

3. if it has - skip it and look for next keyword to have the same loop repeated

I want it so that it only processes one & adds it to the bot_sent - so that next time its ran, it will ignore that keyword - then process the next.

See where I'm confused. Yes, exit will exit the loop.

[daveh33]

The code produces the following output in my browser: -

Code: [Select]

If your SMS was: hi how are you
The reply SMS would be: REPLY MESSAGE FOR HI

Keyword: hi marked - no messages related to this keyword will be sent again to this number


If your SMS was: hi how are you
The reply SMS would be: REPLY MESSAGE FOR HOW ARE YOU

Keyword: how r u marked - no messages related to this keyword will be sent again to this number
I am going to replace that text with a code to integrate it with the API

I want it so that it only processes it for the first keyword then exits - then the next time its ran, it won't find that keyword & skip to the next