A view can be created by running a CREATE VIEW command on your database.
- Open your database administration tool (usually PhpMyAdmin) and open a SQL query window.
- Create a view using the CREATE VIEW syntax (for starters, try something simple like "CREATE VIEW test AS SELECT * FROM oneofmydatabasetables").
- Save the view within the database and display it. if it displays fine and without SQL syntax error within your database, go to your Tools JX admin screen.
- In "Connection", hit the "Check and list tables" button, the new view "test" should display in the "Table Names in Database" dropdown list in the "Table Settings section".
- Select "test" from the dropdown list, set up "test" in the Tools JX admin screen like you would set up any other of the databases' tables to be displayed in a grid in an article.
CREATE VIEW examples
Create a view "ViewName" that pulls data from "Table1" and "Table2" which are connected by fields Table1.ID and Table2.ID
CREATE VIEW ViewName AS
SELECT *FROM Table1 t1, Table2 t2
WHERE t1.ID = t2.ID
Create a view pulling data from two tables using INNER JOIN
CREATE VIEW ViewName AS
SELECT *FROM Table1 t1
INNER JOIN Table2 t2
ON t1.ID = t2.ID
Create a view that enables you to dislay images in the Table JX or Card View JX. It creates a <img> tag from a field where only an URL of an image is stored.
CREATE VIEW ViewName AS
SELECT field1, field2, field3, CONCAT('<img src="', image_field, '" />') AS image
FROM mytable
Create a view that displays true/false images according to the "flag" field.
CREATE VIEW YourView AS
SELECT
(case
(when flag = True) then '<img src="pic1.gif" alt="P1" />'
(when flag = False) then '<img src="pic2.gif" alt="P2" />'
end) As Pic
FROM YourTable
SELECT
(case
(when flag = True) then '<img src="pic1.gif" alt="P1" />'
(when flag = False) then '<img src="pic2.gif" alt="P2" />'
end) As Pic
FROM YourTable
Two views that help to display the most recent article from each category. Unfortunately mysql does not support a subselect in 'from' clause at creating a view. This is why we need to create two views. First one selects the last date and the category for each category from the articles' table.
CREATE VIEW mxHelpView AS
SELECT catid, MAX(created) AS LastDate
FROM jos_content
GROUP BY catid
SELECT catid, MAX(created) AS LastDate
FROM jos_content
GROUP BY catid
With the help of this view we select the most recent articles from each category.
CREATE VIEW LatestArticles AS SELECT jos_content . *
FROM jos_content
INNER JOIN myHelpView ON myHelpView.catid = jos_content.catid
AND myHelpView.LastDate = jos_content.created
FROM jos_content
INNER JOIN myHelpView ON myHelpView.catid = jos_content.catid
AND myHelpView.LastDate = jos_content.created
It is obvious that a database view is a very powerfull tool for data manipulation. With the connection with Tools JX you can insert almost any set of data that you want straight into your Joomla article.
Maybe you will find the following links usefull: