Page 1 of 1

Specialized Search Question

Posted: Thu Feb 27, 2020 7:43 am
by CGI1984
We have a few forums which have a lot of posts and are supposed to be forums which only contain topics with questions. So we want to be sure each subject header in these forums contains a question mark.

Thus, I am wondering if there a way we ca do a search to help reveal any topics in these forums which are missing the question mark in the subject header? I know the indexing usually doesn't allow you to search for a single digit, so I am not sure if this kind of a search is possible to do. Thank you.

Re: Specialized Search Question

Posted: Thu Feb 27, 2020 11:07 am
by HiFiKabin
What about questions that do not have a question mark

Like that one

Re: Specialized Search Question

Posted: Thu Feb 27, 2020 12:13 pm
by KYPREO
You can configure Sphinx to index question marks, but it's a lot of work for a simple task.

THEN what you're really asking for is a search for topic titles NOT ending in a question mark. That could be done but would require custom coding of the search function.

You could just run this query against the database:

Code: Select all

SELECT topic_id, topic_title FROM phpbb_topics WHERE topic_title NOT LIKE '%?';
This will find topics where the topic title does not end in a question mark (substitute your database table name prefix if required).

Re: Specialized Search Question

Posted: Thu Feb 27, 2020 12:58 pm
by CGI1984
Thank you for that. I will give it a try. :)

Re: Specialized Search Question

Posted: Sat Feb 29, 2020 8:22 pm
by stevemaury
That gives you posts where "?" appears anywhere. This is more precise and faster:

Code: Select all

SELECT topic_id, topic_title FROM phpbb_topics WHERE RIGHT(topic_title,1) = '?'

Re: Specialized Search Question

Posted: Sun Mar 01, 2020 2:53 am
by KYPREO
stevemaury wrote:
Sat Feb 29, 2020 8:22 pm
That gives you posts where "?" appears anywhere. This is more precise and faster:

Code: Select all

SELECT topic_id, topic_title FROM phpbb_topics WHERE RIGHT(topic_title,1) = '?'
The OP wanted to find topics that do not end in a question mark. My query does that. You can do it your way of course but it needs to be inverted with a NOT.

Re: Specialized Search Question

Posted: Sun Mar 01, 2020 9:55 pm
by stevemaury
Thanks. Misread it. Yes, either will work but yours will hit on a "?" anywhere. Here is my corrected query:

Code: Select all

SELECT topic_id, topic_title FROM phpbb_topics WHERE RIGHT(topic_title,1) != '?'

Re: Specialized Search Question

Posted: Mon Mar 02, 2020 8:46 am
by AmigoJack
stevemaury wrote:
Sun Mar 01, 2020 9:55 pm
but yours will hit on a "?" anywhere
No, that's wrong on your end, KYPREO got it right right away. Also performing a string operation on every potential match (RIGHT) surely hits more performance than using an operator (LIKE) - how can that be faster for you?

Note that both the string function RIGHT and the operator != might not be supported on all DBMSs, whereas both operators LIKE and <> must be supported on any. We don't know OP's DBMS yet.

Re: Specialized Search Question

Posted: Tue Mar 03, 2020 6:17 pm
by stevemaury
Yes, in re-reading the OP, he wants all topic titles that do not CONTAIN a "?", although I originally thought he wanted all titles that did not END with a "?", so, yes KYPREO's initial query would work.

And yes, <> is probably preferable to !=. I tend to assume MySQL or MariaDB is being used if the poster does not specify.