Dialogflow Tutorials: Integrate Dialogflow with MySQL Database

[Laughter] [Music] hello friends in this video tutorial we are going to see how to integrate your dialogue for chatbot with mysql so if you have Hostgator or GoDaddy then you might already have MySQL database in your control panels so we will see how to integrate your MySQL database with dialogue flow okay so for let's login into our dialog flow console and if you have Hostgator or GoDaddy then just login into that and you will see PHP my admin ok now click on PHP myadmin and you will see your database so I have created a database called unsure dialogue flow demo ok and I have created a table called users and I have just added some random data here so let's see how to connect this - your database with our dialog flow chat board and how to retrieve this data into our chat bot so first step is to login into your dialog flow console and let's create an intent and let's call it get data from MySQL and for now just add one training phase called MySQL ok now let's add a parameter called email [Music] and let's enable the fulfillment so that we can connect this with our code okay so the idea here is we will ask for the email and for that email we will fetch the data from the database okay so let's save it now we need to go into the fulfillment section and now we need to write the code to connect dialogue flow with about MySQL database so the first step is to install the MySQL NPM package so to do that just google NPM I ask you and click on this link and just copy this version number we will need this and here just write MySQL and the version number so MySQL and version number is 2.72 dot one it might be changed for you but whatever you find here just copy and paste here right so this is in package store Jason now go back to index dot Jess and let's create our new intent [Music] and the name of the right so the name of the intent should be exactly same yeah this is the name of the intent that we have created and let's create a new handler function handle read from my SQL now let's create this function handle read from MySQL so this function will read our data from the MySQL database okay all right so you have created intent map entered the name of the intent and this is the function okay now let's write some code so first thing here import our MySQL library require my secure okay now here we need to create another function called connect to database okay so this function will connect to our MySQL database so let's see what should be right here we write connection is equal to MySQL dot three eight connection okay [Music] and then host user password and database so here you need to specify the host you need to specify the username you need to specify the password and finally you need to specify the database which database you are going to connect so where do we get all these details form so if you go to your control panel you will see the site IP so you can use this site IP as your hostname just copy this and go to your dialog flow and paste it now username is something you can create an user so if you go to MySQL databases you can create your own user so you will see an option to create a new user so you can create yourself and if you have user then just use those details so I will add my user and I will add my password and I will add my database [Music] okay so this is our connection now the next step is to return new promise [Music] [Music] now you will write connection dot connect and then finally resolve connection [Music] so this is the code to connect to database okay now we need to write another function to query the database so we'll rewrite query database so this function was used to connect to database this function will be used to query the database and we will pass the connection in this function okay and this will also be a promise based function so return new promise resolve Richard okay and in this the right connection dot query and your SQL and here you will write a callback function which will take three parameters error results and fields and here you will try to resolve results so in here you will need to write your query so for now we will just write select star from users so we have our table called users will just write select star from users now we will use these two functions in our handle read from my SQL code so this function will take agent now let's right connect to database dot then [Music] we'll get the connection we will use this connection [Music] then returned query data base connection dart and so now we will call this function query database and we'll pass this connection and then we'll get result finally [Music] for now let's just console dot log the result just to see if we are getting the result back or not okay so connect to database then get the connection then query database and then get the result and then console log the result so and then finally connection dot and just to close the connection okay now let's deploy this all right so the deployment is successful now let's test it so just write MySQL please provide an email we can provide an email because right now we are not using it and she let email dot-com and click on view execution logs all right and one thing you need to make sure if your make sure your billing account is configured if you have not configured your billing account then in your console you will see this message building account not configured and if you don't know how to configure billing account then click on the I button on the top right corner [Music] [Music] okay so having the console of and you can see I've got the data from the database these are our two data from the database so now what we can do is we can write something like this HN dot add result zero or actually first name [Music] [Music] [Music] right so what we have done is taken the first result and first name is result dog first name last name is result dot last name all right select stride now so if you like my SQL and it asks for email so just write an email for now and there you go can see first name is initial and last name is Srivastava so it got the data from the database and it showed it here so if we just change the data name to test and last name to user and if we try it again [Music] you see first name test last name user so it is getting the data from the database now what we can do is we can write some logic to compare the email address right so we can write something like this result dot map email [Music] so first we need to get the email address of the user agent dot parameter start email this is the email address this one and we can write something like this wizard dot map email let's call it user in here so result dot parameter is normal email let's just do it use a result dot map user user dot and this parameter email so we are map in mapping through all the results and getting each user and we are checking if the email address is same as what user has provided and we can write something like this if so let's do it properly yeah so result map user if user email equal to whatever user has entered if they are same then sure this let's correct our database back to whatever it was before [Music] all right so let's quickly reply it [Music] and also change here to user dot first name and user dot last name so what we are doing here is we are looping through all the users okay and we are checking the email address so if the email address matches with whatever user has provided then we are just printing the first name and last name okay let's quickly deploy it again and see if it works all right so let's try it again okay so last year thus for email so the email we have entered here is uncial at example.com so let's enter the same email and there you go first name ensure last name Srivastava let's try it again for our second user Rahul Verma so MySQL please provide email Rahul at example.com and there you go Rahul and last name Baba so let's quickly add new data just to see if it works as expected so cruise-o-matic calm and another one Shawn small Shawn at example.com okay so now we have got four data Tom Cruise and John small okay so let's try it again - cure email Tom at example.com and there you go so you can see how easy it is to connect your dialog flow with MySQL database and you can see how can we get the data from MySQL database and show it in our chat bot dialogue so chat bot in next video tutorial I will show how to add edit and delete a new data into the MySQL database so I hope you liked this video please click on the like button share this with your friends if you have not subscribed to our channel please subscribe to our channel thank you [Music]

Channel: AxleWeb Technologies

Views: 17,570

Rating: 4.9594593 out of 5

Keywords: chatbot, dialogflow, integrate dialogflow with mysql, integrate dialogflow with database, integrate chatbot with mysql database, integrate dialogflow with mysql database, connect dialogflow with mysql database, mysql dialogflow, mysql chatbot, chatbot with mysql database

Id: v7k5vckSzNo

Length: 21min 21sec (1281 seconds)

Published: Sun Nov 10 2019