Searching By Custom Field Arrays

To put it another way, I’m looking for a SQL query to filter posts depending on whether the array stored in a particular custom field contains a given value.

a|x

Haven’t tried this but you might play with something like:

$query = "SELECT * FROM $wpdb->postmeta WHERE meta_key = 'listing_categories' AND 'Cinemas' IN (" . implode(',', meta_value) . ")";

Related:
wpdb
https://www.php.net/manual/en/function.implode.php
https://dev.mysql.com/doc/refman/4.1/en/comparison-operators.html#function_in

Note: if you continue this thread please present the code that is being used to write that data into the postmeta table.

Hi MichaelH,

thanks for getting back to me.
I’ll give that a go.

The code writing the data into the postmeta table was generated by a custom write panel created with the Magic Fields plugin. Looking at the source, it’s just a standard HTML form checkbox group:

<div class="mf_custom_field">
	<label for="11_1_1_2_rc_cwp_meta_listing_categories" class="selectit mf-checkbox-list">
		<input tabindex="3"  class="checkbox_list_mf" id="Bars and Clubs" name="11_1_1_2_rc_cwp_meta_listing_categories[]" value="Bars and Clubs" type="checkbox"  />
		Bars and Clubs </label>
	<br />
	<label for="11_1_1_2_rc_cwp_meta_listing_categories" class="selectit mf-checkbox-list">
		<input tabindex="3"  class="checkbox_list_mf" id="Cinemas" name="11_1_1_2_rc_cwp_meta_listing_categories[]" value="Cinemas" type="checkbox"  />
		Cinemas </label>
	<br />
	<label for="11_1_1_2_rc_cwp_meta_listing_categories" class="selectit mf-checkbox-list">
		<input tabindex="3"  class="checkbox_list_mf" id="Galleries and Museums" name="11_1_1_2_rc_cwp_meta_listing_categories[]" value="Galleries and Museums" type="checkbox"  />
		Galleries and Museums </label>
	<br />
	<label for="11_1_1_2_rc_cwp_meta_listing_categories" class="selectit mf-checkbox-list">
		<input tabindex="3"  class="checkbox_list_mf" id="Music Venues" name="11_1_1_2_rc_cwp_meta_listing_categories[]" value="Music Venues" type="checkbox"  />
		Music Venues </label>
	<br />
	<label for="11_1_1_2_rc_cwp_meta_listing_categories" class="selectit mf-checkbox-list">
		<input tabindex="3"  class="checkbox_list_mf" id="Restaurants and Cafes" name="11_1_1_2_rc_cwp_meta_listing_categories[]" value="Restaurants and Cafes" type="checkbox"  />
		Restaurants and Cafes </label>
	<br />
	<label for="11_1_1_2_rc_cwp_meta_listing_categories" class="selectit mf-checkbox-list">
		<input tabindex="3"  class="checkbox_list_mf" id="Shops" name="11_1_1_2_rc_cwp_meta_listing_categories[]" value="Shops" type="checkbox"  />
		Shops </label>
	<br />
	<label for="11_1_1_2_rc_cwp_meta_listing_categories" class="selectit mf-checkbox-list">
		<input tabindex="3"  class="checkbox_list_mf" id="Theatres" name="11_1_1_2_rc_cwp_meta_listing_categories[]" value="Theatres" type="checkbox"  />
		Theatres </label>
	<br />
</div>

Well that code doesn’t really show the code that puts the array into the database, but you referenced Magic Fields so that will give someone a hint if they see this thread later.

Ah, yes, you’re absolutely right of course. Sorry for my very naive reply there.

I’m not sure how or if the Magic Fields plugin catches post data sent from one of its custom panels.

a|x

Hi again,

just came back to this after the Easter break.
Looks like the data written to the database looks something like this:

a:2:{i:0;s:14:"Bars and Clubs";i:1;s:21:"Restaurants and Cafes";}

I guess using implode() as in your code won’t work on this string, as it’s not comma-seperated. Would I use regular-expressions to search in the string for a particular sub-string? Any tips on how I might do that?

Thanks again,

a|x

That’s serialized data which is not really easy to work with in a SQL statement but you could use LIKE :

$query = “SELECT * FROM $wpdb->postmeta WHERE meta_key = ‘listing_categories’ AND meta_value LIKE ‘%Cinemas%'”;

There’s also RLIKE…

Ah, I see. That seems to make sense, but when I try it in my template, only the header, and items above ‘The Loop’ in the main content section of the page is rendered (ie the pages chokes when it tries to run the loop). The SQL query part itself seems to run OK, but it seems to get stuck after that.

I’ll do some more experiments to see if I can track down more specifically what might be causing the problem.

Incidentally, do I have to run setup_postdata(); for each post inside the loop?

a|x

<?php
$query = your select statement here
$results = $wpdb->get_results($query);
if ($results) {
  foreach($results as $post) {
   setup_postdata($post); ?>
     <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
    <?php
  } // foreach($posts
} // if ($results
?>

See wpdb

Ah, thanks for that MichaelH.

I think we might be talking at cross-purposes though.

The query you mentioned above only seems to return the actual value of the key ‘listing_categories’ for the selected post(s). Or at least, that’s what it does when I run in using phpMyAdmin on the post_meta table of the WP database.

I know $wpdb provides an abstraction layer between MySQL and the actual database, which might make the query run differently in the context of a WordPress template, but I’m definitely not getting the selected post(s) title appearing in The Loop, with your code above. I’m assuming this is because the query isn’t actually returning the post data.

I guess to make this work, I’d have to fetch an array of the IDs of selected posts, then run a second query on the posts database, and use the title from there, and the custom fields data from the first query. Or is this not necessary?

I think what I’ll do, since I’m short on time, is just redesign the form, so that each category option is a discrete checkbox, rather than a group, so I can just check for TRUE for a given meta_key (‘listing_categories_cinemas’ for example). It seems a fiddlier way of doing it (and the form looks terrible), but maybe it will be easier in the long-run.

Thanks again,

a|x

I am having a similar issue. I am using a checkbox list, but I am having difficulties displaying posts based on a custom field value, due to the way they are stored by magic fields.

Did this ever get resolved? I am curious to know.

Thank you!

Nadine