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!
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 |
Thank you for the clarification. I will pursue this path further.
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.
Aside from that I think this add on will do a fine job.
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.
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.
Again, thank you for a timely and helpful reply!
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?
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.
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.
But SQL is probably best for front end filtering.
You should now have a license to XX sources
https://www.concrete5.org/marketplace/addons/universal-content-pulle...
https://www.concrete5.org/marketplace/addons/universal-content-pulle...
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.
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.
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}}
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}}
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...
I will attempt next to add the filtering...
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.
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.
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.
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.
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'
Your select would then be
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.
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:
Get/Post param: column_a
Default value:
Get/Post param: column_b
Default value:
etc....
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....
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.
If you would like to contract me to get it done quickly, then please email direct - details on my support site.
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.
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.
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?
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
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
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
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.
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.
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.
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.
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.
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.
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.
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")
If you update UCP, it should now maintain checkbox settings for an associated form (the form must have class="ucp-attach-form")
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.