2

I have a simple shortcode which parses "user" id from URL and loads specific data from another mysql db for that user inside one custom page:

$sql_u = 'user';
$sql_p = 'password';
$sql_db = 'database';
$sql_ip = '10.10.10.10';

function fetch_data_func ($atts) {
 global $wpdb,$sql_u,$sql_p,$sql_db,$sql_ip;
 $reg = new wpdb($sql_u,$sql_p,$sql_db,$sql_ip);
 $user = get_query_var('user');
 $result = $reg->get_row($wpdb->prepare("SELECT * from data_table WHERE url_id=%s",$user));
 return $result->{$atts['text']};
}
add_shortcode('fetch_data', 'fetch_data_func');

And this is how I call this shortcode:

Name: [fetch_data text="name"]
Some random text or other WP blocks
Address: [fetch_data text="address"]
Some random text or other WP blocks
City: [fetch_data text="city"]
etc.

I have about 20 columns in that specific mysql db for each user which means about 20 shortcode calls on various parts of the same page in wordpress.

For obvious reasons, when calling the shortcode multiple times inside one single page, loading times drastically increase. I believe with each shortcode call, a new query to database is performed. So, when calling the shortcode 20 times, then 20 queries are made, always fetching the same data and this means (in my case) about 10 secs of page loading on pretty decent apache server.

My question is, how would it be even possible to modify the shortcode so it will actually perform the query only once and fetch all data needed inside "get_row" function and then just use this shortcode inside one single page multiple times without performing any additional new queries each time. Is this even doable in that kind of form or should I look for another approach? I also tested this with get_var and multiple shortcodes, but the result is the same, slow. I'm actually lost here.

Or maybe even better question, would it be possible to call this shortcode multiple times from various parts of the same page without performing a sql query each time again?

Thank you in advance so much for any kind of help!

2

PHP and many other languages have a special construct to combine state an behavior – because that's what you want. That construct is called an object.

Objects are defined by classes. So you need a class that remembers the current user data (the state) and delivers the parts that you need (the behavior). This can be very simple. Create a new PHP file named UserData.php and include it right before you register the shortcode.

The content of the file should look similar to this:

namespace WPSE;

class UserData
{
    private $user, $sql_u, $sql_p, $sql_db, $sql_ip;

    public function __construct( $sql_u, $sql_p, $sql_db, $sql_ip )
    {
        $this->sql_u = $sql_u;
        $this->sql_u = $sql_p;
        $this->sql_u = $sql_db;
        $this->sql_u = $sql_ip;
    }

    public function fetch( $atts )
    {
        if ( empty ( $this->user ) {
            $this->fetch_user();
        }
        return $this->user->{$atts['text']};
    }

    private function fetch_user()
    {
        $reg         = new \wpdb( 
                           $this->sql_u, 
                           $this->sql_p, 
                           $this->sql_db, 
                           $this->sql_ip
                       );
        $user_id     = get_query_var('user');
        $this->user  = $reg->get_row(
            $reg->prepare(
                "SELECT * from data_table WHERE url_id=%s",
                $user_id
                )
        );
    }
}

And then you register the shortcode with the namespace:

include_once ('UserData.php');
$user_data = new \WPSE\UserData( $sql_u, $sql_p, $sql_db, $sql_ip );
add_shortcode( 'fetch_data', [ $user_data, 'fetch' ] );

Note that this is completely untested. I just want to give you an idea on how to handle cases like this one.

Also, welcome to WordPress Stack Exchange! :)

| improve this answer | |
  • Thank you so much for your answer. By reading the description, I believe this is exactly the right approach for situations like this. The only issue I have is, that after some hours of "experimenting", I, for the love of god, am unable to make it work. I will update my first post if I may with the exact code I currently have and I'd kindly like to ask you to point me into the right direction, where I'm making mistakes. – futurion Feb 19 at 8:38
  • @futurion My bad, I posted the wrong shortcode registration code. Please see my update, and try again. :) – fuxia Feb 19 at 8:50
  • 1
    I can't tell you how grateful I am, it works, and it works blazingly fast. Loading 1 or 100 variables now takes the exact same amount of time. Thank you so much, this really is an amazing approach! There are two minor issues still in your code. First is, when registering shortcode, you should actually call 'shortcode' or change the name of public function to fetch_data_func. And the second issue is that for some reason I have to define global variables inside private function fetch_user(), otherwise there's no sql output. Should I post an answer with updated code, or should you change yours? – futurion Feb 19 at 9:42
  • @futurion Your edit was fine and exactly the way to go. :) However, the class is instantiated with the SQL variables already, so I made an edit to use them, so you don't have to use globals. – fuxia Feb 19 at 10:03
  • I tried with your latest edit without global variables and it won't work. It seems there has to be at least "global $wpdb;" otherwise I get an error in apache log (PHP Fatal error: Uncaught Error: Call to a member function prepare() on null). Also, when using variables as you suggested ($this->variable) I don't get any sql results, only blank screen. It seems as the variable value isn't read in this case. The option with defining global variables still works ok though. I have all variables defined inside functions.php as $sql_u = 'user'; etc. Anything else I could try maybe? – futurion Feb 19 at 10:49
0

You can use commas to separate the parameters and display the data once. This way you can pick whichever fields you want to show.

[fetch_data fields="First Name:first_name,Last Name:last_name,City:city"]

This is the shortcode function:

add_shortcode('fetch_data', 'fetch_data_func');

function fetch_data_func($attr){
    $user_arr = get_user_data_from_db();//use your own code

    //Split by comma then by colon
    $fields = explode(',', $attr['fields']);
    $fields = array_map(function($item){
        $field = explode(':', $item);
        return array( 'label'=>$field[0],'name'=>$field[1] );
    }, $fields);

    //Use Output Buffering so you can include template here
    ob_start();
    foreach ($fields as $field) {
        echo $field['label'] . ': ' . $user_arr[$field['name']] . '<br>';
        //Will show like this 
        //First name: John
        //City: New York
    }
    return ob_get_clean();
}
| improve this answer | |
  • Thank you for your answer. However, I believe this would only work in case when all fields would be displayed at once with one single shortcode call. The issue is, I'm using these fields on various places inside my page. Meaning, I have some text, then I use field [name], then I have also some random text, blocks and other wp elements and then I display field [address] and so on. In each case, I have to call the shortcode multiple times so I believe a suggestion @fuxia has made in this case is actually a point into right direction. Or am I wrong here? – futurion Feb 19 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.