Retrieve data from other tables with query loop

[pburki]

Hello,

I have a plugin that stores info into a specific database table. Is this possible to use the query loop builder to retrieve info from that table ?

Thanks

Philippe

[Patric]

Hi

yes, it is possible, but you have to code the database access / retrieve yourself.

See this:

Custom SQL (external database) for Query Loop

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.

Cheers

Patric

[pburki]

Hi Patric,

Thanks for your answer. I looked into the articles, but this is fairly complexe for me, since I am not a developer. Would you be able to help me customise the script?

I am trying to retrieve the data from a database table named “participants_database”. I would like to retrieve ‘first_name’ and ‘last_name’ for people in a specific ‘group’ and order the result by ‘last_name’.

I would really appreciate your help.

Thanks and best regards,

Philippe

[Patric]

Ok Philippe

…only because today is the Championsleague Final and I want Haaland to score… But I am not a programmer, so this is the best I can do.

Let’s assume that in WP I have a database called “participants_database” with the fields “first_name” and “last_name”:

To get this data into Bricks via a Custom Query Loop:

1. Put this code snippet into a WPCodebox snippet or into functions.php…

<?php 
add_filter( 'bricks/setup/control_options', function( $control_options ) {
    $control_options['queryTypes']['participants'] = esc_html__( 'Participants', 'my-plugin' );
    return $control_options;
} );

add_filter( 'bricks/query/run', function( $results, $query_obj ) {
    if ( $query_obj->object_type !== 'participants' ) {
        return $results;
    }
    $settings = $query_obj->settings;
   // $participants = array();
// Here we retrieve the database data 
    global $wpdb;
    $i = 0;
    $obj = [];
    $result = $wpdb->get_results (
            "
           SELECT * FROM 
           participants_database ORDER BY last_name ASC
           "
            );
   
       if ( $wpdb->last_error ) {
  echo 'wpdb error: ' . $wpdb->last_error;
}
  
 foreach ($result as $row) {
    $participant[$i]['last_name'] = $row->last_name; 
    $participant[$i]['first_name'] = $row->first_name;
    $i++;
    
   }
    
     return [
        [
           'participant' => $participant,
             ],
    ];    
    
    return $loop_object;
}, 10, 3 );
 

Create a new page in Bricks and create this structure (a section, a container below, a Div below, a code element below):

In the Div, enable the query loop and you should now see “Participants” as a query option. Select it:

In the code element, enter this code:

<?php
 $loop_object = \Bricks\Query::get_loop_object();
 if ( ! $loop_object ) return false;
 if ( ! is_array( $loop_object ) ) return false;
 if ( ! array_key_exists( 'participant', $loop_object ) ) return false;
 $participants = $loop_object['participant'];
 foreach ( $participants as $participant) {
 echo "<p><b>Firstname: </b>".$participant['first_name']."</p>";
 echo "<p><b>Lastname: </b>".$participant['last_name']."</p>";
 echo "<p><b>Fullname: </b>".$participant['first_name']." ".$participant['last_name']."</p>";
 }
?>

Make sure that “execute code” is enabled:

Your page in Bricks should now show the found data from the database, like in my example:

Cheers

Patric

[Patric]

Hi

here is an alternative way to show the values in Bricks without a code element in the query loop… this enables the formatting via STYLE in Bricks directly (but you still need the code from above for the “Participants” custom query of course).

Put this into a WBCodebox snippet or into functions.php:

function retrieve_participants( $name ) {
    $loop_object = \Bricks\Query::get_loop_object();
 if ( ! $loop_object ) return false;
 if ( ! is_array( $loop_object ) ) return false;
 
 $participants = $loop_object['participant'];
 $i = 0;
 
 if ( $name == 'firstname') {
 foreach ($participants as $row) {
    $participant[$i] = $row['first_name']."<br>";
    $i++;
    }
 return implode(" ",$participant);
 }
 if ( $name == 'lastname') {
 foreach ($participants as $row) {
    $participant[$i]= $row['last_name']."<br>";
    $i++;
    }
 return implode(" ",$participant);
 }
  if ( $name == 'fullname') {
 foreach ($participants as $row) {
    $participant[$i]= $row['first_name']." ".$row['last_name']."<br>";
    $i++;
    }
 return implode(" ",$participant);
 }
}

For example, to show the first names, add in Bricks a Heading Element in the query loop “Participants”:

In the Heading element, enter this dynamic code:

{echo:retrieve_participants(firstname)}

Style the Heading element the way you want:

Bricks now shows the data:

You can use

for first name: {echo:retrieve_participants(firstname)}
for last name: {echo:retrieve_participants(lastname)}
for full name: {echo:retrieve_participants(fullname)}

Or combine them together as you want:

{echo:retrieve_p…(lastname)} {echo:retrieve_p…(firstname)}

Cheers

Patric

[pburki]

Thank you very much Patric, this helped me a lot !
Cheers,
Philippe

[abhijitrabha]

Thanks Patric this works like a charm.

But when I inspect the dom it creates a single loop item.
for eg if I use this ‘{echo:retrieve_participants(firstname)}’ in a H3 and my data table has 5rows then normally it should create five H3 each holding individual ‘firstname’.

Currently it creates a single H3 and concat all the ‘firstname’ within.