Wordpress - wp-query to list posts by custom field from PHP/MySQL queried array

0
votes

I have a complicated question, hopefully I can explain it adequately. We currently have a wordpress site with hundreds of "Incentive" posts. Each of these "Incentive" posts contain a custom field titled, "Incentive ID". I want to display a page of "recommended incentives" based on a list of "Incentive ID's" that come from a PHP/MySQL query in a separate table. I can create a list of posts by the custom field "Incentive ID" by using wp_query in a custom page template like so

<?php 

// args
$args = array(

    'meta_key' => 'Incentive ID',
    'meta_value' => array(20,21),
    'orderby' => 'meta_value_num',
   'order' => 'ASC',
    'post_type' => 'incentives'
);

// get results
$the_query = new WP_Query( $args );

// The Loop
?>
<?php if( $the_query->have_posts() ): ?>
    <ul style="list-style:decimal;">
    <?php while ( $the_query->have_posts() ) : $the_query->the_post(); ?>
        <li>
            <a href="<?php the_permalink(); ?>"><?php the_title(); ?></a>
        </li>
    <?php endwhile; ?>
    </ul>
<?php endif; ?>

<?php wp_reset_query();  // Restore global post data stomped by the_post(). ?>

However, whenever I try to insert an array from my PHP/MySQL query into the 'meta_value" field, I get mixed results. Sometimes the page is blank, sometimes I receive an array but it overwrites the entire page. Can anyone give me some insight into how I can correctly use wp_query to populate a list of posts based on an array of custom field "Incentive ID" values? Here is all my current code

<?php
/**
 * Template Name: Recommended Incentives
 *
 * @package WordPress
 * @subpackage Twenty_Fourteen
 * @since Twenty Fourteen 1.0
 */
get_header(); ?>
<?php $con = mysql_connect("localhost","xxxxx","xxxxxx");
if (!$con) {
  die('Could not connect: ' . mysql_error());
}
mysql_select_db("lighting_incentives", $con);
$user_ID = get_current_user_id();
$result = mysql_query("SELECT FK_T_L_INCENTIVES_ID FROM WAYPOINT_USER_PICKED WHERE WP_RECOMMENDED = 1 AND FK_USER_ID = 31");

$rows = array();    
while($r = mysql_fetch_array($result)) {
    $row[0] = $r[0];
    array_push($rows,$row);
}
mysql_close($con);
?>
<div id="main-content" class="main-content"> 
    <div id="primary" class="content-area">
        <div id="content" class="site-content" role="main">
        <div class='entry-content' style='max-width:1130px; margin-left:372px; padding-right:0px;'><h3 style="text-align:center; font-size:34px; font-family:arial; font-weight:600;">Recommended Incentives</h3>
            <?php 
// args
$args = array(
    'meta_key' => 'Incentive ID',
    'meta_value' => print json_encode($rows, JSON_NUMERIC_CHECK),
    'orderby' => 'meta_value_num',
   'order' => 'ASC',
    'post_type' => 'incentives'
);
// get results
$the_query = new WP_Query( $args );
// The Loop
?>
<?php if( $the_query->have_posts() ): ?>
    <ul style="list-style:decimal;">
    <?php while ( $the_query->have_posts() ) : $the_query->the_post(); ?>
        <li>
            <a href="<?php the_permalink(); ?>"><?php the_title(); ?></a>
        </li>
    <?php endwhile; ?>
    </ul>
<?php endif; ?>

<?php wp_reset_query();  // Restore global post data stomped by the_post(). ?>

        </div> 
        </div><!-- #content -->
    </div><!-- #primary -->
</div><!-- #main-content -->
<?php
get_sidebar();
get_footer();

Any help would be much appreciated!

2
phpmysqlarrayswordpress

2 Answers

0
votes

First off when using WordPress don't use php mysql functions use $wpdb. Second you use a print json_encode that won't work. When you are trying to query by multiple meta values use the meta_query:

// Fill this with the sql result
$id_arrays = array();    
while($r = mysql_fetch_array($result)) {
  $id_arrays[] = $r[0];
}

$args = array(
  'post_type' => 'incentives',
  'orderby' => 'meta_value_num',
  'order' => 'ASC',
  'meta_query' => array(
      array(
        'key' => 'Incentive ID',
        'value' => $id_arrays, // the first ID
        'compare' => 'IN'
     ),
   )
);
$query = new WP_Query( $args );

Not sure if the orderby will work this way but it should. Also you see I'm hardcoding the first 2 entries. To make it dynamic loop over it before adding them to the array.

For more information see:

0
votes

For building you $rows array, you can just do it like this 

while($r = mysql_fetch_array($result)) {
    $row[] = $r[0];
}

You can use meta_query for getting specific posts that have specific meta value related to them. Example :

$meta_query = array(
    array(
        'key' => 'Incentive ID',
        'value' => $row,
        'compare' => 'IN'
    )
);

$args = array( 
    'post_type' => "incentives",
    'post_status' => 'publish', 
    'meta_query' => $meta_query,
    'orderby' => 'meta_value_num', 
    'order' => 'ASC' 
    );

// get results
$the_query = new WP_Query( $args );