{"id":16779,"date":"2015-08-25T05:01:03","date_gmt":"2015-08-24T19:01:03","guid":{"rendered":"http:\/\/www.rjmprogramming.com.au\/ITblog\/?p=16779"},"modified":"2016-05-24T13:14:38","modified_gmt":"2016-05-24T03:14:38","slug":"wordpress-blog-mysql-database-php-code-tutorial","status":"publish","type":"post","link":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wordpress-blog-mysql-database-php-code-tutorial\/","title":{"rendered":"WordPress Blog MySql Database PHP Code Tutorial"},"content":{"rendered":"<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.  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","protected":false},"excerpt":{"rendered":"<p>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 &#8220;backroom up&#8221;, if you see what we mean. By that we mean, &hellip; <a href=\"https:\/\/www.rjmprogramming.com.au\/ITblog\/wordpress-blog-mysql-database-php-code-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,827,932,997,1186,1319,1672,1456],"class_list":["post-16779","post","type-post","status-publish","format-standard","hentry","category-database","category-elearning","category-tutorials","tag-blog","tag-codex","tag-database-2","tag-mysql","tag-php","tag-programming","tag-sql","tag-tutorial","tag-twentyten-twentyten-theme","tag-wordpress"],"_links":{"self":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/16779"}],"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=16779"}],"version-history":[{"count":2,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/16779\/revisions"}],"predecessor-version":[{"id":16781,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/16779\/revisions\/16781"}],"wp:attachment":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/media?parent=16779"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/categories?post=16779"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/tags?post=16779"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}