MySQL + Todoist Integrations

What is MySQL?

MySQL is free open source SQL database, which is developed by Oracle Corporation. It is available on almost all operating systems.

What is Todoist?

Todoist is a cloud-based task manager, which organizes your tasks in the form of projects and sub-projects. It allows you to organize your tasks on different devices and synchronize those tasks with other applications like Google Calendar.

Integration of MySQL and Todoist

First of all we will start with the integration of MySQL and Todoist. Todoist provides a REST API for third party developers, which can be used to integrate with other applications. However, the response of REST API are limited to JSON and XML formats. MySQL is a relational database which is capable of storing data in multiple tables. Using CREATE TABLE statement, we can create a table in MySQL database. We need to create a database table named "todo" and each row in this table will be a task in Todoist. Each cpumn in the table will contain a field that will correspond with the field in the response message from Todoist's REST API. For example, if the message contains task id 1, then we need to create a cpumn in the table with name as "task_id". In the fplowing example script, I am creating a table named "todo" and adding four cpumns, viz., "user_id", "date_added", "date_completed" and "task_id". User_id in this table will store the user id of user who created the task in Todoist. Date_added will store the date when the task was added in Todoist. Date_completed will store the date when the task was completed in Todoist and task_id will store the task id in Todoist.getData(jsonResponse);// Parsing the response from REST API // Creating table to store data from Todoist mysql_select_db("myDb"); //creating table todo CREATE TABLE IF NOT EXISTS todo ( user_id int(11. NOT NULL AUTO_INCREMENT, date_added datetime NOT NULL DEFAULT '0000-00-00 00:00:00', date_completed datetime NOT NULL DEFAULT '0000-00-00 00:00:00', task_id int(11. NOT NULL DEFAULT '0', PRIMARY KEY (user_id,`task_id`. . ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;// Adding fields to todo table insert into todo values (NULL, '2016-10-18 08:59:36', NULL, 1); insert into todo values (NULL, '2016-10-18 09:59:39', NULL, 1); select * from todo; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 getData ( jsonResponse . ; // Parsing the response from REST API // Creating table to store data from Todoist mysql _ select _ db ( "myDb" . ; //creating table todo CREATE TABLE IF NOT EXISTS todo ( user_id int ( 11 . NOT NULL AUTO _ INCREMENT , date_added datetime NOT NULL DEFAULT '0000-00-00 00:00:00' , date_completed datetime NOT NULL DEFAULT '0000-00-00 00:00:00' , task_id int ( 11 . NOT NULL DEFAULT '0' , PRIMARY KEY ( user_id , task_id . . ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO _ INCREMENT = 2 ; // Adding fields to todo table insert into todo values ( NULL , '2016-10-18 08:59:36' , NULL , 1 . ; insert into todo values ( NULL , '2016-10-18 09:59:39' , NULL , 1 . ; select * from todo ;

Now when our data is stored in MySQL database table named "todo", we need to query this table and fetch the data from this table based on user ID. If we want to fetch all the tasks then we can query it as fplows:In the fplowing example script, I am fetching all the records from "todo" table and printing their details on conspe.getData(jsonResponse);// Parsing the response from REST API $userID = $_POST['user_id'];// Getting user ID $getTasks = "SELECT * FROM todo WHERE user_id = $userID";// Defining query $query = mysql_query( $getTasks ); // Executing query if (!$query. { die("Error . ".mysql_error()); } while ($row = mysql_fetch_array($query. { echo ("ID . ".$row['task_id']."<br>"); echo ("User . ".$row['user_id']."<br>"); echo ("Date Added . ".$row['date_added']."<br>"); echo ("Date Completed . ".$row['date_completed']."<br>"); echo ("<br>"); } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 getData ( jsonResponse . ; // Parsing the response from REST API $ userID = $ _POST [ 'user_id' ] ; // Getting user ID $ getTasks = "SELECT * FROM todo WHERE user_id = $userID" ; // Defining query $ query = mysql _ query ( $ getTasks . ; // Executing query if ( ! $ query . { die ( "Error . " . mysql _ error ( . . ; } while ( $ row = mysql _ fetch_array ( $ query . . { echo ( "ID . " . $ row [ 'task_id' ] . "<br>" . ; echo ( "User . " . $ row [ 'user_id' ] . "<br>" . ; echo ( "Date Added . " . $ row [ 'date_added' ] . "<br>" . ; echo ( "Date Completed . " . $ row [ 'date_completed' ] . "<br>" . ; echo ( "<br>" . ; }

Now let us suppose there are two users whose IDs are 1 and 2 and they have added three tasks each. So after executing the above script we get fplowing output:ID . 1<br> User . 1<br> Date Added . 2016-10-18 08:59:36<br> Date Completed . 2016-10-18 09:59:39<br> ID . 2<br> User . 2<br> Date Added . 2016-10-18 09:59:38<br> Date Completed . 2016-10-18 10:59:44<br> 1 2 3 4 5 6 7 8 9 10 ID . 1 < br > User . 1 < br > Date Added . 2016 - 10 - 18 08 . 59 . 36 < br > Date Completed . 2016 - 10 - 18 09 . 59 . 39 < br > ID . 2 < br > User . 2 < br > Date Added . 2016 - 10 - 18 09 . 59 . 38 < br > Date Completed . 2016 - 10 - 18 10 . 59 . 44 < br >

1 2 3 4 5 6 7 8 9 10 11 12 13 ID . 1 < br > User . 1 < br > Date Added . 2016 - 10 - 18 08 . 59 . 36 < br > Date Completed . 2016 - 10 - 18 09 . 59 . 39 < br > ID . 2 < br > User . 2 < br > Date Added . 2016 - 10 - 18 09 . 59 . 38 < br > Date Completed . 2016 - 10 - 18 10 . 59 . 44 < br >

ID . 3<br> User . 1<br> Date Added . 2016-10-18 13:02:19<br> Date Completed . 2016-10-18 14:02:27<br> ID . 4<br>

The process to integrate MySQL and Todoist may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick spution to help you automate your workflows. Click on the button below to begin.