Parsing External Table Arguments

Question

I have a client project that has posts assigned to their country of origin.

The client wants to be able to search the posts by continent and or region.

I have a separate table that assigns each country to its respective region, and I have the WP query that uses the resulting array:

$country_names = array('England','France','Germany',...); // this would be the result from fetching countries associated with a region.
$args = array(
    'posts_per_page' => -1,
    'meta_query' => array(
        array(
            'key' => 'country',
            'value' => $country_names,
            'compare' => 'IN'
        )
    )
);
$query = new WP_Query( $args );

What I'm having trouble with is the part in the middle. Normally, I'd use some standard PHP/MySQL to craft the array:

<?php

//Process incoming variable
if(!empty($_REQUEST['region'])){
 $region = $_REQUEST['region'];
 } else {
 $region = NULL;
 }

// Make a MySQL Connection
$query = "SELECT * FROM regions WHERE region='$region'"; 

$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['country'];
?>

However, I'm having trouble making it work within a WP template, since WP manages the incoming variables using its own internal functions.

Can anyone help me to connect the two together? I'm sure I'm overlooking some simple step here, or else I'm not using some built-in WP function.

Any help is appreciated.

Thanks!

ty

Answer 1

Why don't you just use a custom hierarchical taxonomy where countries are children of parent region terms? Seems more suitable and natural to me.

Answer 2

I created a new template and started from scratch with it. I still can't get the new WP query to return any posts, but it did solve this initial problem.

First I got my results:

if(!empty($_REQUEST['region'])){
     $region = $_REQUEST['region'];
 } else {
     $region = NULL;
 }
if (empty($region)) {
    echo "No region selected";
}else{
    $regionresult = mysql_query("SELECT * FROM gallery_regions WHERE region='$region'") or die(mysql_error());
    $num_rows = mysql_num_rows($regionresult);
    if (!$regionresult) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}
 $country_search_array = array();
    while($row = mysql_fetch_array($regionresult)){
        $country_search_array[] = $row['country']; // Your country field name
    }
    $country_search = "'".implode("','", $country_search_array)."'";?>

Then I created the query:

<? $args = array(
        'posts_per_page' => -1,
        'meta_query' => array(
            array(
                'key' => 'Country',
                'value' => $country_search,
                'compare' => 'IN'
            )
        )
    );
    $query = new WP_Query( $args );?>

The results can be viewed here: http://www.tylonius.com/gallery-test.html