{"id":20653,"date":"2016-03-13T03:01:23","date_gmt":"2016-03-12T17:01:23","guid":{"rendered":"http:\/\/www.rjmprogramming.com.au\/ITblog\/?p=20653"},"modified":"2016-05-24T13:13:12","modified_gmt":"2016-05-24T03:13:12","slug":"wordpress-query-monitor-primer-tutorial","status":"publish","type":"post","link":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wordpress-query-monitor-primer-tutorial\/","title":{"rendered":"WordPress Query Monitor Primer Tutorial"},"content":{"rendered":"<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/wordpress\/query_monitor.jpg\"><img decoding=\"async\" style=\"float:left;border: 15px solid pink;\" alt=\"WordPress Query Monitor Primer Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/wordpress\/query_monitor.jpg\" title=\"WordPress Query Monitor Primer Tutorial\"  \/><\/a><p class=\"wp-caption-text\">WordPress Query Monitor Primer Tutorial<\/p><\/div>\n<p>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 <a target=_blank title='SQL DML information from Wikipedia ... thanks' href='https:\/\/en.wikipedia.org\/wiki\/Data_manipulation_language'>DML<\/a> statements for &#8230;<\/p>\n<ul>\n<li>SELECT<\/li>\n<li>INSERT<\/li>\n<li>UPDATE<\/li>\n<li>DELETE<\/li>\n<\/ul>\n<p> &#8230; and with that &#8220;SELECT&#8221; we hone in on today regarding &#8220;SELECT&#8221; SQL statements &#8230;<\/p>\n<ul>\n<li>&#8220;SELECT&#8221; column list<\/li>\n<li>FROM part of statement including its JOIN parts<\/li>\n<li>WHERE part of statement<\/li>\n<li>GROUP BY part of statement<\/li>\n<li>ORDER BY part of statement<\/li>\n<\/ul>\n<p>When we created <a title='WordPress Blog MySql Database PHP Code Tutorial' href='#wbmdpct'>WordPress Blog MySql Database PHP Code Tutorial<\/a> we got in &#8220;under the hood&#8221; of WordPress towards its &#8220;backend&#8221; MySql database parts, to the point where there was a place in the code where we could piece together what the MySql &#8220;SELECT&#8221; SQL DML statement was, and this is where we <b>intervene<\/b> today &#8230;<\/p>\n<p><code><br \/>\n\t\t$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-&gt;posts.id FROM $wpdb-&gt;posts $join WHERE 1=1 $where $groupby \")))) . localmaybe($orderby, \"  \") . \" $limits\";<br \/>\n            <b>if (file_exists(\"localsearch_\" . server_remote_addr() . \".qsinterest\")) {  \/\/ user interest regarding SELECT query<br \/>\n              $qinterest=@file_get_contents(\"localsearch_\" . server_remote_addr() . \".qsinterest\");<br \/>\n              if (strlen($qinterest) &lt; 3 && strpos($tmp, \"SELECT \") !== false) file_put_contents(\"localsearch_\" . server_remote_addr() . \".qsinterest\", $tmp);<br \/>\n            }<br \/>\n            if (file_exists(\"localsearch_\" . server_remote_addr() . \".qiinterest\")) {  \/\/ user interest regarding INSERT query<br \/>\n              $qinterest=@file_get_contents(\"localsearch_\" . server_remote_addr() . \".qiinterest\");<br \/>\n              if (strlen($qinterest) &lt; 3 && strpos($tmp, \"INSERT \") !== false) file_put_contents(\"localsearch_\" . server_remote_addr() . \".qiinterest\", $tmp);<br \/>\n            }<br \/>\n            if (file_exists(\"localsearch_\" . server_remote_addr() . \".quinterest\")) {  \/\/ user interest regarding UPDATE query<br \/>\n              $qinterest=@file_get_contents(\"localsearch_\" . server_remote_addr() . \".quinterest\");<br \/>\n              if (strlen($qinterest) &lt; 3 && strpos($tmp, \"UPDATE \") !== false) file_put_contents(\"localsearch_\" . server_remote_addr() . \".quinterest\", $tmp);<br \/>\n            }<\/b><br \/>\n<\/code><\/p>\n<p> &#8230; with our idea to show you the MySql SQL involved at any given query point.<\/p>\n<p>So we allow today&#8217;s <a id='mylr' href='#mylr' onclick=\" if (document.URL.indexOf('\/wordpress') == -1) {  window.open('http:\/\/www.rjmprogramming.com.au\/wordpress\/?qm=y','_blank');  }  window.open('http:\/\/www.rjmprogramming.com.au\/wordpress\/query_monitor.php','_blank','top=20,left=20,width=500,height=500'); \" title='Live Run'>live run<\/a> to operate as a separate web browser window monitoring the work you do in other windows for that blog where a &#8220;SELECT&#8221; SQL query happens, and you&#8217;ve clicked the button to say you are interested. Here is its PHP source code you could call <a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/wordpress\/query_monitor.php_GETME\" title='query_monitor.php'>query_monitor.php<\/a> 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&#8217;s theme&#8217;s wp-includes\/query.php that is changed with the code snippet you see above.<\/p>\n<hr>\n<p id='wbmdpct'>Previous relevant <a target=_blank title='WordPress Blog MySql Database PHP Code Tutorial' href='https:\/\/www.rjmprogramming.com.au\/ITblog\/wordpress-blog-mysql-database-php-code-tutorial\/'>WordPress Blog MySql Database PHP Code Tutorial<\/a> is shown below.<\/p>\n<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/wordpress\/query_php.jpg\"><img decoding=\"async\" style=\"float:left; border: 15px solid pink;\" alt=\"WordPress Blog MySql Database PHP Code Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/wordpress\/query_php.jpg\" title=\"WordPress Blog MySql Database PHP Code Tutorial\"   \/><\/a><p class=\"wp-caption-text\">WordPress Blog MySql Database PHP Code Tutorial<\/p><\/div>\n<p>Were you here yesterday for the <a target=_blank title='WordPress Blog Alphabetical Search Follow Up Tutorial' href='https:\/\/www.rjmprogramming.com.au\/ITblog\/wordpress-blog-alphabetical-search-follow-up-tutorial\/'>WordPress Blog Alphabetical Search Follow Up Tutorial<\/a>?  Reading it, you may have wondered about why the problem was not handled from the &#8220;backroom up&#8221;, if you see what we mean.  We also wonder about this with our WordPress 4.1.1 version of blog posting called <a target=_blank title='WordPress Blog MySql Database PHP Code Tutorial' href='https:\/\/www.rjmprogramming.com.au\/ITblog\/wordpress-blog-mysql-database-php-code-tutorial\/'>WordPress Blog MySql Database PHP Code Tutorial<\/a>.  By that we mean, the word &#8220;backroom&#8221; is a metaphor for &#8220;database&#8221;, and we wanted to use a metaphor &#8230; okay?   Zsseeeeesh!<\/p>\n<p>It is true that you can do this, but would recommend following WordPress Codex ideas, and will suggest a link <a target=_blank href='https:\/\/codex.wordpress.org\/Function_Reference\/query_posts' title='WordPress Codex link for query_posts' href='https:\/\/codex.wordpress.org\/Function_Reference\/query_posts'>here<\/a> that you can work &#8220;in and out from&#8221; &#8230; it&#8217;s a metaphor &#8230; ok?! &#8230;  Zsssseeeeeeeeessssh!<\/p>\n<p>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 &#8230;<\/p>\n<ul>\n<li>Apache\/PHP\/MySql web hosting is likely to have an interface to the MySql database via <a target=_blank title='phpMyAdmin' href='https:\/\/www.phpmyadmin.net\/' title='phpMyAdmin home page'>phpMyAdmin<\/a> &#8230; or should we say the marvellous, wonderful, stupendous phpMyAdmin? &#8230; getting to know this product is very useful<\/li>\n<li>WordPress query.php code in wp-includes directory is a place to look for some of the (MySql) SQL queries and their implementation<\/li>\n<\/ul>\n<p>We concentrate heavily, here, on changes to the WordPress Blog TwentyTen theme&#8217;s header.php, but we have also made changes to this query.php in the past.  For this we followed the wonderful advice from <a target=_blank href='http:\/\/www.web0.at\/sql_calc_found_rows-slow-in-wordpress-solution-with-working-pageination\/' title='Useful link ... thanks'>this useful link<\/a> for which we give hearty thanks.  The query.php code changed as per &#8230;<\/p>\n<p><code><br \/>\n$ diff query.php_old query.php<br \/>\n2412c2412,2426<br \/>\n&lt; \t\t$this-&gt;request = \" SELECT $found_rows $distinct $fields FROM $wpdb-&gt;posts $join WHERE 1=1 $where $groupby $orderby $limits\";<br \/>\n---<br \/>\n&gt;         \/\/ Was ...<br \/>\n&gt; \t\t\/\/ $this-&gt;request = \" SELECT $found_rows $distinct $fields FROM $wpdb-&gt;posts $join WHERE 1=1 $where $groupby $orderby $limits\";<br \/>\n&gt;<br \/>\n&gt; \t\t\/\/ Now 20\/11\/2014 ... thanks to http:\/\/www.web0.at\/sql_calc_found_rows-slow-in-wordpress-solution-with-working-pageination\/<br \/>\n&gt; \t\t$tmp = \" SELECT $distinct $wpdb-&gt;posts.id FROM $wpdb-&gt;posts $join WHERE 1=1 $where $groupby $orderby $limits\";<br \/>\n&gt;<br \/>\n&gt;  $tmp_q = $wpdb-&gt;get_col($tmp);<br \/>\n&gt;  $tmp_ids = implode($tmp_q,',');<br \/>\n&gt;  $sqlString = \" SELECT $distinct $fields FROM $wpdb-&gt;posts $join WHERE $wpdb-&gt;posts.id IN ($tmp_ids) $groupby $orderby \";<br \/>\n&gt;<br \/>\n&gt;  $sqlString2 = \" SELECT $found_rows $distinct $fields FROM $wpdb-&gt;posts $join WHERE 1=1 $where $groupby $orderby $limits\";<br \/>\n&gt;<br \/>\n&gt;  $this-&gt;request = $sqlString;<br \/>\n&gt;         \/\/ ... end of new code<br \/>\n&gt;<br \/>\n2435c2449,2453<br \/>\n&lt; \t\t\t$found_posts_query = apply_filters_ref_array( 'found_posts_query', array( 'SELECT FOUND_ROWS()', &$this ) );<br \/>\n---<br \/>\n&gt; \t\t    \/\/ Was ...<br \/>\n&gt; \t\t\t\/\/ $found_posts_query = apply_filters_ref_array( 'found_posts_query', array( 'SELECT FOUND_ROWS()', &$this ) );<br \/>\n&gt; \t\t\t\/\/ Now ... 20\/11\/2014 ... thanks to http:\/\/www.web0.at\/sql_calc_found_rows-slow-in-wordpress-solution-with-working-pageination\/<br \/>\n&gt;  $found_posts_query = apply_filters_ref_array( 'found_posts_query', array( \"SELECT count($distinct $wpdb-&gt;posts.id) FROM $wpdb-&gt;posts $join WHERE 1=1 $where\", &$this ) );<br \/>\n&gt;             \/\/ ... end of change<br \/>\n<\/code><\/p>\n<p>So what&#8217;s the story? &#8230; the tale? &#8230; it&#8217;s a simile &#8230; Zsssssseeeeeeeeeeeessssssh!<\/p>\n<p>Well, it&#8217;s that with all things to do with the database, proceed carefully, and read up on it, especially in <a target=_blank title='WordPress Codex' href='https:\/\/codex.wordpress.org\/'>WordPress Codex<\/a>, 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.<\/p>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d16779' onclick='var dv=document.getElementById(\"d16779\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"https:\/\/www.rjmprogramming.com.au\/ITblog\/?tag=mysql\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d16779' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n<hr>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d20653' onclick='var dv=document.getElementById(\"d20653\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"https:\/\/www.rjmprogramming.com.au\/ITblog\/tag\/database\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d20653' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n","protected":false},"excerpt":{"rendered":"<p>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 &hellip; <a href=\"https:\/\/www.rjmprogramming.com.au\/ITblog\/wordpress-query-monitor-primer-tutorial\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,12,37],"tags":[151,1887,299,1576,827,932,997,1018,1186,1319,1324,1325,1456],"class_list":["post-20653","post","type-post","status-publish","format-standard","hentry","category-database","category-elearning","category-tutorials","tag-blog","tag-codex","tag-database-2","tag-dml","tag-mysql","tag-php","tag-programming","tag-query","tag-sql","tag-tutorial","tag-twentyten","tag-twentyten-theme","tag-wordpress"],"_links":{"self":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/20653"}],"collection":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/comments?post=20653"}],"version-history":[{"count":8,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/20653\/revisions"}],"predecessor-version":[{"id":20686,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/20653\/revisions\/20686"}],"wp:attachment":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/media?parent=20653"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/categories?post=20653"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/tags?post=20653"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}