Build Your Own Business Website header image

Community Library Website Case Study – Part 50 – Find and Replace Semi-colons in the Database

Difficulty Level -

Filed Under Topics - , ,

Listed Under Lesson Subjects - ,

Applies to - ,

Whoops, you've found some premium content!

Watch the opening clip of this video to preview it,
the full video is available to paid members.

This is the 50th in a series of live lessons on using WordPress and Thesis as a Content Management System (CMS) for a Community Library Website. In this lesson we directly modify the data in our database using phpMyAdmin and MySQL statements.  We search for the semi-colons in the author names and replace them with commas.

Video Transcript

Rick: The 2nd thing we’re going to do is the thing that we talked about doing weeks ago which is fixing this semi-colon in author names. Just to refresh your memory here a little bit, in fact, we’ll say wow about this first. If we go to authors in the Library Case Study… pardon me… let’s see. Dashboard… now let’s see what happens to authors. Oh my gosh, did I fail to save that? Manage taxonomies, okay so I’ve got item authors and I should have… item-authors, the taxonomy name, author, author, author… save custom taxonomy and then go back to authors. Well you know, I think I just trashed my authors list here. Let’s look at post…oh actually, what I really did was I complete trashed my database here. I didn’t realize I had done that.

Anyway so, rather than having a cool fix for you, let’s just rewind that whole conversation. Go back to this. As you may recall, in order to use CSV Importer to import author names, we had to put these little semi-colons in because the commas made it think that it was 2 separate items and 2 separate tags. And so, that’s just the way CSV Importer imported… interpreted the data. It interpreted a comma in between the 1st and last name as meaning that there were 2 separate authors: one with black stock and one with Terry. And so, our solution to that was to replace all of those commas with semi-colons.

Well, now what we’re going to do now that we have imported the data into our file… into our site… what we are going to do is we’re going to use a SQL statement to change these semi-colons into commas. And so, we’re going to open up php MyAdmin and look at our database which in this case is byobtutoword_19. Now you know, I’m just going to make sure that that’s the case by opening my file manager and in going to public HTML and then going to my site which in this case is test. And then opening my WP config file and my editor and looking… yeah, byobtutoword_19… that is the database name here. So we’ve got the right database open and what happens here is that that semi-colon is in the name of our item author. And so, if we go to… which is a taxonomy term. So if we go browse WP terms table, you can see this. For example, Patty Old West you know, has this…it should be West, Patty Old and it’s got the semi-colon and Mellaby, Derek has the semi-colon in between. So this is the table that stores the taxonomy terms that has the semi-colon in it.

So if we go over to SQL and we say select from WP terms where and we say where name which is the field in the table that contains the name of the author where name is like and then we use the code for searching for the semi-colon… which is a single quote, a percentage sign, and then the semi-colon. And then a closing percentage sign and a closing single quote. Okay so, select from WP terms where name is like… and what like means really is it contains the semi-colon. If we say go here, you can see it has selected all of these items. So the query was completed and it has 34 items that meet that criteria. So that was our test. Just to make sure we knew what we were doing.

If we go back to SQL and instead of select from, if we say update and actually, the convention here is not really going to make any difference but the convention here is to do this in uppercase. So update and then we are going to update the WP terms table, update WP terms and what we want to do is we want to set the name so that equals replace… what are we replacing? We are replacing the field name and we are looking for the semi-colon and we are going to replace the semi-colon with a comma. Okay, so this is the syntax: update WP terms, set the name equals replace name… and it replaces this string in the name and the string in this case is only a semi-colon… with this string which is a comma. Now you can do the same thing right… say, you wonder where you were going to change… well, don’t worry about that. Anyway, this will replace one string in the field name with another string. That’s how replace works.

And so if we say go, it did that to 34 rows and so if we go back to WP terms table and we browse it, you can see now that West, Patty Old works just fine. You’re also going to see that we have a problem. Right down here now, what we have is… we used to have an ampersand here. Now, the database stores… the ampersand is an HTML character and some of the query results were not authors. I saw DVD. Oh, interesting. Okay well, we’ll go take a look at that in a second here.

The ampersand is an HTML character which is save as ampersand amp semi-colon. And since we found the semi-colon and replaced it with a comma, now the ampersand isn’t displaying it properly. So we’ve got to go back and fix that. And so the way we fix that again, let’s go back to our SQL statement and again, what we say is update wp_terms and then we’re going to set the name equals replace and then again, we’re going to replace the name field and we’re going to look at this string which is ampersand amp comma. And then we are going to replace that with the string ampersand amp semi-colon and then close that out.

Now before I show you that though, I want to go over so you can see what happened. I meant to do that. Here if we refresh this now, you can see that we’ve got all of our commas in the way the way they were supposed to be and you can also see where we’ve got a… maybe, not yet. Some place in here you’ll see a problem too. Yeah, right there. There’s the amp and ampersand and the comma here.

So what we’re going to do then is we’re going to fix that. So we go back over to this and there’s our replacing the name field with the errant HTML character with the correct one. We say go, that happened to 5 rows so if we go back to browse and we go to wp_terms, now you can see that has been… well, now it has a semi-colon anyway. And so, when we go back to our site and we refresh this, we’ll go back to that page again. Oh you can see now it’s got the ampersand there.

So that’s how we make that change. Now, I’m going to turn Pam’s microphone on here real quick. Pam? Where else did you see a problem?

Pam: You found it. I don’t know why but when you first did the select to select the ones to act upon, there was a place where it said something like DVDs, children and family. It wasn’t an author but I don’t see it here in what we have so maybe I was imagining things.

Rick: Well, maybe we fixed that actually, right? If we look at media type for a second here… okay, under locations…yeah you know, that was probably right there.

Pam: Yeah.

Rick: If I was better at this, we could have made sure that when we did our replace that we didn’t take the ampersand value and replaced the semi-colon. But I’m not that good so this is what it seemed to me like it would work.

Pam: Well, this is a great solution. It’s very easy to understand, very straightforward.

0 Comments… add one

Save $200 on Membership Now!

Start learning today for as little as
$0.82 PER DAY!
Subscription Options
0 comments… add one

Leave a Comment