Cannot Edit MySql Database Through Phpmyadmin? Add or Drop Tables Remotely, Tested for Joomla on Godaddy

Today I was moving a Joomla site to Godaddy shared hosting from a virtual private server (VPS). When I set up the MySql database I wasn't really paying attention to what I was doing and didn't click on 'allow direct access'......

Later on I was adding a few extra extensions and came to install Rocketthemes' Rokcandy component. The problem started because the component had been installed previously but rather than a clean uninstall the directories had been deleted. When trying to install I was recieving errors that the component existed and could not be installed. These errors were happening because there were tables in the database that had not been deleted.

No problem I thought, I'll just login to Phpmyadmin through the Godaddy database manager and delete the tables. No chance! Without direct access enabled you cannot sign in to Phpmyadmin even though the link to Phpmyadmin is there. The solution?.............

I first tried using the great MySql application Heidisql which allows you to remotely connect to your databases through a windows application. Not allowed! The connection must come from the designated server and website.

My immediate thought was to create a new database and use Joomlapack (Now renamed to AkeebaBackup) to backup just the database, which I could then import to my new, directly accessible database and then delete the tables. So this is what I started to do. However Godaddy is configured, it takes an amazing amount of time to create something which, on any other setup, is done instantly. So during the wait I started googling a solution to this problem.

That is when I came across Chad Lloyd. This guy must have had a similar problem because he decided to write a php script to directly access the database through a web browser.

So how is it done?

Download the zip file here:

Download Chads Direct Database Editor

Or download from Chads site and be sure to read his information on security: Chads SP Editor Page

Open up the zip and extract the script to your desktop. Open up the file in a text editor and scroll down until you find the lines below:

//server configuration
$server = "mysql_server_name";                    //server name
$user = "mysql_user_name";                        //server user name
$password = "mysql_password";                    //server password
$schema = "mysql_database";                        //database name
$use_deterministic = true;                        //true | false - some shared hosts require
// this to be true (i.e., GoDaddy)

Replace the names and passwords with your MySql login information and then save the file.

Use an FTP client or your webbased file manager to upload the file to your domain (I just dropped it in my domain root so it was easily accessible!)

Then browse to the file destination, in mycase http://www.mydomain.com/speditor.php5 and you will see something similar to the screenshot below.

As you can see from the screenshot, I had absolutley no idea what I was doing and tried to enter a direct command on the first page (Not that I have a clue what the correct command would be!). So, on my second attempt I just entered the name of the table I wanted to edit 'jos-rokcandy' and hit go.

Next page, the table was in a box on the left as above (but without the DROP lol) so I took a look at the dropdown, changed it to delete and hit go.

Bingo, job done. Don't forget to delete the file after you've used it so no cheeky imps delete your whole database!

Next I went back to the extensions installer and tried to reinstall Rokcandy. No problems, worked first time. Thanks Chad!!

Hope it helps you too. Just don't be stupid like me and go back and take a few screenshots and delete it again by accident!

EDIT: I just came across this component for joomla which lets you edit your database through the backend.

http://extensions.joomla.org/extensions/hosting-a-servers/database-management/9886

Twitter

SocButtons v1.4