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. 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 http://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 http://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.
