Joomla Articles Reporting with MySQL

Joomla has modules that show a lot about the published articles. For example, the most recent articles or the most popular. The data for this is retrieved from the Joomla database.

Would you be interested in investigating whether you can compile these types of tables yourself? We assume that you have some knowledge of MySQL and that you can execute queries in phpMyAdmin or in a MySQL client. There you can run the scripts described below. If you want to display the output in a Joomla article, a component with a plugin is required. The name of the extension is Plotalot and it is sold by Les Arbres Design . The package includes an extensive documentation package that shows all steps.

All Joomla articles in a row

This is the MySQL query:

SELECT jl_content.title AS Articletitle,
jl_categories.path AS Categorypath,
jl_content.hits AS Hits
FROM jl_content LEFT JOIN
jl_categories ON jl_content.catid = jl_categories.id
WHERE jl_content.state = 1 and jl_content.access = 1
ORDER BY jl_categories.path, jl_content.title

Explanation of the steps:

• Replace the prefix jl_ with the prefix you chose during your Joomla installation
• The WHERE line shows that only articles that are published (state=1) and accessible to everyone (access=1) are selected
• The order is determined first by the category and then by the title

The above query produces the following result via the Plotalot plugin:

There are some changes in the Plotalot query:

• A hyperlink to the article is created in the CONCAT line
• The hard prefix jl_ is replaced here by the self-detecting prefix #__
• Because this website is multilingual, the English language is selected in the WHERE line
• To keep this page clear, the number of lines is limited to 20

With these additions, this is the Plotalot query:

SELECT
CONCAT('<a target="_self" href="/%%J_ROOT_URI%%index.php/',
LEFT(#__content.language,2),
'/?option=com_content&view=article&id=',
#__content.id,'&catid=', #__content.catid,'">',
#__content.title,'</a>') AS Weblocation,
#__categories.path AS Categorypath,
#__content.hits AS Hits
FROM #__content LEFT JOIN
#__categories ON #__content.catid = #__categories.id
WHERE #__content.state = 1 and #__content.access = 1
and LEFT(#__content.language,2) = 'en'
ORDER BY #__categories.path, #__content.title LIMIT 20

Once you get the hang of making queries in MySQL, it is a small step to create new variations yourself.

Recent articles

SELECT
jl_content.title AS Article,
jl_categories.path AS Categoriepath,
date(created) AS Date,
jl_content.hits AS Hits
FROM jl_content
LEFT JOIN jl_categories ON jl_content.catid = jl_categories.id
WHERE jl_content.state = 1 and jl_content.access = 1
ORDER BY created DESC LIMIT 10

Most popular articles

SELECT
jl_content.title AS Article,
jl_categories.path AS Categorypath,
jl_content.hits AS Hits
FROM jl_content LEFT JOIN
jl_categories ON jl_content.catid = jl_categories.id
WHERE jl_content.state = 1 and jl_content.access = 1
ORDER BY jl_content.hits desc limit 10

Number of articles per category: 

SELECT
jl_categories.path AS Path,
COUNT(jl_categories.title) AS Quantity
FROM jl_content LEFT JOIN
jl_categories ON jl_content.catid = jl_categories.id LEFT JOIN
jl_categories jl_categories1 ON jl_categories.parent_id =
jl_categories1.id
WHERE jl_content.state = 1 and jl_content.access = 1
GROUP BY jl_categories.path
UNION
SELECT
'Total' AS Path,
COUNT(jl_content.title) AS Quantity
FROM jl_content
WHERE jl_content.state = 1 and jl_content.access = 1

Core data Joomla configuration:

SELECT 'Template' AS Description, template AS Value
FROM jl_template_styles
WHERE client_id=0 AND home=1
UNION
SELECT 'Components' AS Description, COUNT(name) AS Value
FROM jl_extensions
WHERE type = 'component'
UNION
SELECT 'Modules' AS Description,
SUM(CASE WHEN published = 1 THEN 1 ELSE 0 END) AS Value
FROM jl_modules
UNION
SELECT 'Active plugins' AS Description, COUNT(name) AS Value
FROM jl_extensions
WHERE type = 'plugin' AND enabled = 1
UNION
SELECT "All articles " AS "Description",COUNT(title) AS Value
FROM jl_content
UNION
SELECT "Public articles" AS "Description",COUNT(title) AS Value
FROM jl_content
WHERE jl_content.state = 1 and jl_content.access = 1

 Make sure that in each example you replace prefix jl_ with the prefix that you chose during your Joomla installation.