CODEWITHSUNDEEP
mysqlwordpresswoocommerce
CChoma
CChoma

Reputation: 1154

Wordpress Custom Search

This is maddening. I can't seem to figure out what seems like an easy thing to do. I am building a custom search function for a Wordpress site that is selling used RVs. I am using Woocommerce to catalog the products (probably not important since it just creates a product with a post_type=product instead of post_type=post).

I am using custom fields to define options such as the vehicle's 'Class' (e.g. 'Class A', 'Class B', 'Class C', etc.) and 'Fuel Type' (e.g. 'Gasoline', 'Diesel', 'Towable'). So each product may have a different meta_value for the same meta_key, but may only have each meta_key defined only once and may only have one meta_value per meta_key.

Part of the search form looks something like this. (There is more but this all we need to worry about at the moment)

<h4>Type / Class</h4>

<label><input type="checkbox" name="a1" value="0" />All Types</label>
<label><input type="checkbox" name="a2" value="Class A" />"Class A" RV's</label>
<label><input type="checkbox" name="a3" value="Class B" />"Class B" RV's</label>
<label><input type="checkbox" name="a4" value="Class C" />"Class C" RV's</label>
<label><input type="checkbox" name="a5" value="Fifth Wheel" />Fifth Wheel RV's</label>
<label><input type="checkbox" name="a6" value="Toy Hauler" />Toy Haulers</label>
<label><input type="checkbox" name="a7" value="Travel Trailer" />Travel Trailer RV's</label>
<label><input type="checkbox" name="a8" value="Camping Trailer" />Camping Trailers</label>

<h4>Engine Type</h4>

<label><input type="checkbox" name="c1" value="0" />All Engine Types</label>
<label><input type="checkbox" name="c2" value="Gasoline" />Gasoline</label>
<label><input type="checkbox" name="c3" value="Diesel" />Diesel</label>
<label><input type="checkbox" name="c4" value="Towable" />Towable</label>

Which is sent to run the following query and return the results.

$result = mysql_query("
            SELECT * FROM wp_postmeta 
            WHERE 
            (meta_key= 'class' AND (meta_value = '$a2' OR meta_value = '$a3' OR meta_value = '$a4' OR meta_value = '$a5' OR meta_value = '$a6' OR meta_value = '$a7' OR meta_value = '$a8'))
            ");

What this does is if a box is selected, passes a true parameter (1) for the option in the url for the selected option(s) (ex. site.com/search/?a2=1&a4=1 if 'Class A' and 'Class C' are selected). This seems to work fine.

However, when I try to search for the combination of another meta_key and it's values using this new query...

$result = mysql_query("
            SELECT * FROM wp_postmeta 
            WHERE 
            (meta_key= 'class' AND (meta_value = '$a2' OR meta_value = '$a3' OR meta_value = '$a4' OR meta_value = '$a5' OR meta_value = '$a6' OR meta_value = '$a7' OR meta_value = '$a8'))
            AND
            (meta_key= 'fuel' AND (meta_value = '$c2' OR meta_value = '$c3' OR meta_value = '$c4'))
            ");

... it does not work anymore :( Each meta_key statement will work independently, but not together using the AND operator. It seems right to me but it's not working. Strangely though, if I change the AND to OR at line 5 it works, but does not return the results I want as it expands the search instead of narrows it.

Any help is greatly appreciated. I am also willing to try other methods if anyone knows how to pull this off as I could be going about this all wrong. I'm a designer first recently turned programmer and my level of expertise is intermediate at best so please be kind.

Upvotes: 0

Views: 389

Answers (1)

Jeremy Johnson
Jeremy Johnson

Reputation: 196

This is an error with how your writing your SQL query. Each record in wp_postmeta can only have one meta_key value, but your trying to match multiple meta_key values, which is why your seeing no results returned, because no records match your query.

In order to return the data your looking for you will need to join tables for each unique meta_key condition you want to lookup.

Something like this should be what your looking for.

SELECT distinct(m1.post_id) as post_id FROM 
    wp_postmeta m1, 
    wp_postmeta m2
WHERE
    m1.post_id=m2.post_id AND
    (m1.meta_key='class' AND (m1.meta_value IN ('$a2','$a3','$a4','$a5','$a6','$a7','$a8'))) AND
    (m2.meta_key='fuel' AND (m2.meta_value IN ('$c2','$c3','$c4')))

You will need to build out the SQL query based on what search options are set.

Upvotes: 3

Related Questions