Joomla Weblinks Reporting with MySQL

An underrated Joomla component is Weblinks. This allows you to enter your favorite websites into a file. Weblinks were once part of the standard Joomla installation. Nowadays you have to install this component yourself.

The objection of many was that you only have limited presentation options for your web links via the standard modules. For example, you cannot select by category. This problem is overcome by third-party modules. I can especially recommend Cuter Weblinks. This offers a range of possibilities.

The purpose of this page is to show how you can present your own web links with a MySQL query on the Joomla database. 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.

I made these queries back in 2016. The great thing about Joomla's backward compatibility is that the database structure is unchanged. Fortunately, the old queries work without any glitches.

All weblinks in a row

This is the MySQL query:

SELECT
jl_weblinks.title AS Website,
jl_weblinks.url AS Url,
jl_weblinks.description AS Description,
jl_weblinks.hits AS Hits,
jl_categories.title AS Category,
jl_weblinks.id AS Id,
jl_categories.path AS Path
FROM jl_weblinks LEFT JOIN
jl_categories ON jl_weblinks.catid = jl_categories.id
WHERE jl_weblinks.state = 1 and jl_weblinks.access = 1
ORDER BY jl_categories.path, jl_weblinks.title

Explanation of the steps:

• Replace the prefix jl_ with the prefix you chose during your Joomla installation
• The web links are in the (prefix)weblinks table. The category is placed in the general table (prefix)categories, which is also used for articles
• 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 weblink

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

 There are some changes in the Plotalot query:

• A hyperlink to the weblink 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(#__weblinks.language,2),'/component/weblinks/?task=weblink.go&id=', #__weblinks.id,'">',#__weblinks.title,'</a>') AS Website,
#__categories.path AS Categorypath,
#__weblinks.hits AS Hits
FROM #__weblinks LEFT JOIN
#__categories ON #__weblinks.catid = #__categories.id
WHERE #__weblinks.state = 1 and #__weblinks.access = 1
and LEFT(#__weblinks.language,2) = 'en'
ORDER BY #__categories.path, #__weblinks.title LIMIT 20

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

Recent weblinks

SELECT
date(created) AS Date,
jl_weblinks.title AS Website,
jl_weblinks.url AS Url,
jl_weblinks.description AS Description,
jl_weblinks.hits AS Hits,
jl_categories.title AS Category,
jl_weblinks.id AS Id,
jl_categories.path AS Path
FROM jl_weblinks LEFT JOIN
jl_categories ON jl_weblinks.catid = jl_categories.id
ORDER BY date(created) DESC, jl_weblinks.title LIMIT 10

Most popular weblinks

SELECT
jl_weblinks.hits AS Hits,
jl_weblinks.title AS Website,
jl_weblinks.url AS Url,
jl_weblinks.description AS Description,
jl_categories.title AS Category,
jl_weblinks.id AS Id,
jl_categories.path AS Path
FROM jl_weblinks LEFT JOIN
jl_categories ON jl_weblinks.catid = jl_categories.id
ORDER BY jl_weblinks.hits DESC, jl_weblinks.title LIMIT 10

Total weblinks per category:

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

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