I start with the intro for beginners or foolish users of Elementor and similar: The database of your WordPress is where the posts and pages and all the options of the theme, the plugins and each little box that you fill out and then click on are stored. "keep".
The BD (Hey, did you know that in France, these acronyms mean something like "comic"?) It is not intended to be "fiddled" directly; It can be done and nothing happens, but we are supposed to have the WP admin interface and the front of the website to interact with it.
After all, we could almost manage a WP directly from the database (which is still a set of tables, like those in Excel) without using the "admin". What happens is that it is neither so comfortable nor so visual.
The WordPress database is as it is with its standard protections, and adding a plugin that accesses all of it, well what do you want me to say, it is an extra door that you have there.
To access it, our hosting provides us with access data, which we can use to connect to it remotely from a program (a program is a app also, yes) customer MYSQL, for example heidi, Workbench etc And with this "program" you navigate through its pages as if it were an excel.
Then there is language SQL, which is to search and do specific things with the data, we can use it either with the previous program, or by Command line (hackerrrr mode), or the easy way that hosting providers always give us is, in their options panel, an online software called PHPMyAdmin.
He PHPMyAdmin It's okay, you can do things without having any idea of SQL code, such as searching and modifying values and texts, and also exporting the entire database or certain tables, to have your backups, do migrations, etc.
What I am going to tell you - but don't tell too many people - is that there are cases in which I have come across a website in which I had to do "things", and I have needed access to the database. "bareback", and I did not have complete credentials or access to PHPMyAdmin.
Plugins to bareback the database, and the recommendation: Without fear, but with "care";
As always, there are plugins for this. Let's see, while it is true that you should not be afraid of touching the database, You have to be careful, as with everything, but a little more. So, before recommending plugins, I tell you that it is important that if you add one, you do what you have to do with it, and then uninstall it, or restrict it to administrator users. And of course, before doing anything, backup the entire database.
The WordPress database "is as it is" with its standard protections, and adding a plugin that accesses all of it, well what do you want me to tell you, it is a extra door you have there. So it's better to simply remove the door when you leave.
WP Data Access It is my favorite plugin to change "things" directly in the DB; It's not like I've tried so many, but this one works great for me and is very useful and intuitive.
Common cases in which I have had to resort to exploring the database with WPDA
Well look, I'll tell you some real field work cases, which happen to me frequently:
- Having to change the main email of the site administrator, and not being able to, because, for whatever reason, the email does not appear to confirm the change.
Yep. It has happened to me several times that, on a site where the emails work perfectly, the email to confirm the admin change never arrives or you can't find it. Well, it is one of the cases in which, using WPDA I have gone to the _options table, I have changed the admin_email and I have not had to wait for the confirmation notice to make the change. Time saved. - Having to modify a text that appears in a corner of the web... that YOU DON'T KNOW where it comes from. (A text that you know 100% is not malware, be careful, it would not be the case of the typical Russian or Viagra text, this would be an entire post on the topic of malware, which is not the case here)
This is super typical: Client tells you, change this text to me, and you say okay; You go to the page in your backend, and it is not there, neither in the content nor in the fields, nor in the widget... you look at the theme settings and you cannot find it, you download the entire theme by ftp to search for the text in the php, and nothing, the text you want to change does not appear... well it is clear that it is in the database; In these cases, I first look for it in the _options table, then in _postmeta, and hey, if it's not there, I do a global search throughout the database, and it always appears, sometimes it was a field from a plugin that let you add text before or after a form or widget, etc.; - Like the previous one, but in the case of a custom field, that you know at 100% that it exists, but its form field does not appear in the administrator.
Well yes; When we uninstall plugins, in many cases their settings information is still in the database; Also, there are cases in which we may have mixed it up with the ACF and its options to be shown in content type content editor). Well, for a quick fix, if it is a custom field, it will be in _postmeta, and if you know it is the post content, in _posts, you go, look for it and change it... - Exports and imports (plugin contents)
To export or import posts, no, because with the relationships that exist with the terms and the postmeta, you can mess things up. Always be careful with relationships.
But it has worked great for me to export and import plugin content. For example, maps, sliders, testimonials, etc. created with a plugin, which creates its own tables to store these contents. With a little Google, you can always find which tables are generated by each plugin.
A real plugin case study was with MAPSVG, an interactive map plugin. I had to move a lot of maps that had a lot of information, and with two clicks I was able to get everything in a CSV, truly magical.
Basic use of WP Data Access
The main default view of WPDA, is your table explorer. Here we have them all listed and we can look for (search for TABLES, not data within them). He prefix of our tables may be different.
For example: "lgtbi_options, lgtbi_comments". Where I write lgtbi_, which are four random letters that came out as is, you must have others. If the prefix of your table name is "wp_", you should change it or ask to have it changed, as an additional security measure. It's so that hackers get involved and don't know that your database is from a WP.
We continue, as I said, here first the tables of our database:
Hovering over each table we have 2 options, table configuration (manage), and exploration (explore, see "what has pol inside"):
Once we enter the data explorer of a table, we have the options and values. Now we can search here for an option or value using the search box.
The values are what we can change here, in the example, in the following screenshot, you can see the admin email of my _options. If I change it here to sack, the prior approval email is NOT sent to the previous email address.
The thing is, the WordPress database It is just another text file; It is very large (sometimes immense) and its structure must be kept intact, handing him a cloth from time to time, but hey; that doesn't bite. Simply, if you are not 100% sure of what you are doing, then do not touch it, if in doubt.
Be especially careful with serialized data
If you see an option value in a format like this:
to:9:{yes:5:"e-mail";s:13:"test@test.com";s:4:"yam";s:15:"Werner
Etsebeth";s:8:"address1";s:17:"1 Giligans
island";s:8:"address2";s:1:"5";s:4:"city";s:9:"cape
Town";s:5:"state";s:2:"TO THE";s:3:"zip";s:4:"7460";s:7:"
country";s:2:"U.S.";s:5:"phone";s:0:"";}
You CANNOT change its value as is; Be careful, it cracks. You cannot change the parts of text in quotes as is, this is another topic. I leave you this link where the types of data are explained very well:
Anti-handling and site breakage measures
As an anti-tamper security measure, the plugin does not allow us to delete (drop) nor empty (truncate) tables that are from WP itself. (Other plugin tables etc yes, so anyway careorl; )
With the option import/export, you can bring data to the tables from csv or similar. With export, you can even export all the tables, thus obtaining a backup of the complete database (making backup copies will improve your sex life and more, believe me).
To end; If you "touch" your WordPress database with the method that I have explained in this post, "with plugin", even if said plugin can only be used by administrators, after doing what you do or testing it, remove it. You better remove it. Remember, backup and remove this plugin later*.
* I clarify about removing; Remove it only if you make sporadic use of it like the one I explain in the post. WPDA has many features besides being a “phpmyAdmin” within WP, you can design and generate very nice data table views to display on pages and posts using shortcodes. It is very powerful.
Don't fear the database!
Leave a Reply