Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2015-03-26 00:15:54

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

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

#2 2015-03-26 01:26:42

Gocom
Developer Emeritus
From: Helsinki, Finland
Registered: 2006-07-14
Posts: 4,533
Website

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

#3 2015-03-26 03:01:20

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

Re: Reverse look-up article from article image

Thank you Jukka, you’re a star.

Offline

#4 2016-08-11 22:45:17

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

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

#5 2016-08-12 21:27:42

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

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

#6 2016-08-12 21:53:49

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

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

#7 2016-08-13 07:40:58

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

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

#8 2016-08-13 22:18:17

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

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

#9 2016-08-14 11:39:10

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

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

#10 2016-08-14 20:22:07

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

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

Board footer

Powered by FluxBB