Example - Chart from SQLite
Here we have a more complex example. An SQLite database is uploaded to the Concrete File Manager and pulled using the SQLite File Manager with Form content source.
The database is the well used chinook.db example database for music sales data. Whilst there is plenty of data to play with, we have kept it relatively simple by querying to summarize sales data by country.
The form input Filter is connected to a partial match of the country name. When clicked, the page will reload with a reduces subset of countries. For example, try entering "land".
More details about how it is done
Universal Content Puller XX Sources includes sources for pulling from SQLite databases. To provide some data to work on, the publicly available chinook.db used in many datbase training and education exercises has been uploaded to the file manager so it is available as a ConcreteCMS file. As a prerequisite, the file extension ".db" was added to the allowed extensions at Dashboard > System & Settings > Files > Allowed File Types.
The SQLite File Manager With Form content source was then used to select the chinook.db file and from that pull the sales data with some SQL. This is in the SQLite dialect of SQL, which if you are familiar with MySQL has some small differences.
In the query below, we have one placeholder for a partial match on the Country provided by the form. Concatenation is managed by the || operator. In MySQL the equivalent would have been CONCAT('%',?,'%'). We need concatenation because the ? placeholder cannot be inside a string.
The form is simple HTML in an HTML Block.
SELECT `BillingCountry` AS 'Country',
SUM(`Total`) AS 'Total'
WHERE `BillingCountry` LIKE '%'||?||'%'
GROUP BY `BillingCountry`
ORDER BY `BillingCountry`
<form class="ucp-attach-form form-inline pull-right">
<input type="text" name="Country" class="form-control">
<input type="submit" value="Filter" class="btn btn-primary">
Transform and display
Having pulled data from the SQLite database and named the columns nicely in the SQL, the transform is a simple transpose with the Array Hacker transform.
Display is a Bar Chart from Universal Content Puller Charts.
Below we have the same data shown as a table. The SQLite File Manager With Form content source is identically configured so it responds to the Country filter above. No transform is required and the display is a Table.
|Country||Total Sales $|