There are a number of ways to perform admin tasks on a Joomla website. Sometimes it’s not possible to do it via the dashboard, which is when I might fall back on SQL and the database for some help.

In my last article I wrote about using the command line as a way of performing tasks like updating user details. That required terminal access to the webserver. This time round you would need access to the database either via a terminal window or phpMyAdmin, either in your web hosting Control Panel or web browser if your site is configured that way. However you access the database, the same words of caution should apply as SQL or Structured Query Language is a powerful tool so think before running any queries that aren’t of the SELECT variety.

While there’s plenty of database queries used in Joomla Extension Development, and I’d be happy to talk about the structure of the code in PHP, I want to show you how I have used it for detective work and “firefighting”. This article is about my own experience using SQL for some tasks and not meant as a set of instructions or a tutorial.

What can I do with SQL in Joomla?

My first experience of using SQL with Joomla was due to an issue relating to errors I couldn’t resolve in the dashboard. An extension had been enabled that was for a different version of Joomla and had thrown a “spanner in the works” and the only thing I could think to do was open phpMyAdmin, go to the extensions table and set enabled to be 0 and click Go to save the change. In fact, using the Browse window, I didn’t even need SQL for that task. But it helped me out and made me realise that using the database I could get back into my website dashboard if something had gone badly wrong including some sticky moments when logins don’t work or my IP address was blocked.

A recent example occurred during an upgrade when I got an error regarding a plugin and whichever page I tried to go to I still got the error. So here’s what I did:

update `#_extensions` set enabled = 0 where name = "plg_behaviour_compat6" (where compat6 was showing in the error message)

As a result I could use the dashboard again, at least enough to find the next error along the way.

IP address blocked

Not all websites use the AdminTools extension but I have found on occasion that my IP address has changed and the website login is inaccessible or doesn’t permit me to login from that IP address. 

I might want to add an IP address:

INSERT INTO `s92h8_admintools_adminiplist` (`ip`, `description`) VALUES( 'x.x.x.x', 'Dan at work');

Or maybe change the existing record:

UPDATE `s92h8_admintools_adminiplist` SET `ip` = 'x.y.z.x' WHERE id = 1;

You can see the difference in syntax between the INSERT and the UPDATE statement (NB it’s not a  real IP address as you’d need numbers for that) You can find your details by Googling something like “what is my ip address ipv4”. Quotes are always important in SQL statements as well.

Fixing User Issues

Other useful SQL tricks include updating user accounts. I know this can be done in the dashboard, but there are times when it’s just not possible. Google for “reset Joomla admin password” and you’ll find loads of posts about it. There’s also a Joomla Documentation page that covers the subject of password resets and even creating new users via the database.

Here’s some tasks you might be able to resolve this way:

• Password change
• Username change
• Email change
• User groups / make then Super Admin (or not)
• Block a user
• Delete users (with extreme caution / details exist in other tables too)

In each of the tasks above you could use the browse window in phpMyAdmin, make the change and hit Go to update.

Passwords are encrypted in the Joomla database, which should be standard practice across all websites. To change the password we’d need to “MD5 hash” the password. This applies particularly for Super Admins where the password can’t be reset from the login window.

UPDATE #__users SET password = MD5('NewPassword') WHERE username = 'superadminusername';

Articles and menu items

There’s a few things I might want to use SQL for when it comes to articles. Helpfully in my Joomla 5 website they all live in the #__content (change #_ for your table prefix)

What I can’t do easily in the dashboard is to find articles with certain content. This may be where I’ve placed a module reference using a { shortcode } or maybe I need to change an email address link.

The solution comes with my favourite condition, which is LIKE. I use this often as a means of finding part of the contents of a database field. In #__content it’s more often than not the fulltext field. 

For example:

SELECT * FROM `#_content` WHERE `fulltext` LIKE "%email%";

The percentage signs wrap what I’m searching for within the large fulltext field and any time it finds it I can go to the article to update my content or I can easily edit that row in phpMyAdmin. I’m sure there’s a SQL statement that would do search and replace within the field but even I have limitations.

Another simple search might be to look for who created an article. Maybe that person has left the organisation so their created_by_alias might need to be replaced.

UPDATE `s92h8_content` SET `created_by_alias`= 2 WHERE `created_by_alias = 4;

Where the number is their user ID. This should do the trick of changing the author name in the article meta.

For menu items we may want to change who can view an article by changing access in #__menu or something that I struggle with from time to time is where 2 menu items have a similar alias but maybe in different menus. 

Try something like this:

SELECT * FROM `#__menu` WHERE `alias` LIKE '%how-to%';

It could save you hours of work in the menu manager trying to find which menu item to amend.

Conclusion

This is a short selection of SQL examples you could use within Joomla that has “saved my life”, or at least given me a shortcut to finding the answer to something annoying. Learning a few queries such as SELECT with WHERE or LIKE may prove very helpful in nailing issues you have with your website, or a simple means of doing some quick admin.

For some more examples try:

https://ultahost.com/knowledge-base/run-sql-queries-phpmyadmin/

https://www.balbooa.com/help/knowledge-base/troubleshooting/reset-joomla-admin-password