PHP MySQL Dialogflow

Question

I'm setting up a chatbot (dialogflow) with a PHP webhook

What I want to do is to take the user input to query a MySQL table and pass the result back to the dialogflow API

So far I succeeded with passing a text string back to the API, but I don't understand how to query the database and pass the result back to the dialogflow API

I'll appreciate your help with this
I've used the API format from the dialogflow docs here

This is what I have

<?php
$method = $_SERVER['REQUEST_METHOD'];
if($method == 'POST') {
$requestBody = file_get_contents('php://input'); 
$json = json_decode($requestBody); 
$text = $json->result->parameters->cities;
$conn = mysqli_connect("xxx", "xxx", "xxx", "xxx"); 
    $sql = "SELECT * FROM exampletable LIKE '%".$_POST["cities"]."%'"; 
    $result = mysqli_query($conn, $sql);
    $emparray = array();
    while($row =mysqli_fetch_assoc($result)) {
        $emparray[] = $row;
    }
$speech = $emparray;
    $response->speech = $speech;
    $response->displayText = $speech;
    $response->source = "webhook";
    echo json_encode(array($response,$emparray));
else
{
    echo "Method not allowed";
}
 ?>

Thankyou

You have SQL injection in your code.. must read https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — Raymond Nijland, Mar 19 '18 at 12:58

score 0 · Accepted Answer · answered Mar 19 '18 at 13:51

whenever the webhook gets triggered you need to listen to actions from JSON responses, from the action made the switch case of actions

index.php

<?php
require 'get_enews.php';

function processMessage($input) {
    $action = $input["result"]["action"];
    switch($action){

        case 'getNews':
            $param = $input["result"]["parameters"]["number"];
            getNews($param);
            break;

        default :
            sendMessage(array(
                "source" => "RMC",
                "speech" => "I am not able to understand. what do you want ?",
                "displayText" => "I am not able to understand. what do you want ?",
                "contextOut" => array()
            ));
    }
}
function sendMessage($parameters) {
    header('Content-Type: application/json');
    $data = str_replace('\/','/',json_encode($parameters));
    echo $data;
}
$input = json_decode(file_get_contents('php://input'), true);
if (isset($input["result"]["action"])) {
    processMessage($input);
}
?>

get_enews.php

<?php
function getNews($param){
    require 'config.php';
    $getNews="";
    $Query="SELECT link FROM public.news WHERE year='$param'";
    $Result=pg_query($con,$Query);
    if(isset($Result) && !empty($Result) && pg_num_rows($Result) > 0){
    $row=pg_fetch_assoc($Result);
    $getNews= "Here is details that you require - Link: " . $row["link"];
        $arr=array(
            "source" => "RMC",
            "speech" => $getNews,
            "displayText" => $getNews,
        );
        sendMessage($arr);
    }else{
        $arr=array(
            "source" => "RMC",
            "speech" => "No year matched in database.",
            "displayText" => "No year matched in database.",
        );
        sendMessage($arr);
    }
}
?>

So when the action gets caught it will get executed and goes in the getNews($param); function here I am getting year as a response from the user in my case and I am executing the query in the database and giving back a response from the database.

dude you are awesome it worked! If you could point me to documentation where I can learn how to figure this out myself I'll appreciate it — Laycoonz, Mar 19 '18 at 14:38
Hello, i am also trying to connect dialogflow with mysql, can you please point me to some tutorial which uses php — user2828442, Oct 15 '18 at 07:13

PHP MySQL Dialogflow

1 Answers1

Linked