Joomla · Joomla 1.6 · Joomla 1.7 · Joomla Components · Joomla Modules · Joomla Tutorials

Joomla 1.7 Help – Use SQL to query the Backend Database Directly

Joomla 1.7 Extensions – Module to write SQL to query the Backend Database Directly.

I found this free and very cool Joomla 1.7 extension for you to be able to query the backend database directly.  This extension is for those who know or have an idea of how to write SQL statements.  If you are like me, I need to look at my phpMyAdmin to check out my table names and table structures.  From there you can see your database prefix name, table names and select the columns that you want to display.

Say for example:

  • I want to display a simple table list of Weblinks.
  • In the table of Weblinks you want to display the title, URL, # of hits
  • Display as a module on the website

Overview of steps of what you need to do by Elaine  at Cmsmind.com:

  1. Login to your phpMyAdmin to find your database prefix and look at weblink table structure
  2. Install free Joomla 1.7 extension – Art Table Lite
  3. Create module and add in the SQL statement
  4. Click here to find out how to add a module to an article.
  5. BONUS:  Other SQL statements you could use to query other tables.

Here are the steps to insert your own SQL statements in a Module:

1.   Login to your phpMyAdmin to find your database prefix and look at weblink table structure

  •  Login to your webhosting and click on phpMyAdmin
  • Along the left navigation you will see a list of table names prefixed with your database prefix.
  • My db prefix is cmsmind_, so search for cmsmind_weblinks and click on it.
  • You will see the table structure, if you click on ‘browse’ you will see all the fields in the table
  • See screenshot below
phpMyAdmin Joomla Prefix
phpMyAdmin Joomla Prefix

2.  Install free Joomla 1.7 extension in the Extensions Manager

  • Search for ‘Art Table Lite’ and download the ZIP file from the Joomla Extensions directory
  • Login to your Joomla 1.7 administrator backend
  • Click on ‘Extensions’, ‘Extension Manager’
  • Under Install, select the ZIP file downloaded and click on ‘Upload and Install’
  • You will see the following message:
Joomla 1.7 Art Table Lite Extension Manager
Joomla 1.7 Art Table Lite Extension Manager

3.  Create module and add in the SQL statement in Module Manager

  • Click on ‘Extensions’ ,’Module Manager’, New
  • Select ‘Module Type’ = Art Table Lite Edition
  • In the Module Manager, set the following values:
    • Title = SQL in Module
    • Position = position-9
    • Status = Published
    • Access = Public
    • SQL Query = Select title, URL, hits from cmsmind_weblinks
  • Click on ‘Save & Close’
  • See screenshot below:
Joomla 1.7 Insert SQL Module Manager
Joomla 1.7 Insert SQL Module Manager

Now go to your Joomla 1.7 frontend and refresh your website.    You can see what it looks like below:

Joomla 1.7 Show SQL in module on frontend
Joomla 1.7 Show SQL in module on frontend

4.  If you want to show other tables via SQL in the module, below are some examples.  Use the SQL statements (beginning with select) and insert it into step 3 – SQL Query.

  • show users and email addresses:  select name, email FROM cmsmind_users
  • show published articles and number of hits : select title, hits FROM cmsmind_content where state = 1 order by title
  • show published contact name, email: select name, email_to FROM cmsmind_contact_details where published=1

Hope that helps you.

Thanks,

Elaine

One thought on “Joomla 1.7 Help – Use SQL to query the Backend Database Directly

Leave a Reply

Your email address will not be published. Required fields are marked *

Captcha Captcha Reload