Skip Navigation

[Closed] Alphabetical filter for first letter of a custom field

This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.

Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.

This topic contains 9 replies, has 3 voices.

Last updated by Christian Cox 1 month, 1 week ago.

Assigned support staff: Christian Cox.

Author
Posts
#1763539

Hello,

I'm trying to setup an alphabetical filter for the first letter of my custom field "wissenschaftler-nachname" (scientist's surname/last name).

I could get the achieved filter set up using the post title with the following support tickets:
https://toolset.com/forums/topic/help-with-custom-alphabetic-searchdisplay/
https://toolset.com/forums/topic/alphabetical-and-numeric-search-buttons/

To be able to autoupdate the filtered view with links I followed the steps in this thread: https://toolset.com/forums/topic/i-want-to-remove-the-searchbutton-in-a-views-search/

Then I tried to adjust the code with the help of the following support ticket: https://toolset.com/forums/topic/alphabetic-filter/. But this isn't working.

You can find my view embeded here: hidden link. The alphabetical filter is at the top (A, B, C, D).
The filter worked while using the post title, although it used all letters of the title, not only the first one as planed. But now it seems to be not working at all anymore.

Can you help me with this please? I can grant access if you need. Just let me know.

Thank you in advance and kind regards
Sean

#1764319

Christian Cox
Supporter

Languages: English (English )

Timezone: America/New_York (GMT-04:00)

Then I tried to adjust the code with the help of the following support ticket...But this isn't working.
Hello, can you provide the code you used so I can take a look at your modifications? Or if you'd prefer, you can provide access in the private reply fields here and I can take a closer look. Please let me know where to find the custom code you have created for this View.

Thank you!

#1767527

Christian Cox
Supporter

Languages: English (English )

Timezone: America/New_York (GMT-04:00)

Hi, are the filter results accurate at this URL?
hidden link

#1767975

Hello Christian,

Thank you for your answer.

Yes the filter results are correct at hidden link and hidden link.
But the filter doesn't seem to work at hidden link and hidden link.

How can this be fixed to only search for the first letter?
Aditionally: Can the filter be adjusted to always show all first letters? Now at hidden link only "Alle" and "B" is shown.

Kind regards
Sean

#1768509

Shane
Supporter

Languages: English (English )

Timezone: America/Jamaica (GMT-05:00)

Hi Sean,

Christian is currently on a Public Holiday today but he will be back tomorrow to continue assisting.

Thank you for the continued patience.

#1769635

Christian Cox
Supporter

Languages: English (English )

Timezone: America/New_York (GMT-04:00)

How can this be fixed to only search for the first letter?
Custom SQL really falls outside the scope of support we provide here in the forums, but I can offer some advice to help you find your own custom solution. It looks like your posts_where_first_letter filter code is attempting to directly manipulate the SQL query to replace a specific string with a different string. However, the "source" string (i.e. $where) is not identical to the string produced by the SQL query, so no replacement is happening. To analyze the actual SQL query string, go to Toolset > Settings > Front-end Content and turn on " Enable Views debug mode" and choose the full debug mode. Reload the page hidden link and a popup will be shown (you may need to disable your popup blocker to see this). In the popup, you will be able to find the MySQL query generated for each View on the page. Look for the MySQL query generated for the View 992, and you will see something like this:

SELECT   wp_posts.* FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1  AND wp_posts.ID NOT IN (981) AND ( 
  wp_postmeta.meta_key = 'wpcf-wissenschaftler-nachname' 
  AND 
  ( 
    ( mt1.meta_key = 'wpcf-wissenschaftler-nachname' AND mt1.meta_value LIKE '{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}D{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}' )
  )
) AND wp_posts.post_type = 'wissenschaftlerinnen' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC, wp_posts.post_title DESC 

The problem in this query is here:

( mt1.meta_key = 'wpcf-wissenschaftler-nachname' AND mt1.meta_value LIKE '{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}D{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}' )

Instead you want to test only the first letter, so you need something like this:

( mt1.meta_key = 'wpcf-wissenschaftler-nachname' AND mt1.meta_value LIKE 'D{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}' )

That effectively removes the first '%' in the query. The long string {...} represents a % symbol, and it is not static.

If you look in your PHP filter code, you can see that the string replacement "search" text is searching for text that does not exist in the actual MySQL query. For example, "CAST AS" does not exist in the actual SQL query string. So your search text must be adjusted to compensate for the differences in the source text and the replacement text. The hash value that represents '%' here will be different each time the query is submitted, so you cannot consider that hash to be static. If you are skilled with Regular Expressions, this should be pretty straightforward for you. I am not very skilled with Regular Expressions, so I don't have a simple cut-and-paste solution for you. Instead, I suggest you use PHP's strpos method to find the position of the first instance of AND mt1.meta_value LIKE, then find the position of the next instance of }. Knowing those two string positions would give you the ability to strip out the first hash in the query string, which would change the query to search only the first letter.
https://www.php.net/manual/en/function.strpos.php
I'm happy to help troubleshoot your code updates if you try to use strpos and it's not working as expected, just paste your progress and we can review together.

Aditionally: Can the filter be adjusted to always show all first letters? Now at hidden link only "Alle" and "B" is shown.
Can you explain why there is a custom field Query Filter in the View here?
hidden link
It seems that you could remove this Query Filter, and the list of first letters would not be filtered using the URL parameter.

#1771039

Hello Christian,

Thank you for your comprehensive answer.

How can this be fixed to only search for the first letter?
I don't quite understand what to change in the PHP filter code.
I mostly used the code Ana provided in her answer at https://toolset.com/forums/topic/alphabetic-filter/#post-226609 and thought this code would easily work for me with some small customizations. Did I do something wrong here? If I understand correctly, the provided code worked without needing to customize the given PHP filter code.

Aditionally: Can the filter be adjusted to always show all first letters?
Yes, you are right. I did add the query filter here because Ana (same link) said that this had to be done. But maybe I misunderstood.

Kind regards
Sean

#1771379

Christian Cox
Supporter

Languages: English (English )

Timezone: America/New_York (GMT-04:00)

At a basic level, you need to modify the "LIKE" clause from using a wildcard before and after the first letter:

... mt1.meta_value LIKE '%D%'...

to using a % wildcard only after the first letter:

...mt1.meta_value LIKE 'D%'...

When you have a wildcard before the letter 'D', the search function looks for the letter anywhere in the text string. That's not what you want, you want the letter followed by a wildcard, indicating that the letter is the first letter of the search string.

#1784175

Hello Christian,

I understand that, but I don't know how (and where exactly) to modify the code there.
Aditionally I don't understand why I have to change the code. I thought the code provided would work. Could you please point out if I setup something wrong and tell me what has to be changed?

Thank you and kind regards
Sean

#1784553

Christian Cox
Supporter

Languages: English (English )

Timezone: America/New_York (GMT-04:00)

Aditionally I don't understand why I have to change the code. I thought the code provided would work..
Unfortunately no, as I was trying to explain before ( https://toolset.com/forums/topic/alphabetical-filter-for-first-letter-of-a-custom-field/#post-1769635 ), your query is different and the code there in the other post is designed for a very specific query that is not exactly like yours. The code there uses PHP string replacement to look for some specific text in the SQL query and replace it with other text. If it's not obvious why it's not working, I think you'll need a developer to take a look because this is not a simple change for me and I am not able to give you a cut-and-paste solution.

That ticket is quite old, and both our software and WordPress have changed significantly since then. 6 years is old as far as software goes, and Ana used to be hired to produce custom code solutions for clients. She has since moved on in the company and our policy has evolved. We no longer contract to produce custom solutions for clients, as per our support policy: https://toolset.com/toolset-support-policy
When you need assistance to produce a custom solution, now we recommend you contact an independent contractor who has knowledge of PHP, WordPress, and Toolset and can offer custom solutions and new feature development.

Could you please point out if I setup something wrong and tell me what has to be changed?
That would be beyond the scope of our support policy. This is custom code that is outside the scope of support we provide here in the forums. If it's beyond your understanding, it's time to get a professional, independent developer involved. I can't provide custom SQL queries here per our support policy. I can help you with Toolset APIs, I can show you examples, and I can help troubleshoot code you produce. However, producing that custom code is your responsibility. You're essentially asking me to provide a cut-and-paste new feature for you, and that is not part of the service we provide here as laid out in our support policy. If I've misunderstood your request, please elaborate. I can help troubleshoot, explain Toolset APIs, and help direct you to any examples I can find online to help you make the necessary changes to the custom code you must produce to make this work.

The topic ‘[Closed] Alphabetical filter for first letter of a custom field’ is closed to new replies.