Second time I’ve encountered this and it’s frustrating the heck out of me. In AGI files, I cannot use certain formatted php-mysqli statements when passing variables from the dialplan. They pass in, I see them in the console, but the query won’t execute and I can’t pass variables back to the diaplan. I’ve only needed to use this to query remote databases and 11.x, so I don’t know if the behavior exists using a local database or other versions.
Again, I’ve narrowed it down to one minor difference- I can’t use a certain query format if the variables are passed from the dialplan…
To highlight what I’m talking about (for brevity I’ve cut out the database connect line, require php.agi line, numerous lines that handle safe execution, and returning $vars back to the dialplan):
Fail! (using prepared statement & dialplan variables):
[code]$var1 = $argv[1];
$var2 = $argv[2];
$stmt = $con->prepare(“SELECT field FROM table WHERE field1=’?’ AND field2 LIKE ‘%?%’ ORDER BY id DESC LIMIT 1”);
$stmt->bind_param(‘ss’, $var1, $var2);
$stmt->execute();
$stmt->bind_result($field);
$stmt->fetch();
$stmt->close();[/code]
Good! (using same prepared statement & hard coded variables):
[code]$var1 = “abcdefg”;
$var2 = ""123456;
$stmt = $con->prepare(“SELECT field FROM table WHERE field1=’?’ AND field2 LIKE ‘%?%’ ORDER BY id DESC LIMIT 1”);
$stmt->bind_param(‘ss’, $var1, $var2);
$stmt->execute();
$stmt->bind_result($field);
$stmt->fetch();
$stmt->close();[/code]
Always good! (variables passed from the dialplan, but a different query format):
[code]$var1 = $argv[1];
$var2 = $argv[2];
$query = mysqli_query($con,"SELECT field FROM table WHERE field1 = $var1 AND field2 LIKE CONCAT('%', $var2, '%') ORDER BY id DESC LIMIT 1");
$result = mysqli_fetch_assoc($query);
$field = $result['field'];[/code]