Reply To: Font-end search form for a subfield

• LucasG

• July 23, 2015 at 12:47 am

Hello @hube2

Many thanks, I’ve tried this.
I can see how close I am to the solution, but it’s just out of reach with the knowledges I have.
It’s the first time I use the $wpdb method and I’m not sure what i am doing wrong (again !).

So I dynamically make the SQL request to have something like this in a var:

SELECT *
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
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)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id)
WHERE 1=1
  AND (((mt3.meta_key = 'tous_lots_0_surface_du_lot'
         AND CAST(mt3.meta_value AS SIGNED) BETWEEN '110.5' AND '155.25')
        AND (mt4.meta_key = 'tous_lots_0_etat_de_livraison'
             AND CAST(mt4.meta_value AS CHAR) LIKE '%"1-loc"%'))
       OR ((mt3.meta_key = 'tous_lots_1_surface_du_lot'
            AND CAST(mt3.meta_value AS SIGNED) BETWEEN '110.5' AND '155.25')
           AND (mt4.meta_key = 'tous_lots_1_etat_de_livraison'
                AND CAST(mt4.meta_value AS CHAR) LIKE '%"1-loc"%'))
       OR ((mt3.meta_key = 'tous_lots_2_surface_du_lot'
            AND CAST(mt3.meta_value AS SIGNED) BETWEEN '110.5' AND '155.25')
           AND (mt4.meta_key = 'tous_lots_2_etat_de_livraison'
                AND CAST(mt4.meta_value AS CHAR) LIKE '%"1-loc"%'))
       OR (etc...)
  AND (wp_term_relationships.term_taxonomy_id IN (10))
  AND (wp_postmeta.meta_key = 'dept_nb_for_the_order'
       AND mt1.meta_key = 'city_name_for_the_order'
       AND mt2.meta_key = 'arrondissement')
  AND post_status = 'publish'
  AND wp_posts.post_type = 'post'
GROUP BY wp_posts.ID
ORDER BY CAST(wp_postmeta.meta_value AS CHAR) ASC, CAST(mt1.meta_value AS CHAR) ASC, CAST(mt2.meta_value AS CHAR) ASC LIMIT 0,
                                                                                                                                10

Then I send the query and loop through the results.
It almost works !!!

I think I just have a problem with LIKE ‘%”1-loc”%’
Even if this string isn’t in the db, the result shows up.

Thanks a lot for your help, I almost reach the end 🙂