Reading and displaying data from a custom table

[einsteinsboi]

I'm working on a new project and wanting to use Processwire for it, but I'm a bit stuck on how to execute it.

Here's the scenario. I intend to create a custom table within my database. This table will be populated and updated automatically via a cron job, so the content doesn't need to be edited within the CMS. Now what I want to do is display the data from this table in my website, reading it directly from the database. These pages will all fall into one broad category and then subcategories based on the data in the table.

What I'm wondering is how to instruct processwire to read the content from my custom table and display it as pages, one for each entry.

I imagine I would start by creating a template for these pages, and fields corresponding to the table columns that I want to display (I don't need to show all of them). Where do I go from here?? I'm sure it's a simple solution but I can't for the life of me think of how to approach it.

thanks

[SiNNuT]

This is an interesting question and i also am curious to hear how some seasoned pro's would approach this. Since it basically is just for display purposes i can't see why it would be necessary to create corresponding fields in PW and show the entries as pages.

Since you can basically do anything you want in PW templates i would setup one template to act as some sort of controller for your custom table. I'm not sure there is any benefit from using existing PW database classes, so you can just connect via PDO/mysqli or even 3rd party database/orm classes. You can then query the table and use the data in the template. You could use urlSegments to cater to your display needs and logic.

[Soma]

Maybe this is of interest.

[einsteinsboi]

This is an interesting question and i also am curious to hear how some seasoned pro's would approach this. Since it basically is just for display purposes i can't see why it would be necessary to create corresponding fields in PW and show the entries as pages.

Since you can basically do anything you want in PW templates i would setup one template to act as some sort of controller for your custom table. I'm not sure there is any benefit from using existing PW database classes, so you can just connect via PDO/mysqli or even 3rd party database/orm classes. You can then query the table and use the data in the template. You could use urlSegments to cater to your display needs and logic.

This is interesting, thanks for the response, but I'm not sure I follow. Maybe I should mention that I would like to add other fields to those pages that contain data from the custom table, I just wouldn't edit anything that comes from the table itself. So for example the table data has name, description, source url, image url, etc, and I want to add a custom field for my own review of the product. So these do need to be individual pages.

Maybe this is of interest. http://processwire.c...ndary-database/

Thanks Soma. What I intend to use is a custom table within the pw database, I don't want an additional database. Or am I missing something?

[Soma]

If it's a table in PW DB you can do SQL queries like this:

$result = $this->db->query("SELECT id, name, title, url FROM yourtablename WHERE id=$id");

Some modules use this , if you look at ProcessRedirects for example https://github.com/apeisa/ProcessRedirects

[SiNNuT]

Generally 'product' and 'review' would be two different entities which relate one-to-one or one-to-many. If i understand correctly the products are in your custom table fed by an external resource and you want to make product reviews in the form of PW pages that link to products and use (some of) it's data? Maybe you can tell in a little bit more detail how you would want things to look and work in the front-end (url structure etc.) and in the back-end.

www.mynewproject.com

-/reviews/ <- list reviews, do some category stuff

--/iphone5/ <- single review, PW page which uses some data from custom table

[ryan]

This sounds like a fun project! Does the data need to stay in this external table, or can it live in ProcessWire instead?

If the data can live in ProcessWire, your cron job could very easily update the data by just bootstrapping ProcessWire. Bootstrapping is as simple as: include("/path/to/pw/index.php"); One-time importing data from an external source is also very easy to do via the API. So if you can do it, I would just let the data live in ProcessWire instead of an external database, and it'll make the whole job a piece of cake.

But if that data needs to stay external, then Sinnut's solution is a good way to go. You would use the DB's primary key (or some other unique column) to serve as the urlSegment that loads the page. You'd setup one page/template to handle all that data, and it would find it like this:

$key = (int) $input->urlSegment1; 
if(!$key) throw new Wire404Exception();

$result = $yourDB->query("SELECT make, model, year FROM your_table WHERE id=$key"); 
if(!$item->num_rows) throw new Wire404Exception();

list($make, $model, $year) = $result->fetch_row();

echo "<ul>";
echo "<li>Make: $make</li>";
echo "<li>Model: $model</li>";
echo "<li>Year: $year</li>";
echo "</ul>";

Note: enable URL segments on the template where this code is (on the URLs tab), as URL segments are not enabled by default.

So for example the table data has name, description, source url, image url, etc, and I want to add a custom field for my own review of the product. So these do need to be individual pages.

This is another reason why it may make a lot of sense to keep all the data in ProcessWire. But if you can't do that, it won't be a problem: all you need is for your pages to contain a reference to the primary key of row they map to in your external table. I would suggest using the built-in "name" field for those pages to map to the primary key in your external table, because you know it'll be unique and relevant. But you could always create a separate integer or text field in ProcessWire do handle it too. But lets say you use 'name', then your page templates could load the data like this:

$result = $yourDB->query("SELECT make, model,year FROM your_table WHERE id='{$page->name}'");  

[slkwrm]

Hi, iensteinsboi and welcome!

I remember reading your tutorials while learning basics of MODx. Thank you! They were very helpful.

You will be amazed of simplicity of ProcessWire and really friendly community here and hope you will soon become a convert. Yeah, I know you love both Drupal and MODx, but I'm sure you will fall in love with ProcessWire as well

[einsteinsboi]

Generally 'product' and 'review' would be two different entities which relate one-to-one or one-to-many. If i understand correctly the products are in your custom table fed by an external resource and you want to make product reviews in the form of PW pages that link to products and use (some of) it's data? Maybe you can tell in a little bit more detail how you would want things to look and work in the front-end (url structure etc.) and in the back-end.

www.mynewproject.com

-/reviews/ <- list reviews, do some category stuff

--/iphone5/ <- single review, PW page which uses some data from custom table

Perhaps I didn't explain it well I don't want them to be separate entities, this was just an example. What I have is an XML datafeed from a vendor that I work with. I need each entry from the feed to appear as a page on the site, with selected fields displayed. I then need to add content to those same pages, not altering the feed content but adding fields to the page. The content from the vendor will be updated periodically to the db via a cron job and the contents of the db should then be refreshed on the page, but the custom content I'm adding may or may not change. I also want to use some of the fields in the db for meta data to appear in the head section of the page.

Does this make more sense? Thanks for your help

[einsteinsboi]

Ryan, thank you for your input. This looks promising I've responded to specifics below:

This sounds like a fun project! Does the data need to stay in this external table, or can it live in ProcessWire instead?

My thought was to put the data into the Processwire database but in its own table so that any updates to PW don't interfere with the custom data. Is this what you mean?

If the data can live in ProcessWire, your cron job could very easily update the data by just bootstrapping ProcessWire. Bootstrapping is as simple as: include("/path/to/pw/index.php"); One-time importing data from an external source is also very easy to do via the API. So if you can do it, I would just let the data live in ProcessWire instead of an external database, and it'll make the whole job a piece of cake.

I like the idea of "piece of cake" Can you explain this a bit more? I'm not quite familiar with bootstrapping PW yet

But if that data needs to stay external, then Sinnut's solution is a good way to go. You would use the DB's primary key (or some other unique column) to serve as the urlSegment that loads the page. You'd setup one page/template to handle all that data, and it would find it like this:

$key = (int) $input->urlSegment1;
if(!$key) throw new Wire404Exception();

$result = $yourDB->query("SELECT make, model, year FROM your_table WHERE id=$key");
if(!$item->num_rows) throw new Wire404Exception();

list($make, $model, $year) = $result->fetch_row();

echo "<ul>";
echo "<li>Make: $make</li>";
echo "<li>Model: $model</li>";
echo "<li>Year: $year</li>";
echo "</ul>";

Note: enable URL segments on the template where this code is (on the URLs tab), as URL segments are not enabled by default.

This is another reason why it may make a lot of sense to keep all the data in ProcessWire. But if you can't do that, it won't be a problem: all you need is for your pages to contain a reference to the primary key of row they map to in your external table. I would suggest using the built-in "name" field for those pages to map to the primary key in your external table, because you know it'll be unique and relevant. But you could always create a separate integer or text field in ProcessWire do handle it too. But lets say you use 'name', then your page templates could load the data like this:

$result = $yourDB->query("SELECT make, model,year FROM your_table WHERE id='{$page->name}'"); 

Thanks again, I will play with this code and see what I get So far I've only built really simple sites with PW so I'm pretty excited about doing some more indepth stuff and getting my hands dirty with some PHP and the API

Hi, iensteinsboi and welcome!

I remember reading your tutorials while learning basics of MODx. Thank you! They were very helpful.

You will be amazed of simplicity of ProcessWire and really friendly community here and hope you will soon become a convert. Yeah, I know you love both Drupal and MODx, but I'm sure you will fall in love with ProcessWire as well

Thanks for the welcome Glad you found the tuts useful. I'm already sold on PW I've built a few simple sites with it and understand the basics, but I'm looking to delve deeper now, play with the API and do some more complex stuff.

[SiNNuT]

Perhaps I didn't explain it well I don't want them to be separate entities, this was just an example. What I have is an XML datafeed from a vendor that I work with. I need each entry from the feed to appear as a page on the site, with selected fields displayed. I then need to add content to those same pages, not altering the feed content but adding fields to the page. The content from the vendor will be updated periodically to the db via a cron job and the contents of the db should then be refreshed on the page, but the custom content I'm adding may or may not change. I also want to use some of the fields in the db for meta data to appear in the head section of the page.

Does this make more sense? Thanks for your help

Ah, this does make a lot of sense and a perfect fit for PW. Import the xml data feed entries as PW pages and keep up to date via a cronjob. In this case i wouldn't go for a separate database.

Create a template "feed_item". Add fields to the template that map to corresponding data you want to import from the xml feed. You can add as many of your own fields to go next to that. For the actual importing/updating you can write something yourself with the PW API, i'm a noob so can't really help you there but i'm sure Ryan or other veterans can help. You could also take a look a look at this (alpha) module for some inspiration or maybe it works for your use case:

http://modules.proce...ss-data-import/

http://processwire.c...ller/#entry1647

Using this approach you eventually have all of the feed data and your own added fields as PW pages/data. From there you can display and do with it whatever you want.

[apeisa]

You could also take a look a look at this (alpha) module for some inspiration or maybe it works for your use case:

http://modules.proce...ss-data-import/

http://processwire.c...ller/#entry1647

Using this approach you eventually have all of the feed data and your own added fields as PW pages/data. From there you can display and do with it whatever you want.

I hope to get some time to finish that data import module. Although I feel like dropping XML support and make it work well with JSON first. JSON is so much simpler data model without all those attributes and other nasty surprises that XML is full of.

[SiNNuT]

I hope to get some time to finish that data import module. Although I feel like dropping XML support and make it work well with JSON first. JSON is so much simpler data model without all those attributes and other nasty surprises that XML is full of.

I agree, there are still a lot of data APIs/services that serve up xml but JSON is not far behind and growing fast. XML usage will shrink the next years.

[ryan]

Based on what I'm understanding from your last message, I think you should skip keeping the separate table. It just sounds like extra, unnecessary work, unless there's something more to this project that I don't yet understand.

Instead, I think you should have your cron job execute a script that bootstraps ProcessWire and takes care of all the adding, updating and deleting of records consistent with the web service you are reading from. This is something that I think ProcessWire is particularly good at, because it's been designed for this from the beginning (it's something I have to do with a lot of my client work).

Whether XML or JSON doesn't matter much, as PHP includes the ability to read from either type quite easily. Though like the other guys here, I generally prefer JSON just because it's less verbose and less fuss. If JSON, you'll pull the feed and use PHP's json_decode() to convert it to an array. If XML, you'll use PHP's SimpleXML to convert it to an array. Once you've got the array of raw data, you'll iterate through it and add, update, or delete pages in ProcessWire to make it consistent with the data you are pulling from the web service.

Live, working example

I think that the best way to demonstrate it is with a live, working example. This one uses the existing modules.processwire.com/export-json/ feed. You might also want to see the feed in human-readable mode to get a better look at the format.

Below is a shell script that bootstraps ProcessWire, reads from that feed and maintains a mini "modules directory" site, on your own site. I made this feed so that it can be tested and used on a brand new installation using the basic profile (included with PW). If left how it is, it'll create a mini modules directory site below the '/about/what/' page and use the template 'basic-page' for any pages it adds. But you can run this on any ProcessWire installation by just editing the script and changing the parent from '/about/what/' to something else, and changing the template from 'basic-page' to something else, if necessary.

This script assumes that the template used has 3 fields: title, body, and summary. The 'basic-page' template in PW's default profile already has these. If you adapt this for your own use, you'd probably want to change it to use more specific fields consistent with what you need to store on your pages. In this example, I'm just building a 'body' field with some combined data in it, but that's just to minimize the amount of setup necessary for you or others to test this… The purpose is that this is something you can easily run in the default profile without adding any new templates, fields, pages, etc.

1. Paste the following script into the file import-json.php (or download the attachment below). For testing purposes, just put it in the same directory where you have ProcessWire installed. (If you place it elsewhere, update the include("./index.php"); line at the top to load ProcessWire's index.php file).

2. Edit the import-json.php file and update the first line: "#!/usr/bin/php", to point to where you have PHP installed (if not /usr/bin/php). Save.

3. Make the file executable as a shell script:

chmod +x ./import-json.php

4. Run the file at the command line by typing "./import-json.php" and hit enter. It should create about 95 or so pages under /about/what/. Take a look at them. Run it again, and you'll find it reports no changes. Try making some changes to the text on 1 or 2 of the pages it added and run it again, it should update them. Try deleting some of it's pages, and it should add them back. Try adding some pages below /about/what/ on your own, run it again, and it should delete them.

import-json.php

#!/usr/bin/php
<?php // replace the path in the shabang line above with the path to your PHP

// bootstrap ProcessWire. Update the path in the include if this script is not in the same dir
include("./index.php"); 
// if you want to run this as a PW page/template instead, remove everything above (except the PHP tag)

// save our start time, so we can find which pages should be removed
$started = time();

// keep track of how many changes we've made so we can report at the end
$numChanged = 0;
$numAdded = 0;
$numTrashed = 0;

// URL to our web service data
$url = 'http://modules.processwire.com/export-json/?apikey=pw223&limit=100';

// get the data and decode it to an array
$data = json_decode(file_get_contents($url), true);

// if we couldn't load the data, then abort
if(!$data || $data['status'] != 'success') throw new WireException("Can't load data from $url");

// the parent page of our items: /about/what/ is a page from the basic profile
// update this to be whatever parent you want it to populate...
$parent = wire('pages')->get('/about/what/');
if(!$parent->id) throw new WireException("Parent page does not exist"); 

// iterate each item in the feed and create or update pages with the data
foreach($data['items'] as $item) {

 // see if we already have this item
 $page = $parent->child("name=$item[name]");

 // if we don't have this item already then create it
 if(!$page->id) {
   $page = new Page();
   $page->parent = $parent;
   $page->template = 'basic-page'; // template new pages should use
   $page->name = $item['name'];
   echo "\nAdding new page: $item[name]";
   $numAdded++;
 }

 // now populate our page fields from data in the feed
 $page->of(false);  // ensure output formatting is off
 $page->title = $item['title'];
 $page->summary = $item['summary'];

 // To keep it simple, we'll just populate our $page->body field with some combined
 // data from the feed. Outside of this example context, you'd probably want to
 // populate separate fields that you'd created on the page's template.
 $body = "<h2>$item[summary]</h2>";
 $body .= "<p>Version: $item[module_version]</p>";
 foreach($item['categories'] as $category) $body .= "<p>Category: $category[title]</p>";
 $body .= "<p><a href='$item[download_url]'>Download</a> / <a href='$item[url]'>More Details</a></p>";
 $page->body = $body;

 // print what changed
 $changes = $page->getChanges();
 if(count($changes)) {
   $numChanged++;
   foreach($changes as $change) echo "\nUpdated '$change' on page: $page->name";
 }

 // save the page
 $page->save();
}

// now find pages that were not updated above, which indicates they 
// weren't in the feed and should probably be trashed
$expired = $parent->children("modified<$started");
foreach($expired as $page) {
 echo "\nTrashing expired page: $page->name";
 $page->trash(); // move to trash
 $numTrashed++;
}

echo "\n\n$numAdded page(s) were added";
echo "\n$numChanged page(s) were changed";
echo "\n$numTrashed page(s) were trashed\n";

import-json.php.txt

Running the script as a cron job:

You can instruct your cron job to run the script and it should be ready to go. You may want to move it to a non web accessible location for more permanent use. You'll also want to update your bootstrap "include()" line at the top to have the full path to your ProcessWire index.php file, as your cron job probably isn't executing it from the web root dir like you were manually.

Running the script as a template file:

You can run this script as a template file on a page by removing the include() line and everything above it with this line:

<pre><?php

Place it in your /site/templates/ directory, add the template from PW admin, and create a page that uses it, then view it.

[einsteinsboi]

This is brilliant Ryan! Thank you so much, can't wait to play with this example and then adapt it to my needs. My vendor's feed comes in XML format so I'll work with that for now and then see if I can't convince them to use JSON.

I had thought that using the db was the best approach but I see that this may be even better and more efficient. I will work on this and report back

[einsteinsboi]

Ah, this does make a lot of sense and a perfect fit for PW. Import the xml data feed entries as PW pages and keep up to date via a cronjob. In this case i wouldn't go for a separate database.

Create a template "feed_item". Add fields to the template that map to corresponding data you want to import from the xml feed. You can add as many of your own fields to go next to that. For the actual importing/updating you can write something yourself with the PW API, i'm a noob so can't really help you there but i'm sure Ryan or other veterans can help. You could also take a look a look at this (alpha) module for some inspiration or maybe it works for your use case:

http://modules.proce...ss-data-import/

http://processwire.c...ller/#entry1647

Using this approach you eventually have all of the feed data and your own added fields as PW pages/data. From there you can display and do with it whatever you want.

THANK YOU to you and Ryan I now have a much better approach to my problem