Stop using Podio as a general database. If you need to deal with large amounts of data that you will never interact with on a daily basis, use a dedicated database instead. Just because you CAN use Podio for this, does not mean you SHOULD.
Huge data sets in Podio can slow down the system for everyone, and when you need to query your data, it's really slow. On the other hand, you can get a dedicated MySQL server starting from $5/month and performing queries on your data there is nearly instant.
With a little help from ProcFu, you can work with your MySQL database using GlobiFlow in a super-simple way.
Get a Database
We covered this in the previous blog post and are assuming you have an externally usable database connected to ProcFu. If you do not, go over part 1 first.
Make some Tables
To administer your database, you should just use phpMyAdmin - the same place you created your database and user.
For the sake of examples in this post, I have just created a simple table called "mytable"
CREATE TABLE `mytable` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `status` varchar(10) NOT NULL, PRIMARY KEY (`id`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ProcFu currently has 3 scripts to access MySQL. Which one to use depends on what you're trying to get back:
mysql_command.pf- used for commands with no return, like INSERT, UPDATE, DELETE, etc. This script will always come back with "OK" (unless an error has occured).
mysql_query.pf- used for queries that return a row. These are queries like SELECT SUM or SELECT COUNT etc, or SELECT that guarantees only one row like SELECT * LIMIT 1
mysql_array.pf- used for queries that can return multiple rows, like SELECT * etc
To cater for sanitization of input parameters, PF uses question marks as placeholders. Why? Because we NEVER want to insert an un-validated string into a SQL expression. We let MySQL sanitize each parameter for us to keep us and our data safe.
Here are some common examples
INSERT INTO mytable (title, status) VALUES (?, ?)with params
["new item", "foo"]
UPDATE mytable SET status=? WHERE id=?with params
SELECT * FROM mytable WHERE status=?with params
Using from GlobiFlow
The best way to pass these values from GlobiFlow to ProcFu is to use variables. Create one variable for the sql statement, and one for the parameters in JSON.
For mysql_array queries you can then for-each the result and get each individual field with json_decode
Here's a simulation result for the above flow:
And this query took milliseconds instead of seconds, minutes, or in some cases, hours.
Don't forget you always have phpMyAdmin still at your disposal to work with the data interactively, plus you can connect any other service to your data so that it can easily and quickly be shared with all your IT systems that need it, without API's or special programming.
One awesome hack to query your Podio data really quickly is to set up a MySQL sync in GlobiFlow. That way any changes in Podio will be propagated to a MySQL table, and you can then use SQL SELECTs to query this data.
If you did a Search action in Podio using GlobiFlow, it would take a long time (depending on size of your data), but using a MySQL SELECT query, would take only a split second.