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

by Elaine on August 30, 2011

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
joomla 17 elaine cmsmind phpMyAdmin weblinks Joomla 1.7 Help   Use SQL to query the Backend Database Directly

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 17 art table lite extension manager Joomla 1.7 Help   Use SQL to query the Backend Database Directly

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 17 art table lite elaine cmsmind module manager Joomla 1.7 Help   Use SQL to query the Backend Database Directly

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 17 art table lite elaine cmsmind frontend Joomla 1.7 Help   Use SQL to query the Backend Database Directly

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

 Joomla 1.7 Help   Use SQL to query the Backend Database Directly

Elaine

Elaine is the founder of CmsMind, an online blog providing Joomla and Wordpress tutorials. Elaine lives in Toronto and when she is not online, she is either Marathon and Triathlon training or travelling and racing somewhere in the world. Find her also at: Save Money, Premium Themes and Templates, and at Webhosting Tips.

More Posts - Website

{ 1 comment… read it below or add one }

aristos May 13, 2014 at 8:04 am

Can you give us an example of the connection string used?

Reply

Leave a Comment

Captcha Captcha Reload

Older Post:

Newer Post: