WordPress Query Monitor Primer Tutorial

WordPress Query Monitor Primer Tutorial

WordPress Query Monitor Primer Tutorial

Our WordPress blog at RJM Programming uses a MySql database, and its PHP web server source queries that database to find information. That SQL used is the MySql variety of SQL, but there is not that much difference between MySql SQL or the T-SQL of MS-SQL or Oracle SQL or PostGRE SQL or Access SQL or Advantage SQL when it comes down to the basics of DML statements for …

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

… and with that “SELECT” we hone in on today regarding “SELECT” SQL statements …

  • “SELECT” column list
  • FROM part of statement including its JOIN parts
  • WHERE part of statement
  • GROUP BY part of statement
  • ORDER BY part of statement

When we created WordPress Blog MySql Database PHP Code Tutorial we got in “under the hood” of WordPress towards its “backend” MySql database parts, to the point where there was a place in the code where we could piece together what the MySql “SELECT” SQL DML statement was, and this is where we intervene today …


$tmp = str_replace("ORDER BY post_title ASC AND", " AND ", str_replace("ORDER BY post_title DESC AND", " AND ", str_replace("1=1 post_title AND", "1=1 AND", str_replace("post_title AND wp_posts.post_type", " AND wp_posts.post_type", " SELECT $distinct $wpdb->posts.id FROM $wpdb->posts $join WHERE 1=1 $where $groupby ")))) . localmaybe($orderby, " ") . " $limits";
if (file_exists("localsearch_" . server_remote_addr() . ".qsinterest")) { // user interest regarding SELECT query
$qinterest=@file_get_contents("localsearch_" . server_remote_addr() . ".qsinterest");
if (strlen($qinterest) < 3 && strpos($tmp, "SELECT ") !== false) file_put_contents("localsearch_" . server_remote_addr() . ".qsinterest", $tmp);
}
if (file_exists("localsearch_" . server_remote_addr() . ".qiinterest")) { // user interest regarding INSERT query
$qinterest=@file_get_contents("localsearch_" . server_remote_addr() . ".qiinterest");
if (strlen($qinterest) < 3 && strpos($tmp, "INSERT ") !== false) file_put_contents("localsearch_" . server_remote_addr() . ".qiinterest", $tmp);
}
if (file_exists("localsearch_" . server_remote_addr() . ".quinterest")) { // user interest regarding UPDATE query
$qinterest=@file_get_contents("localsearch_" . server_remote_addr() . ".quinterest");
if (strlen($qinterest) < 3 && strpos($tmp, "UPDATE ") !== false) file_put_contents("localsearch_" . server_remote_addr() . ".quinterest", $tmp);
}

… with our idea to show you the MySql SQL involved at any given query point.

So we allow today’s live run to operate as a separate web browser window monitoring the work you do in other windows for that blog where a “SELECT” SQL query happens, and you’ve clicked the button to say you are interested. Here is its PHP source code you could call query_monitor.php that should be placed at the document root of the WordPress blog and is specific to the Twenty Ten theme we use with our WordPress blog in the sense that it is the TwentyTen’s theme’s wp-includes/query.php that is changed with the code snippet you see above.


Previous relevant WordPress Blog MySql Database PHP Code Tutorial is shown below.

WordPress Blog MySql Database PHP Code Tutorial

WordPress Blog MySql Database PHP Code Tutorial

Were you here yesterday for the WordPress Blog Alphabetical Search Follow Up Tutorial? Reading it, you may have wondered about why the problem was not handled from the “backroom up”, if you see what we mean. We also wonder about this with our WordPress 4.1.1 version of blog posting called WordPress Blog MySql Database PHP Code Tutorial. By that we mean, the word “backroom” is a metaphor for “database”, and we wanted to use a metaphor … okay? Zsseeeeesh!

It is true that you can do this, but would recommend following WordPress Codex ideas, and will suggest a link here that you can work “in and out from” … it’s a metaphor … ok?! … Zsssseeeeeeeeessssh!

But seriously, if you want a bit of DIY advice regarding the interface WordPress has, PHP-code-wise, to the MySql database, we have some suggestions …

  • Apache/PHP/MySql web hosting is likely to have an interface to the MySql database via phpMyAdmin … or should we say the marvellous, wonderful, stupendous phpMyAdmin? … getting to know this product is very useful
  • WordPress query.php code in wp-includes directory is a place to look for some of the (MySql) SQL queries and their implementation

We concentrate heavily, here, on changes to the WordPress Blog TwentyTen theme’s header.php, but we have also made changes to this query.php in the past. For this we followed the wonderful advice from this useful link for which we give hearty thanks. The query.php code changed as per …


$ diff query.php_old query.php
2412c2412,2426
< $this->request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
---
> // Was ...
> // $this->request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
>
> // Now 20/11/2014 ... thanks to //www.web0.at/sql_calc_found_rows-slow-in-wordpress-solution-with-working-pageination/
> $tmp = " SELECT $distinct $wpdb->posts.id FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
>
> $tmp_q = $wpdb->get_col($tmp);
> $tmp_ids = implode($tmp_q,',');
> $sqlString = " SELECT $distinct $fields FROM $wpdb->posts $join WHERE $wpdb->posts.id IN ($tmp_ids) $groupby $orderby ";
>
> $sqlString2 = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
>
> $this->request = $sqlString;
> // ... end of new code
>
2435c2449,2453
< $found_posts_query = apply_filters_ref_array( 'found_posts_query', array( 'SELECT FOUND_ROWS()', &$this ) );
---
> // Was ...
> // $found_posts_query = apply_filters_ref_array( 'found_posts_query', array( 'SELECT FOUND_ROWS()', &$this ) );
> // Now ... 20/11/2014 ... thanks to //www.web0.at/sql_calc_found_rows-slow-in-wordpress-solution-with-working-pageination/
> $found_posts_query = apply_filters_ref_array( 'found_posts_query', array( "SELECT count($distinct $wpdb->posts.id) FROM $wpdb->posts $join WHERE 1=1 $where", &$this ) );
> // ... end of change

So what’s the story? … the tale? … it’s a simile … Zsssssseeeeeeeeeeeessssssh!

Well, it’s that with all things to do with the database, proceed carefully, and read up on it, especially in WordPress Codex, before proceeding, perhaps even backing up the database prior to any major changes you make (in PHP) to MySql (in WordPress database). For DIY projects that are readonly ones, bear in mind that there is less danger to what you do. Adjusting SQL SELECT statements that stand alone, and are not part of an ongoing procedure that eventually does any UPDATE nor INSERT nor DELETE to the database, could be good candidates for the use of query.php to attempt a solution of your own to an issue in WordPress for its MySql database, using PHP code.

If this was interesting you may be interested in this too.


If this was interesting you may be interested in this too.

This entry was posted in Database, eLearning, Tutorials and tagged , , , , , , , , , , , , . Bookmark the permalink.

8 Responses to WordPress Query Monitor Primer Tutorial

  1. besi wf beam says:

    some wonderful entropy assisted me a lot thank you for share like this blog

  2. Aw, it was a genuinely nice post. In notion I’ve to place in location writing like this on top of that – spending time and actual work to possess a best notch article… but what can I say… I procrastinate alot as well as by no indicates apparently go carried out.

  3. You completed various nice points there. I did a search on the topic and found most persons will consent with your blog.

  4. Fantastic paintings! This can be the form of info that really should be shared about the online world. Disgrace on search engines for not positioning this place up upper! Come on over and check out my web site . Thanks =)

  5. prosklitiria says:

    I feel that is among the so significantly significant information for me. And i’m pleased studying your article. But really should observation on some normal issues, The web site taste is wonderful, the articles is truly fantastic . Outstanding activity, cheers.

  6. A domain name name, or Internet address, is an deal with exactly where it really is straightforward to be observed online. It’s how you will communicate yourself by indicates of email or your website and it’s which buyers assume of once attempting to discover you.

  7. It’s genuinely a cool and useful piece of details. I’m glad that you shared this useful data with us. Please maintain us informed like this. Thanks for sharing.

  8. This site is usually a walk-through for all the internet it suited you about it and didn’t know who require to. Glimpse here, and you’ll definitely uncover it.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>