MySql Database on Database Join Tutorial
Why might you want to join two tables from different databases of the same type, that being MySql, today? We can think of …
- synchronizing two data sources represented by those two database tables, perhaps one updated, one yet to be updated
- reports that compare databases, or show trends, or show differences
- its a lot like synchronizing, but database backup system may want to do this
We use this technique for WordPress keeping blog postings with the same (posting) ID over two different MySql databases.
So today we delve into WordPress Blog Posting Generic Misspelling Fix Tutorial‘s …
… except that, here at rjmprogramming.com.au we join two databases, the complication of which we’ll spare you
… you will not be spared today!
What MySql SQL keywords are most crucial when you get involved with a join over two or more different MySql databases?
- ON … and though it would have happened even involving just the one database, is still significant, that being …
- JOIN … for us, an inferred and default “INNER JOIN” (ie. the intersection part of a Venn Diagram rather than the non-intersection parts of an “OUTER JOIN”)
… and what often happens in this scenario is that database table aliases become extremely useful to differentiate columns with the same name over two different databases. We have a PHP web application (where $dbaltname and $dbname name those two databases involved below, and strtolower(urldecode($_GET[‘answer’])) represents a user designated word to find in a WordPress Blog posting title) to illustrate this, comparing WordPress Blog post comment counts, the SQL of which is submitted to the MySql engine via …
$res = mysql_query("SELECT t1.ID, t2.ID, t1.post_title, t2.post_title, t1.comment_count, t2.comment_count, t1.post_content, t2.post_content
FROM " . $dbaltname . ".wps_posts t1
ON " . $dbname . ".wp_posts t2
JOIN t2.post_title = t1.post_title and t2.post_status in ('publish','future') and t1.post_status in ('publish','future')
WHERE t2.ID = t1.ID and lower(concat(concat(' ',t1.post_title),' ')) LIKE '% " . strtolower(urldecode($_GET['answer'])) . " %' ");
Again, we can’t show you our exact PHP code today, but we can show you a one database one post table scenario PHP old_new_wp_comment_count.php for your perusal, and perhaps download, as well as you being able to try it in live run form.
Previous relevant WordPress Blog Posting Generic Misspelling Fix Tutorial is shown below.
WordPress Blog Posting Generic Misspelling Fix Tutorial
Let’s start out as “devil’s advocate” for today’s WordPress Generic Misspelling Fix project, that has a large amount of synergy with the “content” emphasis of WordPress Blog Posting Thread Content Summary Tutorial.
What are some things to beware of when applying any generic replacement logic for a [fromText] to [toText] scenario …
- if you are not willing to directly check for all mentions of [fromText] that will be replaced consider …
- will be case sensitive, and if the misspelling starts a sentence any fully lowercase entry will not find these
- any genuine misspelling you are fixing some absolute URL may have misspelled the same way, and you then create a broken link
- you meant to misspell
- is it worth the bother, or with only a few finds, wouldn’t it be better to visit every case … the motive for today’s interest to us did not fit into this category … we had about 20 misspellings of “featurng” (see, we deliberately misspelt, but luckily the PDF slideshow was from earlier on in the day versus on a rerun we could add a preceeding space to this one, probably … get what we mean though?!) … curious, huh?
- can it appear within the proper syntax of the “outerHTML” parts of the underlying HTML, and by changing it, you “break” some HTML that was previous working
So if you are still here interested, what approach are we using here? We are going to construct some MySql SQL via PHP to …
- access the WordPress MySql database via a connection …
$hostname = 'database_hostname';/*** mysql username ***/
$username = 'mysql_username';/*** mysql ***/
$password = 'mysql_username_password';$dbname = 'database_name';
$link = mysql_connect($hostname, $username, $password);
if (!$link) {
die('Could not connect: ' . mysql_error());
}mysql_select_db($dbname);
- query the database for a list of blog postings and their content …
$res = mysql_query("SELECT t1.ID, t1.post_title, t1.post_content
FROM " . $dbname . ".wps_posts t1
WHERE t1.post_content LIKE '%" . $from . "%' and t1.post_status in ('publish','future')");
if ($res == 0) {
echo("<b>Error " . mysql_errno() . ": " . mysql_error() . "</b>");
} else if (mysql_num_rows($res) == 0) {
echo("<b>Query executed successfully</b>");
} else {
while (($r_array = mysql_fetch_row($res))) {
mysql_select_db($dbname);
if (!isset($_GET['commit'])) {
$reptxt .= "UPDATE " . $dbname . ".wps_posts SET post_content=replace(post_content,'" . $from . "','" . $to . "') WHERE ID=" . $r_array[0] . " and post_title='" . $r_array[1] . "' and '" . $sdate . "'='" . $sdate . "'\n";
if ($tablestr == "") {
$tablestr="<h1>Commit to the WordPress Blog Checkbox Changes found going from '" . $from . "' to '" . $to . "'</h1><br><table border=20><tbody id=mytbody><tr><th style=width:10%;>Title</th><th style=width:10%;>Okay for this Change?</th><th style=width:300px;>Look 4.1.1</th></tr></tbody></table><br><form method=GET action=./old_new_wp_misspellings.php><input type=hidden name=astringthatislikeanapplicationpassword value=y></input><input type=hidden name=from value='" . $from . "'></input><input type=hidden name=to value='" . $to . "'></input><input type=hidden name=commit id=commit value=',";
}
$tablestr.=$r_array[0] . $cdel;
$tablestr=str_replace("</tr></tbody></table><br><form method=", "</tr><tr><td>" . $r_array[1] . "</td><td><input onchange=\"document.getElementById('commit').value=document.getElementById('commit').value.replace('," . $r_array[0] . ",',',');\" type=checkbox checked></input></td><td style=width:300px;><!--div style='overflow:hidden; -ms-transform: scale(0.2, 0.2); -webkit-transform: scale(0.2, 0.2); transform: scale(0.2, 0.2);'--><details><summary>Reveal WordPress 4.1.1 Look '" . $from . "' to '" . $to . "' proposed changes ...</summary>" . str_replace($from,$to,$r_array[2]) . "</details><!--/div--></td></tr></tbody></table><br><form method=", $tablestr);} else if (strpos(("," . urldecode($_GET['commit']) . ","),("," . $r_array[0] . ",")) !== false) {
mysql_query("UPDATE " . $dbname . ".wps_posts SET post_content=replace(post_content,'" . $from . "','" . $to . "') WHERE ID=" . $r_array[0] . " and post_title='" . $r_array[1] . "'");
}
mysql_select_db($dbname);
}
if ($reptxt != "" && !isset($_GET['commit'])) {
file_put_contents("old_new_wp_misspellings.txt", $reptxt);
$tablestr.="'></input><input type=submit value='Commit to the SQL Relevant to Checkboxes Below' style=background-color:yellow;></input></form><br><textarea cols=80 rows=100>" . $reptxt . "</textarea>";
echo "<!doctype html><html><body>" . $tablestr . "</body></html>";
} else if (isset($_GET['commit'])) {
if (file_exists("old_new_wp_misspellings.txt")) unlink("old_new_wp_misspellings.txt");
echo "<!doctype html><html><body><p>Commit to MySql database done.</p></body></html>";
}
}
mysql_close($link);
- includes the step to present that HTML data gathered to the user … the checkboxes of which and view of the blog content of which helps them decide whether to click the yellow button to …
- perform (commit) the database changes to the WordPress MySql database informing the user …
$res = mysql_query("SELECT t1.ID, t1.post_title, t1.post_content
FROM " . $dbname . ".wps_posts t1
WHERE t1.post_content LIKE '%" . $from . "%' and t1.post_status in ('publish','future')");
if ($res == 0) {
echo("<b>Error " . mysql_errno() . ": " . mysql_error() . "</b>");
} else if (mysql_num_rows($res) == 0) {
echo("<b>Query executed successfully</b>");
} else {
while (($r_array = mysql_fetch_row($res))) {
mysql_select_db($dbname);
if (!isset($_GET['commit'])) {
$reptxt .= "UPDATE " . $dbname . ".wps_posts SET post_content=replace(post_content,'" . $from . "','" . $to . "') WHERE ID=" . $r_array[0] . " and post_title='" . $r_array[1] . "' and '" . $sdate . "'='" . $sdate . "'\n";
if ($tablestr == "") {
$tablestr="<h1>Commit to the WordPress Blog Checkbox Changes found going from '" . $from . "' to '" . $to . "'</h1><br><table border=20><tbody id=mytbody><tr><th style=width:10%;>Title</th><th style=width:10%;>Okay for this Change?</th><th style=width:300px;>Look 4.1.1</th></tr></tbody></table><br><form method=GET action=./old_new_wp_misspellings.php><input type=hidden name=astringthatislikeanapplicationpassword value=y></input><input type=hidden name=from value='" . $from . "'></input><input type=hidden name=to value='" . $to . "'></input><input type=hidden name=commit id=commit value=',"; // . $r_array[0] . ","; // . "'></input><input type=submit value=Commit></input></form>";
}
$tablestr.=$r_array[0] . $cdel;
$tablestr=str_replace("</tr></tbody></table><br><form method=", "</tr><tr><td>" . $r_array[1] . "</td><td><input onchange=\"document.getElementById('commit').value=document.getElementById('commit').value.replace('," . $r_array[0] . ",',',');\" type=checkbox checked></input></td><td style=width:300px;><!--div style='overflow:hidden; -ms-transform: scale(0.2, 0.2); -webkit-transform: scale(0.2, 0.2); transform: scale(0.2, 0.2);'--><details><summary>Reveal WordPress 4.1.1 Look '" . $from . "' to '" . $to . "' proposed changes ...</summary>" . str_replace($from,$to,$r_array[2]) . "</details><!--/div--></td></tr></tbody></table><br><form method=", $tablestr);} else if (strpos(("," . urldecode($_GET['commit']) . ","),("," . $r_array[0] . ",")) !== false) {
mysql_query("UPDATE " . $dbname . ".wps_posts SET post_content=replace(post_content,'" . $from . "','" . $to . "') WHERE ID=" . $r_array[0] . " and post_title='" . $r_array[1] . "'");
}
mysql_select_db($dbname);
}
if ($reptxt != "" && !isset($_GET['commit'])) {
file_put_contents("old_new_wp_misspellings.txt", $reptxt);
$tablestr.="'></input><input type=submit value='Commit to the SQL Relevant to Checkboxes Below' style=background-color:yellow;></input></form><br><textarea cols=80 rows=100>" . $reptxt . "</textarea>";
echo "<!doctype html><html><body>" . $tablestr . "</body></html>";
} else if (isset($_GET['commit'])) {
if (file_exists("old_new_wp_misspellings.txt")) unlink("old_new_wp_misspellings.txt");
echo "<!doctype html><html><body><p>Commit to MySql database done.</p></body></html>";
}
}
mysql_close($link);
… except that, here at rjmprogramming.com.au we join two databases, the complication of which we’ll spare you, though you can see a bit of it by perusing today’s PDF slideshow.
Now let’s talk about categories of misspellings …
- oneoffs … not suitable for this hammer blow to a peanut problem, today
- systematic … misspelling via user errors or some other reason … more likely to be candidates for today’s generic replacement approach
Here at this blog we often have a “thread of blog postings” approach to our presentation. In other words, we build a story out of its parts and create a thread of a story as we go along, often starting a thread or subthread with a “Primer” tutorial. You may rest assured the Search Engines do not like the repetition of this content, please be aware. We need it, with our style of blogging to come upon some subject matter in parts. However, in this scenario, a misspelling in a “Primer” tutorial can cause havoc, and think a “Primer” tutorial must have had “featurng”, and it has propogated through about twenty other blog postings until we finally “bit the bullet” and wrote this PHP/MySql tool for WordPress Generic Misspelling Fixes today.
We can’t show you our exact PHP code today, but we can show you a one database one post table scenario PHP old_new_wp_misspellings.php for your perusal, and perhaps download.
You can also see this play out at WordPress 4.1.1’s WordPress Blog Posting Generic Misspelling Fix Tutorial.
Previous relevant WordPress Blog Posting Thread Content Summary Tutorial is shown below.
WordPress Blog Posting Thread Content Summary Tutorial
Yesterday’s WordPress Blog Posting Content Summary Primer Tutorial used the reveal HTML5 “stars” …
… to allow for “scrunched up” presentations of multiple blog posts so really only had huge impactive use with the WordPress Blog document root index.php call of it, where several of the latest blog postings are shown to the user. However, in the way we go about it here, we construct these …
Blog Posting Threads
… that call on and contain blog postings related to it (from the recent past, usually, but doesn’t have to be). These “lead in” blog postings are presented in full separated from the new blog posting content by our home (CSS) styled …
<hr />
… horizontal rule elements. Today, we make use of that “habit” we have to introduce new …
… “pairings” to scrunch these up, when the user has decided to generally “scrunch up” (though we are going to think about doing it all the time after seeing what it’s like, for a while).
Not much new here, you might say, but there is something new about what we do navigation wise in these scenarios. We have another “habit” with our blog posting creations for these “blog posting threads”. Invariably, we’re pretty sure, we provide within the new blog posting, an HTML a hashtagging link (eg. #wpbpcspt to get to blog posting “thread” member below, often “yesterday’s”). We want it to be that if the user uses one of these a hashtagging links that causes any (new) “closed” details/summary “guardians against verbosity” to open up. How is that done? We maintain a global …
var nohlist=";";
… which gets to be accessed and used in a new Javascript function …
function checknohlist() {
if (('' + location.hash).indexOf('#') != -1) {
if (nohlist.indexOf(';#' + location.hash.split('#')[1] + ';') != -1) {
document.getElementById('ds_' + location.hash.split('#')[1]).setAttribute('open', true);
nohlist=nohlist.replace(';#' + location.hash.split('#')[1] + ';',';');
}
}
if (nohlist.replace(';','') != '') setTimeout(checknohlist, 3000);
}
… using the hashtagging “flagger” location.hash … the bits of a URL after and including the # character, as relevant … that is hashtagging, to us … and this new function is now used in amended function from yesterday as per …
function details_summary(mou) {
var other_bits=[], hrother_bits=[], ihrb=0, fb='', fbids=[], thatidis='', hrp='';
var dbitssare=document.body.innerHTML.split('<div class="entry-content">');
var dbitseare=document.body.innerHTML.split('<div class="entry-utility">');
if (document.URL.indexOf('detailssummary=') != -1 || mou != 0) {
if (dbitssare.length > 1 && dbitssare.length == dbitseare.length) {
var dbih=document.body.innerHTML, idbih=1;
for (var idb=0; idb<dbitssare.length; idb++) {
if (('' + dbitssare[eval(1 + idb)]).indexOf('<p>') != -1) {
if (('' + dbitssare[eval(1 + idb)]).split('<p>')[idbih].split('</p>')[0] == '') {
if (('' + dbitssare[eval(1 + idb)]).split('<p>').length >= eval(1 + eval(idbih))) {
idbih++;
}
}
dbih=dbih.replace('<div class="entry-content">','<details class="gendetails" title="Click me to toggle open/close ... ' + ('' + dbitssare[eval(1 + idb)]).split('<p>')[idbih].split('</p>')[0].replace(/\'/g,'`').replace(/\"/g,'`').replace(/\>/g,'>').replace(/\</g,'<') + '"><summary></summary><div title="entry-content" class="entry-content">');
} else {
dbih=dbih.replace('<div class="entry-content">','<details class="gendetails" title="Click me to toggle open/close"><summary></summary><div title="entry-content" class="entry-content">');
}
if (document.URL.indexOf('andmorehr=') != -1 || 1 == 1) {
other_bits=dbih.split('<div title="entry-content" class="entry-content">');
hrother_bits=other_bits[eval(-1 + other_bits.length)].split('<div class="entry-utility">')[0].split('<hr ');
hrp='<hr ';
for (ihrb=1; ihrb<hrother_bits.length; ihrb++) {
if (hrother_bits[ihrb].indexOf('</p>') != -1 && hrother_bits[ihrb].indexOf('If this was interesting you may be interested') == -1) {
thatidis='';
fb=hrp + hrother_bits[ihrb].split('>')[0] + '>';
fbids=(hrp + hrother_bits[ihrb]).split(fb)[1].split('</p>')[0].split('<p id="');
if (fbids.length <= 1) {
fbids=(hrp + hrother_bits[ihrb]).split(fb)[1].split('</p>')[0].split("<p id='");
if (fbids.length > 1) {
thatidis=fbids[1].split("'")[0];
}
} else {
thatidis=fbids[1].split('"')[0];
}
if (nohlist.replace(';','') == '' && thatidis != '') {
setTimeout(checknohlist, 3000);
}
if (thatidis != '') {
nohlist+='#' + thatidis + ';';
dbih=dbih.replace((hrp + hrother_bits[ihrb]), (fb + '<details id="ds_' + thatidis + '" class="innerdetails" title="Click me to toggle open/close"><summary>' + (hrp + hrother_bits[ihrb]).split(fb)[1].split('</p>')[0] + '</p></summary>' + (hrp + hrother_bits[ihrb]).split((hrp + hrother_bits[ihrb]).split('</p>')[0] + '</p>')[1] + '</details>'));
} else {
dbih=dbih.replace((hrp + hrother_bits[ihrb]), (fb + '<details class="innerdetails" title="Click me to toggle open/close"><summary>' + (hrp + hrother_bits[ihrb]).split(fb)[1].split('</p>')[0] + '</p></summary>' + (hrp + hrother_bits[ihrb]).split((hrp + hrother_bits[ihrb]).split('</p>')[0] + '</p>')[1] + '</details>'));
}
}
hrp='<hr ';
}
}
dbih=dbih.replace('<div class="entry-utility">','</details><div title="entry-utility" class="entry-utility">');
}
document.body.innerHTML=dbih;
}
if (document.URL.indexOf('detailssummary=') != -1 && mou == 0) {
document.getElementById("eds").innerHTML = "➕";
document.getElementById("eds").title = "Open up blog posting contents now";
document.getElementById("eds").style.visibility='visible';
}
} else if (mou == 0 && dbitssare.length > 1 && dbitssare.length == dbitseare.length) {
document.getElementById("eds").style.visibility='visible';
}
}
So if you try today’s live run and “motor down” to one of the “blog posting threads” we have … and a lot are … you’ll see those new details/summary pairings “guarding against verbosity” unless you use some of those hashtagging links into these “lead in” blog posting thread submembers, or if you click the details element yourself.
Previous relevant WordPress Blog Posting Content Summary Primer Tutorial is shown below.
WordPress Blog Posting Content Summary Primer Tutorial
We really like WordPress.org for the basis of this blog’s design (and recognize WordPress.com as a great idea for those not wanting to host their own Apache/PHP/MySql domain). And am sure you would not be surprised that I am not alone. Take a read of the excellent WordPress information by Websitebuilder.org and, reading closely, you’ll see how popular this blogging platform is, and mention of a number of famous people using it.
I’ve never had much trouble with WordPress, so, not all the time, but occasionally, I tweak it. For this, we suggest, as WordPress would, to use their Codex PHP (with MySql) coding advice, and on a personal level, though, you’ll see, reading this blog, that we also like the direct approach of, mainly, changing the header.php PHP code that sits in, for our case of a theme called TwentyTen (“twentyten” in lowercase) …
[documentRootOfWordPressWebsite]/wp-content/themes/twentyten/
Today’s tweak of header.php relates to a matter dear to our hearts. The desire to cater for mobile users with small screens, yet not be dumbing blog posting content down just for the sake of it. Today we channel just about our favourite reveal based idea you can read more about at HTML5 Details Summary Primer Tutorial to harness the goodies that came with HTML5 in the form of the …
Implementing this in header.php went like this …
- added into …
<body onload=" changeasfordownload(); if (cafd == cafd) { cafd=0; } else { cafd=true; } checkonl(); setTimeout(initpostedoncc, 3000); widgetcon(); precc(); courseCookies(); cookie_fonts(); is_mentioned_by(); calendar_pass(); prejustshow(); details_summary(0);">
- added a new emoji button up near the top …
document.getElementById('site-title').innerHTML+='<a id="avs" style="text-decoration:none;" href=# onmouseover="getVisualSynopsis(event);" onmouseout="yehbut();" ontouchstart="getVisualSynopsis(event);" ontouchend="yehbut();" onclick=" uptop(); " title="... you can wait for the long hover functionality about Visual Synopsis (Slideshows)">🎦</a> <a style="cursor:pointer;text-decoration:none;" onclick="popselid();" title="Filter Content via Div ID">➗</a> <a style="cursor:pointer;text-decoration:none;visibility:hidden;" title="Blog post contents reduced to summary" id="eds" onclick="pre_details_summary();">➖</a>' + printscreen(0);
- then added these two new Javascript functions to suit those events defined above …
function pre_details_summary() {
var idos=0,dos=[];
if (('' + document.getElementById("eds").title) == 'Blog post contents reduced to summary') {
if (document.body.innerHTML.indexOf('<summary></summary>') == -1) {
details_summary(1);
} else {
dos=document.getElementsByTagName('details');
for (idos=0; idos<dos.length; idos++) {
if (dos[idos].className == "gendetails") {
dos[idos].removeAttribute('open');
}
}
}
document.getElementById("eds").innerHTML = "➕";
document.getElementById("eds").title = "Open up blog posting contents now";
} else {
dos=document.getElementsByTagName('details');
for (idos=0; idos<dos.length; idos++) {
if (dos[idos].className == "gendetails") {
dos[idos].setAttribute('open', true);
}
}
document.getElementById("eds").innerHTML = "➖";
document.getElementById("eds").title = "Blog post contents reduced to summary";
}
}function details_summary(mou) {
var dbitssare=document.body.innerHTML.split('<div class="entry-content">');
var dbitseare=document.body.innerHTML.split('<div class="entry-utility">');
if (document.URL.indexOf('detailssummary=') != -1 || mou != 0) {
if (dbitssare.length > 1 && dbitssare.length == dbitseare.length) {
var dbih=document.body.innerHTML, idbih=1;
for (var idb=0; idb<dbitssare.length; idb++) {
if (('' + dbitssare[eval(1 + idb)]).indexOf('<p>') != -1) {
if (('' + dbitssare[eval(1 + idb)]).split('<p>')[idbih].split('</p>')[0] == '') {
if (('' + dbitssare[eval(1 + idb)]).split('<p>').length >= eval(1 + eval(idbih))) {
idbih++;
}
}
dbih=dbih.replace('<div class="entry-content">','<details class="gendetails" title="Click me to toggle open/close ... ' + ('' + dbitssare[eval(1 + idb)]).split('<p>')[idbih].split('</p>')[0].replace(/\'/g,'`').replace(/\"/g,'`').replace(/\>/g,'>').replace(/\</g,'<') + '"><summary></summary><div title="entry-content" class="entry-content">');
} else {
dbih=dbih.replace('<div class="entry-content">','<details class="gendetails" title="Click me to toggle open/close"><summary></summary><div title="entry-content" class="entry-content">');
}
dbih=dbih.replace('<div class="entry-utility">','</details><div title="entry-utility" class="entry-utility">');
}
document.body.innerHTML=dbih;
}
if (document.URL.indexOf('detailssummary=') != -1 && mou == 0) {
document.getElementById("eds").innerHTML = "➕";
document.getElementById("eds").title = "Open up blog posting contents now";
document.getElementById("eds").style.visibility='visible';
}
} else if (mou == 0 && dbitssare.length > 1 && dbitssare.length == dbitseare.length) {
document.getElementById("eds").style.visibility='visible';
}
}
Of course, this is most of benefit when you are not already honing in on the one WordPress blog posting, but there’s more fun to come, we reckon!
If this was interesting you may be interested in this too.
If this was interesting you may be interested in this too.
If this was interesting you may be interested in this too.
If this was interesting you may be interested in this too.
