Retrieve data from other tables with query loop

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

1 Like

Hi

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

See this:

Cheers

Patric

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

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”:

DB_query0

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):

DB_query1

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

DB_query2

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:

DB_query6

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

Cheers

Patric

3 Likes

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”:

Heading1

In the Heading element, enter this dynamic code:

{echo:retrieve_participants(firstname)}

Style the Heading element the way you want:

Heading3

Bricks now shows the data:

Heading4

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

4 Likes

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