passing an comma delineated list to a custom wordpress query

Question

Ok, so here's what is going on...

I am pulling a list of the most popular post titles from Google Analytics and I'm placing them in an array

$thisnumber = 0;

$start = date('Y-m-d', (time() - (60 * 60 * 24 * 30)));
$end = date('Y-m-d');
$limit = $getnumber;
$titles = array();

$login = new GADWidgetData();
$ga = new GALib($login->auth_token, $login->account_id, 60);
$pages = $ga->pages_for_date_period($start, $end);

foreach($pages as $page) :
    $title = $page['children']['value'];
    $titlearray = explode(' |', $title, -1);
    $titlesub = implode( "", $titlearray);
    $thetitles[] = $titlesub;
    $thisnumber++;
    if($thisnumber > ($getnumber*2)) break;
endforeach;

$titles_list = implode( ",", $thetitles);

So after all of this I am left with both an array of titles in $thetitles and a list of the titles stored in $titles_list

now I need to send all of these titles to a full wordpress query - so I have a function:

function get_post_by_title($page_title, $output = OBJECT) {
global $wpdb;
    $post = $wpdb->get_var( $wpdb->prepare( "
    SELECT wposts.* 
    FROM $wpdb->posts wposts
    WHERE post_title = %s 
    AND post_type='post'
    AND post_status = 'publish'", $page_title ));

    if ( $post )
        return get_post($post, $output);

return null;

}

Unfortunately, this can be a bunch of queries, and on a heavily traveled site this could bog things down. I attempted to use the SQL IN function, but I think I'm doing it wrong

$querystr = "
SELECT wposts.* 
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE post_title IN ($titles_list) 
AND wposts.post_status = 'publish' 
AND wposts.post_type = 'post'";

$pageposts = $wpdb->get_results($querystr, OBJECT);

Can anyone with better SQL/Wordpress foo give me a hand?

Answer 1

Parameter injection can't be for the IN comma delimited list when using SQL - it would only work for one of the values in that list. You need to create the SQL statement as a string outside of the prepared statement syntax, including the $titles_list concatenation.

Answer 2

Alright, I wanted to come back and give everyone my solution...

$titles_list = implode( "', '", $thetitles);

$order_by_results = build_order($thetitles);

$querystr = "
    SELECT wposts.* 
    FROM $wpdb->posts wposts
    WHERE post_title IN ('" . $titles_list . "') 
    AND wposts.post_status = 'publish' 
    AND wposts.post_type = 'post'
    ORDER BY CASE post_title " . $order_by_results ."
    LIMIT ".$getnumber;

The build_order() function takes the $thetitles from my original list and formats them with a

WHEN   'n[1]'   THEN   1
WHEN   'n[2]'   THEN   2
ELSE     3      END

Pattern, That function is here:

function build_order($titlelist) {
$order_counter = 1;
$row = array();
foreach($titlelist as $title) :
    $row[] = "WHEN '" . $title . "' THEN " . $order_counter;
    $order_counter++;
endforeach;
    $row[] = "ELSE " . $order_counter . " END";

$title_order = implode( " ", $row);

if (title_order)
    return $title_order;
}

Hopefully this will help someone else in in the future