Is there any way to use wpdb prepare statements fo

问题:

I'm trying to properly prepare my data for $wpdb

$region = $wpdb->get_results( $wpdb->prepare( "
      SELECT tr.*, count(*) AS jobs_count FROM {$wpdb->terms} tr
      INNER JOIN {$wpdb->term_taxonomy} tm
        ON ( tm.term_id = tr.term_id )
      INNER JOIN {$wpdb->term_relationships} tmr
        ON ( tmr.term_taxonomy_id = tm.term_taxonomy_id )
      INNER JOIN {$wpdb->posts} p
        ON ( p.ID = tmr.object_id )
      WHERE (tm.parent = '%d'
        AND tm.taxonomy = '%s'
        AND p.post_type = '%s' )
      GROUP BY name HAVING COUNT(name) > '%d'
  ", 0, 'location', 'job', 0 ));

I tried this for get the region name. It's exactly return the data as expected. But here region is the parent taxonomy and the country is it's child item. So I also wanted to prepare the data for getting the country list also. But here the problem is it's looks for under the parent element if it exists. So I make a dynamic array for that named it $sql. The code I want to prepare is given below as $country_query.

In WHERE statement I use php implode method for dynamic query building. It's works for me now. But I also want to prepare the data as like region.

foreach ($region as $reg) {
      $sql[] = " $wpdb->term_taxonomy.parent = '$reg->term_id' ";
}
    $country_query = "SELECT $wpdb->terms.*, count(*) AS jobs_count FROM $wpdb->terms
                      INNER JOIN $wpdb->term_taxonomy
                        ON ( $wpdb->term_taxonomy.term_id = $wpdb->terms.term_id )
                      INNER JOIN $wpdb->term_relationships
                        ON ( $wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id )
                      INNER JOIN $wpdb->posts
                        ON ( $wpdb->posts.ID = $wpdb->term_relationships.object_id )

                      WHERE (". implode(' OR ', $sql) ." AND $wpdb->term_taxonomy.taxonomy = 'location' ) AND $wpdb->posts.post_type = 'job'
                      GROUP BY name HAVING COUNT(name) > 0";

    $country = $wpdb->get_results($country_query);

Currently my query throw this SQL statement after using implode(' OR ', $sql) this. It'll be helpful if anyone knows how to do this please please let me know.

SELECT wp_terms.*, count(*) AS jobs_count FROM wp_terms
                  INNER JOIN wp_term_taxonomy
                    ON ( wp_term_taxonomy.term_id = wp_terms.term_id )
                  INNER JOIN wp_term_relationships
                    ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id )
                  INNER JOIN wp_posts
                    ON ( wp_posts.ID = wp_term_relationships.object_id )

                  WHERE ( wp_term_taxonomy.parent = '2'  OR  wp_term_taxonomy.parent = '322'  OR  wp_term_taxonomy.parent = '651'  AND wp_term_taxonomy.taxonomy = 'location' ) AND wp_posts.post_type = 'job'
                  GROUP BY name HAVING COUNT(name) > 0

I'm also try my best to find an answer. if I can find any answer I'll also share my answer. But the thing is I've no idea how to prepare implode(' OR ', $sql). If it's not possible and anyone knows then I've to ignore this or try an another approach.

Thanks in advance...

回答1:

Thanks to https://stackoverflow.com/users/1704961/mdma for the heads up. He says to me try IN. So finally I found an answer from stackoverflow: https://stackoverflow.com/a/10634225/1993427 It helps me a lot. :)

So my final code is given below for others.

$country = array();

  $sql = array();
  foreach ($region as $reg) {
    $sql[] = $reg->term_id;
  }

$test = array("location", "job", "0");


$sql_st = "SELECT tr.*, count(*) AS jobs_count FROM {$wpdb->terms} tr
                  INNER JOIN {$wpdb->term_taxonomy} tm
                    ON ( tm.term_id = tr.term_id )
                  INNER JOIN {$wpdb->term_relationships} trm
                    ON ( trm.term_taxonomy_id = tm.term_taxonomy_id )
                  INNER JOIN {$wpdb->posts} p
                    ON ( p.ID = trm.object_id )

                  WHERE tm.parent IN(".implode(', ', array_fill(0, count($sql), '%s')).") AND tm.taxonomy = '%s' AND p.post_type = '%s'
                  GROUP BY name HAVING COUNT(name) > '%s'";

$country_query = call_user_func_array(array($wpdb, 'prepare'), array_merge(array($sql_st), $sql, $test));

$country = $wpdb->get_results($country_query);

Cheers :)