mysql query

[daveh33]

I have done the below mysql query to get a variable $block_keyword

 

$query=mysql_query("SELECT DISTINCT(keyword) FROM b_sent WHERE mobile='$value1'");
while ($row=mysql_fetch_array($query)) {
$block_keyword=$row['keyword'];
echo "$block_keyword<br />";
}

 

This works fine - I now what to do another query as below

 

$result=mysql_query("SELECT * FROM b_keyword WHERE keyword!='$block_keyword'");

 

The $block_keyword only seems to pick up 1 record ..

 

The first query returns 5 records - how can I re-write the 2nd query to filter all of them?

[trq]

<?php

if ($result = mysql_query("SELECT DISTINCT(keyword) FROM b_sent WHERE mobile='$value1'")) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_array($result)) {
      $block_keyword[] = $row['keyword'];
    }
  }
}

$result = mysql_query("SELECT * FROM b_keyword WHERE keyword NOT IN('" . implode(',', $block_keyword) . "'");

?>

[daveh33]

I get the error: -

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

this is for this line: -

 

while ($row=mysql_fetch_array($result)) {

 

 

[trq]

This means your query is failing. What does this produce?

 

<?php

$sql = "SELECT DISTINCT(keyword) FROM b_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");
}

$result = mysql_query("SELECT * FROM b_keyword WHERE keyword NOT IN('" . implode(',', $block_keyword) . "'");

?>

[daveh33]

it produces the same error - Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

and indicated error on line: while ($row=mysql_fetch_array($result)) {

 

With the below line of code you wrote, is it causing an error with the NOT IN part - as its a different table?

 

$result = mysql_query("SELECT * FROM b_keyword WHERE keyword NOT IN('" . implode(',', $block_keyword) . "'");

[trq]

Post your code.

[daveh33]

$sql = "SELECT DISTINCT(keyword) FROM b_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");
}

$result = mysql_query("SELECT * FROM b_keyword WHERE keyword NOT IN('" . implode(',', $block_keyword) . "'");

echo "<br /><br>Your Mobile: $value1<br />";

while ($row=mysql_fetch_array($result)) {
$keyword=$row['keyword'];

$eregi=$row['eregi'];
$dbsms=$row['sms'];

[trq]

You really need to try and handle errors, my example is a good example. What does...

 

<?php

$sql = "SELECT DISTINCT(keyword) FROM b_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 b_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'];
    }
  }
} else {
  die(mysql_error() . "<br />$sql");
}

?>

 

produce? Also, where is $value1 comming from?

[daveh33]

$value1=$_GET['mobile'];

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

SELECT * FROM b_keyword WHERE keyword NOT IN('hello'

[trq]

Sorry, should be....

 

$sql = "SELECT * FROM b_keyword WHERE keyword NOT IN('" . implode(',', $block_keyword) . "')";

[daveh33]

Warning: implode() [function.implode]: Bad arguments. - for the same line of code

[trq]

Hmmm, not sure about that. Add this to the top.

 

$block_keyword = array();

[daveh33]

that removes the errors - but the code still isnt working the way it should

 

can you look at the attached code please

 

 

 

[attachment deleted by admin]

[trq]

What is happening and what exactly do you expect?

 

Also, thats not too much code to post here. Fix the indentation and post it within

tags..

[daveh33]

<?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 />";


$result1=mysql_query("SELECT * FROM bot_sent");
$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());

exit();
}
}
  }
} else {
  die(mysql_error() . "<br />$sql");
}
?>

 

Basically - I want to run the eregi statement only once per keyword.. part of the statement adds to the 'bot_sent' table - so I want it to ignore it if its already been processed. But there may be more than 1 keyword in the string

[trq]

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

[trq]

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

[trq]

Try...

 

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

 

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

 

before I did

 

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

[trq]

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

[trq]

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

 

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