import from csv file, every night

giampablo · 2014-10-31 18:18:39

For a site redesign (now with textpattern), the Client is asking for a custom type of search.
Instead of searching in articles, the search query should be performed in the client’s large catalogue of products, now being offered in his online shop residing in another site. The query must return the products (not the articles) matching the search terms.

I guess I just need to create a new (searchable) section for products, using the title field for item name, body for description, cat field for product category and a custom field for the url of the product image.
Then, I must import the entire product list from the e-shop db into mySql db.

The problem is, products are added/edited very often, almost on a daily basis.
The e-shop site is custom-made and maintained by another software house, using a postgreSQL database.
The software house can provide me with a csv file, extracted every night from their db.

What I need:

Has each product to be entered as a new txp article or is there a better, more efficient and elegant way to manage the problem?
a cron-job script to import the csv file in textpattern mySql db. No experience here… Please help.

Please take note that the client does not need to display his entire product list anywhere in the site, except for the search result.

Can anybody help me?

maruchan · 2014-10-31 19:43:58

Then, I must import the entire product list from the e-shop db into mySql db.

Too bad they couldn’t be bothered to provide a search API. :-/

Your #2 could work, but I’d just import it into a separate DB and build your own basic search script that returns JSON. Then TXP could work with that using a plugin or an included script, and if you ever needed to use the catalog data from a non-search feature (like add things to a sidebar somewhere), you could just expand the search script without needing to painfully extract it from your site’s search feature. :-)

jakob · 2014-11-01 09:21:18

Importing that all to textpattern just for search purposes sounds like a huge bother.

One idea: Maybe you can find a php script that searches the csv file, or if that’s too slow due to the size of the csv file, a php script that indexes it first and then searches the index.

If that’s possible, you could simply call that php script to the end of your search page, passing the search term from txp into the script, and making the script’s output match your txp search results code. You’d then get a separate set of search results on the same page as your txp search results.

—

If you do go the path of importing the csv into a separate database via a cron job, two untried ideas that you might want to investigate:

It might be possible to import the csv as is (i.e. not converting it into textpattern structure) and then use a combination of rah_swap and smd_query to search it in its native format. rah_swap switches to another database connection for the duration of the tag but I’ve not used it before, so I’m not sure how well it works with non-txp database structures or even if that’s possible at all. smd_query allows you to conduct a custom search query and if it’s still available to use during the db-swap, then you could potentially search just the relevant tables of the parallel database to obtain your search results.
If you do convert the relevant csv data to the textpattern database layout first and then import that to a separate txp database, you might find rah_external_output useful. You’d use the plugin on the parallel database with the product data as a way of outputting the search results for a particular search term (ideally passed to it as an url variable, see adi_gps or rah_gps), and from your main txp-installation, you’d call it remotely passing the search term via the url, e.g. something like http://catalog.mysite.com/?rah_external_output=product-search&q=search-term. That’s the theory, anyway.

If you try any of these, I’d be interested to hear what works (or not).

giampablo · 2014-11-01 12:00:14

Thanks guys, great ideas.

maruchan wrote #285325:

Too bad they couldn’t be bothered to provide a search API. :-/

This was one of the options they investigated together with me, as well as a php script writing directly to mySQL. Too bad their boss ended up deciding for a simple cvs file. And anyway they are going to write a cronjob exporting a cvs… No comment.
As I said, only a search function is required at the moment, so I think I will follow the route suggested by Jakob

jakob wrote #285342:

Importing that all to textpattern just for search purposes sounds like a huge bother.

One idea: Maybe you can find a php script that searches the csv file, or if that’s too slow due to the size of the csv file, a php script that indexes it first and then searches the index.

Exactly. Elaborating your suggestion to use the csv file as it is I found this:

CSV to web service, a PHP script to pull data from a CSV file on the web and turn it into a search ~~and filter~~ interface.
This way there is no need to import the cvs file, using it as it is, and I could simply find a way to plug into <txp:search />.

Do you believe this is feasible? Maybe it will be too slow?

Textpattern CMS

Textpattern CMS support forum

#1 2014-10-31 18:18:39