MySql database pipe

Hi, anyone know if the mysql pipe would allow me to build my pages on top of queries rather than using tadabase tables?

Thank you.

Hey @GaryB I don’t think so but TB could confirm. I think it just allows you to talk to another database that happens to be MySQL

“The MySQL pipe allows you to interact with your MySQL database from your Tadabase app. You can create records, update records, and read records from your database.”

Graham.

Greddie is correct, in that you can " *create records, update records, and read records from your database",
But that means you can also build pages that include both Tadabase data and SQL query data (in separate tables)

For example I have users in our main system (Tadabase) who use our CRM and Training systems that are running on MySql.

I wanted to be able to go to a details page for each user to see their activity/progress across both Tadabase and the MySQL systems.

At first I tried to replicate a copy of the MySQL data to a tadabase table, but that’s a nightmare to keep updated and in sync - I abandonned that approach. Now I bring in the MySQL data ‘on the fly’ via the pipe.

When I go to the Details page for a user in Tadabase it displays the Tadabase details, but also fires Pipes that run custom MySQL queries about that user and returns tables of MySQL data, which are displayed in the same Tadabase ‘details’ page… which is perfect for my needs.

1 Like

I see what you mean, that sounds pretty cool.

Just a picture to show what I mean. This is from a single view in our Tadabase app, but shows data from MySQL

2 Likes

@mtif that is really neat, I can see why that approach is appealing in the scenario you describe. Out of interest, do you use it exclusively for viewing data or have you also managed to update data too?

thanks for sharing, I wouldn’t have considered this approach but this is really nice!

Thanks, I am glad it makes sense to you too.

In fact I moved on from using the standard MySQL pipe. After making a couple of long posts about it (look under MySQL) @moe showed me how to create my own custom MySQL pipe and this has been a revelation.

The original pipe is a good start but only has a few limited queries.
Using the custom pipe I can run run any query I want - SELECT/UPDATE/CREATE. I create these in the standard MySQL Workbench and paste them as a new API Call in the custom pipe.

NB: For both the standard Pipe and my Custom pipe the toughest part for me was figuring our what to do with the results. These arrive as a JSON array. Not being a coder I had to lean on Chat GPT to help me create the javascript needed to render as standard tables on the page - but I am pleased with the result.

1 Like

Excellent - ChatGPT is my friend too :slight_smile: I use it regularly and it’s such a great aid to learning, it really helps with so much, Id encourage anyone on here to try it out if you haven’t already.

Sounds like you’re enjoying the journey too - I’m on a similar one myself :slight_smile:

Keep us posted…and thanks again for sharing.

Very nice work. Thanks for sharing Gary. I think you are the subject matter expert for this kind of challenge. Might need your advise in the future.

@mtif , @GaryB, @GREDDIE

For anyone interested in the video I had shared with Martin here you go:

1 Like

Thanks Moe
For anyone interested in connecting to MySQL I recommend this approach. I cannot express how flexible it has turned out to be and how liberating it has been on our development of our App.
We have now developed over 30 live queries and two way updates that previously would have been running via Zapier/Make or were not possible.

I thought I would add some screen shots and few hints I picked up on the way…

I use the HTML component type and place my Pipe response into a DIV called ‘jsonContainerX’ where X is a variable. And I include this javascript. It works for me, but I am aware that an expert could improve it.

The resulting output is a properly formed HTML table with your data, from MySQL to your Tadabase app.

// THIS will run x number of times, to capture each successive DIV called jsonContainer1...jsonContainerx
function renderComponentWithIndex(index) {
    // Render component_3 and wait for it to load ... CHANGE the Component_ID to suit the page
    TB.render('component_4', function(data) {
        // Wait for 1/2 second
        setTimeout(function() {
            // Get the JSON data from the div
            var jsonContainerId = "jsonContainer" + index;
            var jsonContainer = document.getElementById(jsonContainerId);

            if (jsonContainer) {
                var jsonData = jsonContainer.textContent.trim();

                try {
                    // Parse the JSON data
                    var dataArray = JSON.parse(jsonData);

                    // Create an HTML table NB : THERE is CSS for this table too
                    var table = document.createElement("table");

                    // Create table header
                    var thead = document.createElement("thead");
                    var headerRow = document.createElement("tr");
                    for (var key in dataArray[0]) { // Assuming all objects have the same keys
                        var th = document.createElement("th");
                        th.textContent = key;
                        headerRow.appendChild(th);
                    }
                    thead.appendChild(headerRow);
                    table.appendChild(thead);

                    // Create table body
                    var tbody = document.createElement("tbody");
                    dataArray.forEach(function(data) {
                        var dataRow = document.createElement("tr");
                        for (var key in data) {
                            var td = document.createElement("td");
                            td.textContent = data[key];
                            dataRow.appendChild(td);
                        }
                        tbody.appendChild(dataRow);
                    });
                    table.appendChild(tbody);

                    // Append the table to the container
                    jsonContainer.innerHTML = "";
                    jsonContainer.appendChild(table);

                } catch (error) {
                    console.error("Error parsing JSON:", error);
                }
            } else {
                console.error("jsonContainer not found.");
            }
        }, 500); // 3000 milliseconds = 1/2 second
    });
}