AGI to MySQL via PHP

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!

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;
	}
 }

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();
 }
 }

?>

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']." \" "; 

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.

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

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

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