passing variable with multiple values seperated by comma to sql statement through PHP

2 Views

I’ve looked all over the interwebs, and cannot find that simple answer I’m looking for – possibly because it doesn’t exist, but.. possibly because I don’t know the correct terms to search for.

ok, so, i’ve got a variable – it’s actaully a key value pair in an array that i’m passing into my function. the key is args[comments_mentioned] and the value is dynamically generated for me – it’s ALWAYS going to be number, separated by commas (i.e. 1,2,3,4,5)

Read More

so, just to be super clear: 

         $args[comments_mentioned] == "1,2,3,4"; //could be any amount of number, not just 1,2,3,4,5

i’d like to pass this into a sql statement as a variable to use in an “IN” clause, like so: 

         $sr_sql = <<<SQL
         SELECT *
         FROM $wpdb->commentmeta
         WHERE meta_value = %s
         AND comment_ID in ($args[comments_mentioned])
         ORDER BY meta_id DESC
         SQL;

Then, Prepare it using the wordpress prepare and get results 

        $sr_query = $wpdb->prepare( $sr_sql, $args[user_id]) );
        //receive the correct sql statement, and plug 'er in.
        $sr_comment_rows = $wpdb->get_results($sr_query);

and run my foreach loop: 

        foreach ($sr_comment_rows as $sr_comment) {
            $sResults .= 'do something with $sr_comment';
        }

now, i realize the code above won’t work – i can’t just pass the variable in there like that. BUT, i can’t pass it as a string (%s), because it wraps it in ‘1,2,3,45’, and so it looks for the entire string, and not each number. I can’t pass it as an int (%d), because of the commas…

In other posts, they mentioned create a temp table or variable, but, i’m not sure if that’s the correct way to do it in mysql, or how to reference it once I do.

so, how do I do this? preference for actual code that works 😉

Thank you for reading and helping out!

Post Views: 2

Related posts

Leave a Reply

2 comments

  1. One option, if you cannot normalize your data, is to enclose your string in commas such that it be ",1,2,3,4," and then you could do:

    AND LOCATE( CONCAT(',',comment_ID,',') , ($args[comments_mentioned]) )

    which will match if it finds a eg. ',3,' in ',1,2,3,4,' (Using 3 as an example)

  2. I believe this should be enough:

    $params = $args[comments_mentioned];
$table = $wpdb->commentmeta;
$sr_sql = "
         SELECT *
         FROM $table
         WHERE meta_value = %s
         AND comment_ID in ($params)
         ORDER BY meta_id DESC
         ";

    It will be result something like:

    SELECT *
FROM table_on_variable
WHERE meta_value = %s
         AND comment_ID in (1,2,3,4)
         ORDER BY meta_id DESC

    If your mainly issue is regarding the in clause, so you will not have problems if you use double quotes and single variable as illustrated above.