AGI to MySQL via PHP

Asterisk Asterisk Support
1

Hello,

I have the following script here that I am trying to have write to a database:

in extensions.conf

exten => _*1[1234]0XX2,14,AGI(agent-log.agi|start,${AGENT_NUM},${PAUSE_CODE},${TIMESTAMP})

in agent-log.agi
#!/usr/bin/php -q

<?php

	while (!feof($stdin)) 
	{
		$temp = fgets($stdin);
		$temp = str_replace("\n","",$temp);
		$s = explode(":",$temp);
		$agivar[$s[0]] = trim($s[1]);
		
		if (($temp == "") || ($temp == "\n"))
			{
				break;
			}
	}
	
	$database = "";
	$username = "";
	$password = "";

	mysql_connect("",$username,$password);
	mysql_select_db($database) or die( "Unable to select database");

	//log all this good stuff into mysql 
	
	if($argv[1] == 'start') {

		$sql = 'INSERT INTO ` agent_status` (`agent`, `agent_code`, `log_time`) VALUES(';
		$sql .= '"' .$argv[2]. '",';
		$sql .= '"' .$argv[3]. '",';
		$sql .= '"' .$argv[4]. '",';
		$sql .= '"")';

	mysql_query($sql) or die(mysql_error()); }

	mysql_close();


?>

The issue i’m having is that the variables as set when I watch from the asterisk CLI, but they do not seem to get passed on into this script and then written into our database. Please advise.

Thank You!

2

Use the following code to parse your asterisk variables in to a neat array then retreive your values with $agi[‘arg_1’] etc.

ob_implicit_flush(true);
 set_time_limit(6);
 $in = fopen("php://stdin","r");
 $stdlog = fopen("/var/log/asterisk/agi.log", "w");

function read() {
   global $in, $debug, $stdlog;
   $input = str_replace("\n", "", fgets($in, 4096));
   if ($debug) fputs($stdlog, "read: $input\n");
   return $input;
 }

// main program
 // parse agi headers into array
 while ($env=read()) {
	$s = split(": ",$env);
	$agi[str_replace("agi_","",$s[0])] = trim($s[1]);
	if (($env == "") || ($env == "\n")) {
		break;
	}
 }
3

Thank you wimnat - I have added this code but still seem to be having issues. The agi completes without any errors (that I can see in the CLI). The agi.log is empty. I tired running the file from the command line ./filename.agi start foo foo1 foo2 foo3 and the line drops underneath and does nothing. Needless to say no records are written into the DB.

#!/usr/bin/php -q
<?php

ob_implicit_flush(true);
 set_time_limit(6);
 $in = fopen("php://stdin","r");
 $stdlog = fopen("/var/log/asterisk/agi.log", "w");

function read() {
   global $in, $debug, $stdlog;
   $input = str_replace("\n", "", fgets($in, 4096));
   if ($debug) fputs($stdlog, "read: $input\n");
   return $input;
 }

// main program
 // parse agi headers into array
 while ($env=read()) {
   $s = split(": ",$env);
   $agi[str_replace("agi_","",$s[0])] = trim($s[1]);
   if (($env == "") || ($env == "\n")) {
      break;

        $database = "db";
        $username = "dbuser";
        $password = "dbpass";

        mysql_connect("databaselocation.com:3309",$username,$password);
        mysql_select_db($database) or die( "Unable to select database");

        //log all this good stuff into mysql

        if($agi['arg_1'] == 'start') {

                $sql = 'INSERT INTO ` agent_status` (`agent`, `agent_code`, `log_time`) VALUES(';
                $sql .= '"' .$agi['arg_2']. '",';
                $sql .= '"' .$agi['arg_3']. '",';
                $sql .= '"' .$agi['arg_4']. '",';
                $sql .= '"")';

        mysql_query($sql) or die(mysql_error()); }

        mysql_close();
 }
 }

?>
4

Have you set agi debugging to on?

‘agi set debug on’

Add a write function and write your own Verbose statements to aid debugging. You can use the write function instead of echo if you want to write to your log.

// toggle debugging output (more verbose)
 $debug = true;

 
 function write($line) {
   global $debug, $stdlog;
   if ($debug) fputs($stdlog, "write: $line\n");
   echo $line."\n";
 }

 function errlog($line) {
   global $err;
   echo "VERBOSE \"$line\"\n";
 }


echo "VERBOSE \"Is anything in here?: ".$agi['arg_1']." \" ";
5

Hello,

Its been a while since I have had time to work on this project…but I have added the testing in there…it gives me the desired outcome through the asterisk CLI:

agent-log.agi|start|4001|1|1227281037: Is anything in here?:
-- AGI Script agent-log.agi completed, returning 0

So the information from asterisk is being passed - it looks like the actual issue is with the code that GETS the variables or when it tries to write into the database.

6

$sql = ‘INSERT INTO [b]agent_status[/b] (agent, agent_code, log_time) VALUES(’;

Have you noticed the space BEFORE agent_status ? Looks like that’s an error to me.

7

Thanks Raffles - Good find, i removed the white spaces - still no joy. No values entered into the DB.

8

Put an echo ‘$sql\n\n’; command just before the ‘mysql_query($sql)’ line.

Oh - and try not to use " to surround the data - use ’ instead (eg. [color=green]INSERT INTO table (field1, field2, field3) VALUES (‘val1’, ‘val2’, ‘val3’);[/color]).

EDIT - Just noticed - in your original code you have ’ after VALUES - but you finish the line with a ".