Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Reverse look-up article from article image
Given a known image_id, how could I look up the articles where that image is listed? Some of my articles have multiple image ids specified in their article_image field, comma-seperated…
Thanks
Offline
Re: Reverse look-up article from article image
It would require running custom SQL statement such as:
select * from textpattern where FIND_IN_SET(13, Image);
You can run it with a plugin such as smd_query, etc_query or rah_function, or with some pure PHP:
<!-- Find out articles that have the current image attached in them and store the results list in a variable -->
<txp:variable name="image_articles"><txp:php>
$id = (int) parse('<txp:image_info type="id" />');
echo implode(', ', safe_column('ID', 'textpattern', "FIND_IN_SET({$id}, Image)"));
</txp:php></txp:variable>
<!-- Check the variable 'image_articles' and display appropriate articles with article_custom -->
<txp:if_variable name="image_articles" value="">
<!-- Not used anywhere -->
<txp:else />
<txp:article_custom id='<txp:variable name="image_articles" />' wraptag="ul" break="li">
<txp:title />
</txp:article_custom>
</txp:if_variable>
Offline
Re: Reverse look-up article from article image
Thank you Jukka, you’re a star.
Offline
Re: Reverse look-up article from article image
Gocom wrote #289422:
You can run it with a plugin such as smd_query, etc_query or rah_function, or with some pure PHP:
<txp:php> $id = (int) parse('<txp:image_info type="id" />'); echo implode(', ', safe_column('ID', 'textpattern', "FIND_IN_SET({$id}, Image)")); </txp:php>
Do you have any pointers as to how I can add a sort (e.g. Posted desc) to the implode function? My experiments with adding ORDER BY
in various ways isn’t doing me any good, and every example I find uses a different syntax :)
Thanks
Offline
Re: Reverse look-up article from article image
This perhaps (not tested)?
<txp:php>
$id = (int) parse('<txp:image_info type="id" />');
echo implode(', ', safe_column('ID', 'textpattern', "FIND_IN_SET({$id}, Image) SORT BY Posted DESC"));
</txp:php>
Offline
Re: Reverse look-up article from article image
Thanks Ruud. I gave it a go:
echo implode(', ', safe_column('ID', 'textpattern', "FIND_IN_SET({$id}, Image) SORT BY Posted DESC"));
returns no results : (
Interestingly,
echo implode(', ', safe_column('ID', 'textpattern', "FIND_IN_SET({$id}, Image)", "SORT BY Posted DESC"));
returns 10 results, but not the hundreds I’m expecting : |
Do you have any advice on how I can add a LIMIT function?
Thanks again
Gary
Offline
Re: Reverse look-up article from article image
Please switch the website to debug mode and check in the HTML source which SQL query both attempts generate.
Offline
Re: Reverse look-up article from article image
Thanks Ruud. My debug showed an error in the syntax. Changing ‘SORT’ to ‘ORDER’ fixed that:
<txp:php>
$id = (int) parse('<txp:image_info type="id" />');
echo implode(', ', safe_column('ID', 'textpattern', "FIND_IN_SET({$id}, Image) ORDER BY Title ASC"));
</txp:php>
I get this in debug:
<txp:php>
<txp:image_info type="id" />
[SQL (0.000105142593384): select ID from v0_textpattern as textpattern where FIND_IN_SET(1930, Image) ORDER BY Title ASC]
</txp:php>
…again and again, once for each article that is found i.e. the article sort order remains unchanged. It looks to me that the ‘ORDER BY’ statement is applying individually to each found article, and not to the list of articles as a group which is what I’m looking for.
Another thing I’m looking to do is limit the list of found articles to ‘Status = 4’; my experiments in placing this at various parts of the implode statement yield all kinds of interesting results, none of which are the one I want!
Offline
Re: Reverse look-up article from article image
LOL… it took me a while to see this, but you’re trying to order the table on a column (Title) that is not in the select list (only Id). That doesn’t work. You can’t use safe_column to get what you want, but you can get the same result with a bit more code if you use one of the other safe_ functions to retrieve the data. The query you want is:
SELECT ID, Title
FROM v0_textpattern AS textpattern
WHERE FIND_IN_SET(1930, Image) AND Status = 4
ORDER BY Title ASC
Offline
Re: Reverse look-up article from article image
Thank you Ruud!
Ahem. I should know this after 16 years of using TXP, but how do I wrap that statement with echo/implode/whatever to achieve my comma-separated list of article ids?
:-o
Offline