Suitable for a website with frequently changing data?

Permalink Browser Info Environment
Looking for an environment in which to build a site with access to a lot of data in a single location, with frequent changes. Not clear whether the Sources XX would be required, but MySQL or any suitable database would be fine. Would I be editing inside the Content Puller, or editing with a generic tool, and using this add on to format the content in a web page?

A prominent part of the site would be the ability of the visitor to search on a term, to filter down to the desired subset.

Am I making it clear enough? I've worked in C5 for many years, and would feel more comfortable there than trying to learn database use in WordPress. Thanks!

Type: Pre-Sale
Status: Archived
tomfromdare
View Replies:
JohntheFish replied on at Permalink Reply
JohntheFish
UCP wont help you edit your source data. Its up to you to enter that data into database table(s) or CSV file.

What you can do with UCP is to pull your source data onto a c5 page and format it. Within this, you have full control over caching. So if data is edited often, you would probably not want to enable caching in UCP.

For example, if you want to show a table of data, and the data is in a database table or csv file, UCP can be configured to pull in that data and show it as a table (or in various list formats) with pagination. Within this, you can configure UCP to not display columns you are not interested in.

If the data is in a database table, UCP can also work in association with an on-page form. So you can use another addon or HTML block to set up a form, then configure the query in UCP to use parameters from that form, and hence allow visitors to, for example, filter what is pulled from the database table or adjust the sort order.
tomfromdare replied on at Permalink Reply
tomfromdare
Thank you for the clarification. I will pursue this path further.
tomfromdare replied on at Permalink Reply
tomfromdare
I appreciate the great amount of documentation! Having read about CSV display, the one requirement that I wonder about is, if one of the fields is a URL can it be displayed as a clickable link? That is a critical feature for my use. Plus the ability for the visitor to filter the output which I believe you have addressed.
Aside from that I think this add on will do a fine job.
JohntheFish replied on at Permalink Reply
JohntheFish
Autolinking URLs found in text is an output option for links to pages and/or for images, so fully supported. In fact there are 2 autolinker options, the core autolinker for simple plain text and an advanced autolinker for more complex data.

The 'with form' filtering from a front end form is currently only supported for
- pulling from a URL, where the form parameters are inserted into the URL
- from a database, where the form parameters are inserted into the SQL query
(so not directly for csv sourced data, unless you first import the data into a database table)

I do have some thoughts about a more general purpose form-driven transform filter, but they are at present just ideas on how to do it and not implemented.
tomfromdare replied on at Permalink Reply
tomfromdare
Again, thank you for a timely and helpful reply!
tomfromdare replied on at Permalink Reply
tomfromdare
OK, I've purchased UCP and installed it. Lots of options! When I look at Transform, I don't find MySQL as an option, only Table from CSV or Table from HTML look similar. When filtering the record selection, many of those will be T/F fields. So if I can use MySQL I don't see how. If I use CSV shall I then populate T/F types with "yes" or "no" and select via text matching?
JohntheFish replied on at Permalink Reply
JohntheFish
The SQL filtering is in the content sources in the XX package. Its free if you have purchased UCP. I will grant you a license.

Easiest way to get to grip with the options is to 'view settings' from a similar example on my support site and then use the 'import' option in the block view.

For CSV, the yes/no you discuss sounds feasible.
JohntheFish replied on at Permalink Reply
JohntheFish
But SQL is probably best for front end filtering.
JohntheFish replied on at Permalink Reply
JohntheFish
tomfromdare replied on at Permalink Reply
tomfromdare
I don't mean to be a bother, and would consider hiring you to set this up the first time. It seemed easy to create a sample MySQL table with 4 columns for testing, but after 4 tries, C5 itself displays a screenful of errors (starting with TableNotFoundException) when I carefully edit database.php

When I put back the original content (where did it come from? ) the error goes away. But that original was a database that I created, only it has content I don't recognize, dozens of fields, starting with AreaLayoutColumns, AreaLayoutCustomColumns.

It would seem that working with CSV would be a slow way to go, so do you have any pointers for creating a MySQL file (I used CPanel wizard to create, then imported a SQL layout of the 4 fields, then imported the 4 fields of data, all without error, and it's fully browseable.
JohntheFish replied on at Permalink Reply
JohntheFish
The database with AreaLAyout.... etc is the concrete5 database. You need to be careful not to damage that.

Making some big guesses about your reported problem, perhaps you are confusing databases with tables. A database is a collection of tables of data. For example, the concrete5 database contains about 250 tables starting with 'AreaLayoutColums' and ending with 'WorkflowTypes'.

You have 2 ways to make your table of data available to MySQL. Option 2 is a little more involved, but safer because your data and the concrete5 data are separated into different databases.

1. Create your table within the concrete5 database, import your data to that table. You would use this with the 'Site Database'or 'Site Database with Form' content sources.

The advantage is you don't need to create and interface to a second database. The 'gotcha' most likely to cause issues is that the table name is case sensitive. Suppose your table is called 'MyDataTable'

Use phpMyAdmin to test a query
SELECT * FROM MyDataTable LIMIT 10 OFFSET 0

When editing the content source, that could be adapted to
SELECT * FROM MyDataTable LIMIT {{limit}} OFFSET {{offset}}

With that working, you can then progressively develop further by adding a sort order, filtering data etc.

2. Create a new database, completely separate to the concrete5 database. Perhaps with a name like 'my_database'. For this database you will have a database username and password. Create your table 'MyDataTable' in that new database. It would be the only table in an otherwise empty database.

From UCP you would access this with the 'Any Database'or 'Any Database with Form' content sources.

To connect UCP with this new database, you need to create a new 'connections' entry in database.php - do not change the existing 'concrete' entry. That will have the database username and password for your new database. You need to be careful here as damaging database.php will stop your site from working. Keep a copy of teh original file just in case.

Now in UCP, use the 'Any Database' content source. In the selector, you should see a choice between 'concrete' and 'my_database' - the database you have just created. Once you have selected that, the rest of this path is exactly the same as (1).

SELECT * FROM MyDataTable LIMIT {{limit}} OFFSET {{offset}}
tomfromdare replied on at Permalink Reply
tomfromdare
You are VERY helpful. I have added my database to database.php and from UCP chose Any Database with Form, then chose the new one called 'lookup'. After changing the Display option in response to the helpful warning, I now see the contents of my sample table on screen!

I will attempt next to add the filtering...
JohntheFish replied on at Permalink Reply
JohntheFish
Great!
Develop your SELECT query in phpMyAdmin or similar where you have considerably more diagnostics available, then copy it into UCP.

You will need to swap to the 'With Form' content source and to code a <form> element into an HTML block.

Then insert '?' placeholders into your query and map them to <form> input names.

Let me know how you get on.
tomfromdare replied on at Permalink Reply
tomfromdare
OK, that's a bit out of my comfort range.

From the docs: This content source does not provide the actual form. Providing the form is entirely up to you and in its simplest guise could be just an HTML block. Use of any of the forms blocks or packages may also be feasible, but you will need to disconnect the form display from actually submitting to the form controller. The form simply needs to refresh the page without actually doing anything,...

I don't know how to "disconnect the form" nor understand how UCP will get input from the form. My goal is a set of buttons, each filtering on a BOOLEAN in the table, eg Probiotic, Prebiotic, FODMAP etc where the result set is the sum of those filters.

I'm still open to paying for your help, as you've gone above and beyond a $50 add on.
JohntheFish replied on at Permalink Reply
JohntheFish
The easiest way to make sure the form doesn't connect back to ant controller in the core is to code it as html in an html block. If you leave <form> element with no attribute (save for a class="" to style it), submitting it will come straight back to the page with any form values in the query parameters.

It is only if you use a form block or addon that you need to be concerned about disconnecting it from the controller.

You can see an example of such an html form at "In more detail",
https://c5magic.co.uk/addons/universal-content-puller/example-site-d...

To get started, you could copy that into an html block above your UCP block.

You then need some inputs. You mentioned yes/no. so if you have a column_a with yes/no in it, your form would contain (untested) a checkbox for column_a with a value of 'yes'
<form class="ucp-attach-form form-inline">
    <input type="checkbox" name="column_a" class="form-control" value="yes">
    <label>Filter Column A</label>
    <input type="submit" value="Go" class="btn btn-primary">
</form>


Your select would then be
SELECT * FROM table_name WHERE `column_a` = ? ORDER BY ...... etc

And then in the next part of the UCP data:
Get/Post param: column_a
Default value: no

That would filter on 'yes' when checked, and 'no' when not checked. So flip/flop what results are shown.

If you wanted 'yes' when checked and all when not checked, then we need to get a little bit tricky. When a checkbox is not checked, it has no value in the form - it doesn't provide a 'no' - we provided that with the 'default' above.

SELECT * FROM table_name WHERE `column_a` LIKE CONCAT('%' ,?,'%') ORDER BY ...... etc

Then
Get/Post param: column_a
Default value:
(so no default value - you can see that in the example on c5Magic)

That will match on the column containing 'yes' when checked - LIKE '%yes%' - and containing anything - LIKE '%%' - when not checked. In an SQL LIKE statement, '%' is a wildcard.

You then need a similar query for the COUNT() part, which is essentially just a variation - as in the example.

For multiple checkboxes, you repeat similar in the <form> and then do 'AND LIKE...' in the SQL:
LIKE CONCAT('%' ,?,'%') AND LIKE CONCAT('%' ,?,'%') .....etc...

Get/Post param: column_a
Default value:

Get/Post param: column_b
Default value:

etc....
JohntheFish replied on at Permalink Reply
JohntheFish
You are obviously making an effort to learn, so I am quite happy to continue educating within this support forum while we are making progress.

If you would like to contract me to get it done quickly, then please email direct - details on my support site.
tomfromdare replied on at Permalink Reply 2 Attachments
tomfromdare
Me again :-)
I have got interaction between the form and the table display. Two problems have me stuck.

When I added the Get/Post param, the whole table disappeared, though no error was seen.

Also, I don't see how to add a second Get/Post param, though the arrow on the right apparently would let me change the order.
tomfromdare replied on at Permalink Reply
tomfromdare
Wait - if I make the Get-Post param default to 0, I get a result set. So how can I have about a dozen more of them?
JohntheFish replied on at Permalink Reply
JohntheFish
Extra field mapping rows are shown when you add a placeholder '?' for them in the SQL query.

ie.
With one '?' in the query, one field mapping row is shown
With two '?' in the query, two field mapping rows are shown
With three '?' in the query, three field mapping rows are shown
JohntheFish replied on at Permalink Reply
JohntheFish
I thought your filtering columns were yes/no.

If the column values are 1/0, then you can probably simplify the form and query to
<form class="ucp-attach-form form-inline">
    <input type="checkbox" name="column_a" class="form-control" value="1">
    <label>Filter Column A</label>
    <input type="checkbox" name="column_b" class="form-control" value="1">
    <label>Filter Column B</label>
    <input type="submit" value="Go" class="btn btn-primary">
</form>

SELECT * FROM table_name WHERE `column_a` >=? AND `column_b` >=?  ORDER BY ...... etc


The SQL will then show 2 field mapping rows. If you make the default of each '0' (zero) you will have a filter that activates when the respective checkbox is set.
tomfromdare replied on at Permalink Reply
tomfromdare
Yippee! With your coaching, I now have 2 filters, and each works to display a small subset of records. Also, since they are mutually exclusive (by chance) with both boxes checked the UCP correctly shows no results. Then creating one record with both filters true, I get just that one result.

I will send you a link when the site is presentable, but I am now confident to extend from 2 filters to many.

Two remaining questions: (1) can I have the checkmarks continue to show what was chosen in the form, or somehow have UCP display that? (2) Is it possible to display URLs as clickable links with the plain link displayed but the occasional affiliate link actually used? I have them as separate fields on a small percentage of the records.
JohntheFish replied on at Permalink Reply
JohntheFish
2) For the linking URLs, experiment with the autolink options in the Advanced tab.

1) That will need some php or javascript to repopulate the form fields from query parameters.

Which language are you happier with?
Does your theme already load jQuery? (check when not logged in because the dashboard always loads it)

Are you OK with creating a new block template in /application/blocks/html ? (see c5 documentation)

As a prelude to that, you could install my Safe HTML addon (free) and assign the Safe HTML block template to the HTML block with your form.https://c5magic.co.uk/addons/safe-html...

Alternatively, attack it from the other direction, go back a few steps and use a forms package to manage the form and disconnect the submission from the form controller - which will also involve some of the above codeing.
JohntheFish replied on at Permalink Reply
JohntheFish
Thinking further about (1), maintaining checkbox state is a generic problem that I expect others will encounter in the future. With that in mind, I will release a new version of UCP that takes care of maintaining associated form checkbox state.
JohntheFish replied on at Permalink Reply
JohntheFish
8.0.16 - Maintain checkbox settings in 'with Form' sources.

If you update UCP, it should now maintain checkbox settings for an associated form (the form must have class="ucp-attach-form")

concrete5 Environment Information

# concrete5 Version
Core Version - 8.2.1
Version Installed - 8.2.1
Database Version - 20170802000000

# concrete5 Packages
ExchangeCore reCAPTCHA (1.1.1), EZ Paypal (2.0.1), Peak (2.7), Quick Pay (1.0.1), Stucco (2.1.2)

# concrete5 Overrides
None

# concrete5 Cache Settings
Block Cache - On
Overrides Cache - On
Full Page Caching - Off
Full Page Cache Lifetime - Every 6 hours (default setting).

# Server Software
Apache

# Server API
litespeed

# PHP Version
7.1.33

# PHP Extensions
bcmath, bz2, calendar, Core, ctype, curl, date, dom, exif, fileinfo, filter, ftp, gd, gettext, gmp, hash, iconv, imagick, imap, json, libxml, litespeed, mbstring, mcrypt, mysqli, mysqlnd, openssl, pcntl, pcre, PDO, pdo_mysql, pdo_sqlite, pgsql, Phar, posix, readline, Reflection, session, shmop, SimpleXML, soap, sockets, SPL, sqlite3, standard, tidy, timezonedb, tokenizer, uploadprogress, wddx, xml, xmlreader, xmlrpc, xmlwriter, xsl, zip, zlib

# PHP Settings
max_execution_time - 1500
log_errors_max_len - 1024
max_file_uploads - 20
max_input_nesting_level - 64
max_input_time - 1500
max_input_vars - 2000
memory_limit - 1G
post_max_size - 1G
sql.safe_mode - Off
upload_max_filesize - 1G
mysqli.max_links - Unlimited
mysqli.max_persistent - Unlimited
pcre.backtrack_limit - 1000000
pcre.recursion_limit - 100000
pgsql.max_links - Unlimited
pgsql.max_persistent - Unlimited
session.cache_limiter - <i>no value</i>
session.gc_maxlifetime - 7200
soap.wsdl_cache_limit - 5

Browser User-Agent String

Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.108 Safari/537.36

Hide Post Content

This will replace the post content with the message: "Content has been removed by an Administrator"

Hide Content

Request Refund

You have not specified a license for this support ticket. You must have a valid license assigned to a support ticket to request a refund.