Custom SQL (external database) for Query Loop

How To
1

Is there a way to query an external database and stick the results in a variable, or something, that can then be used in a query loop?

Example: I have a need to create employee lists by department. The data is coming from an external database (Google BigQuery).

What I would like to do is embed the SQL in a code block, have it save the results to a variable, or JSON, or whatever, and then output the results through a query loop.

2

Hi

Yes, that is possible with Bricks.

I use the bricks/ query/run Filter to run my own query loop in which I sql select the required items directly from the database.

I then process the sql data and return an array consisting of the data.

Works perfectly.

See this for more info

Cheers

Patric

1 Like
3

Thanks. I saw that, but I’m not clear if it will allow me to query an external database on an external server. Can you confirm?

4

Hi

If you can reach the server / sql database via php, I see no reason why not.

For another project, I wrote php code within Bricks that loaded sql data from another mariadb „outside“ of the mariadb that the WordPress and code was running from.

Cheers

Patric

5

Thank you much. I will dig into this and see if I can’t make it work.

6

My approach would be to first write the php code that connects to the database and then loads the required data.

Once this works, you copy that into the query filter and make sure that the filter returns the array containing the data.

My code is very specific to my use case (I access 3 databases at the same time and combine data from all 3 together with a very specific logic) and I also do a lot of data preparation & modification in it.

So I fear it would confuse you more than help you if I gave you my code.

That’s how I did it.

Cheers

Patric

7

No worries. Thanks much for that last message. I need to pull information from a Google BigQuery. It sounds like it’s not too different than what you are doing, so that tells me it’s possible. I’ll get the specifics figured out. Just knowing it’s definitely possible tells me enough.

8

Hi

This video helped me a lot:

Cheers

Patric

9

…and of course this one, including some code.