I have searched and searched the forums for an example of this, but I could not find one. I imagine I have missed some nugget of info buried somewhere, but for now I am stumped. Any help is appreciated.
In Bubble I have three things ‘User’, ‘UserOrder’ ‘OrderItem’. A user thing can contain multiple userorders and a userorder can contain multiple orderitems. Pretty basic. However, I need to eventually get this information to another system that is not “thing” based, but rather a standard relational database. In that database, I have also have tables called ‘UserOrder’ and ‘OrderItem’ with OrderItem having a column for idUserOrder which is a foreign key to UserOrder.id Pretty basic relational design.
When a user clicks the ‘submit order’ button in Bubble, I create the appropriate things on the Bubble side and then I used the SQL Connector to do an insert into UserOrder, and that works fine. Next, I need to take the list of OrderItems that the current UserOrder hold and insert a row for each one. This, I can’t figure out how to do in Bubble.
In other systems I would be able to Do a For/Each and call an InsertOrderItem query each loop OR an INSERT INTO table (value1,value2) SELECT (value1, value2) from … OR etc. etc.
Is there a way to do this in Bubble that is real-time? The most records I will ever have to insert at a time is less than 20. Am I just missing something obvious?
I am not sure I explained myself clearly. I need to insert multiple rows into an external database via the SQL Connector using data from the internal Bubble database. Are you suggesting I can write SQL to read from the Bubble database?
For the record, I was able to accomplish this by creating an API Workflow that calls the SQL Insert statement from the connector. I would still like to know if there is a way to do this without the extra step of creating an API workflow that basically just plays the role of traffic cop.
If you have MySQL version 5.7.8 or later, you could pass in a JSON string and use JSON_EXTRACT to get the field values.
Otherwise the easiest approach would be pass in a delimited string to a stored procedure, have it pull it apart and do the inserts.
Or similarly, pass the string to a function which outputs a set of records to be inserted.
Bumping this up, I would like to know if there is a way to insert multiple rows in MySql with one single workflow hit.
We have a Product List which the users can exclude to not use, As of now we have a flow where the user goes to each product and then click on exclude button to exclude this one by one.
This is a tedious and time consuming way for our users, We would like to know if there is a way where i can send multiple rows at once to sql using sql connector.
I would like execute the below mentioned sql query
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9)
But bubble allows you to send only one value at a time. Can anyone help me with a workaround or solution for the same.
I wasn’t able to figure out how to get a single SQL Insert to create multiple rows at a time. Instead, I used the iterate feature of the List Shifter plugin. I iterated through each row of my list and inserted one row at a time via the SQL connector. Works like a charm for me. Hope that helps.
