bind param count first argument

Question

I have the following line:

$formatsArray = $_POST['formats'];
$topicsArray = $_POST['topics'];


// Converting the array into individual strings
$formats = implode(",", $formatsArray);
$topics = implode(",", $topicsArray);


  // Prepare the statement
    $resources = $con->prepare("SELECT * FROM resources WHERE
            (format IN (?))
             AND (topic IN (?))");

    // Bind the statement
    $resources->bind_param('ss',$formats, $topics);

The problem is that topics derived from an array where it could contain multiple string, but 's' will only recognize 1. I would want that if the topic array has 10 entries, than there would be 10s, and same for format.

I have thinking of counting the size of the array and adding an s in every iteration, but not sure how. Any help would be appreciated.

   // Count array
    $formatCount = count($formatsArray);
    $topicCount = count($topicsArray);

not opssible. placeholders represent **SINGLE** values. a csv string `a,b,c` stuffed into a placeholder is not going to be three separate things for `in` to consider, it'll be `... IN('a,b,c')` - a single value. you have to build a query string dynamically and put in as many `?` as you have values, or use something else entirely. — Marc B, Sep 03 '15 at 21:27
You bind individual values to each placeholder `?`.... so an array of values won't bind to a single placeholder — Mark Baker, Sep 03 '15 at 21:27
possible duplicate of [convert an array into individual strings](http://stackoverflow.com/questions/32302633/convert-an-array-into-individual-strings) — chris85, Sep 03 '15 at 21:30
the array has been converted to a string so where instance it would return idea generation,customer development the problem is that i want to evaluate "idea generation" and "customer development" for instance seperatetly — code_legend, Sep 03 '15 at 21:30
It's the exact same concept. You need to build the query dynamically. You never even resolved any of the other threads... — chris85, Sep 03 '15 at 21:31
i am trying to achieve that, and those previous questions have been invaluable in arriving at this step, but from here. since i bind individual values to each placeholder, wouldn't a counter work? You could how big the array is if its 5 then you place 5s — code_legend, Sep 03 '15 at 21:44
possible duplicate of [MySQLi Bind Param with an array for IN](http://stackoverflow.com/questions/6053239/mysqli-bind-param-with-an-array-for-in) — ooXei1sh, Sep 04 '15 at 01:37

ooXei1sh · Answer 1 · 2015-09-04T01:25:10.480

How about this then:

<?php  
    $con = new mysqli("localhost", "USERNAME", "PASSWORD", "DATABASE");

    $formatsArray = array('a','b','c','d',);
    $topicsArray = array('x','y','z',);

    $sql = 'SELECT * FROM resources WHERE (format IN (FORMAT_REPLACE_ME)) AND (topic IN (TOPIC_REPLACE_ME))';

    $formatsPlaceholders = makePlaceHolders($formatsArray);
    $topicsPlaceholders = makePlaceHolders($topicsArray);

    $sql = str_replace('FORMAT_REPLACE_ME', $formatsPlaceholders, $sql);
    $sql = str_replace('TOPIC_REPLACE_ME', $topicsPlaceholders, $sql);

    //error_log(print_r($sql,1).' '.__FILE__.' '.__LINE__,0);

    try {
        $s = $con->prepare($sql);

        $vals = array_merge($formatsArray, $topicsArray);

        // from http://stackoverflow.com/a/31562035/1814739
        $typDfs = str_repeat( 's' , count( $vals ) );
        $params = array( $typDfs );
        foreach ( $vals as $k => $v ) {
            ${ 'varvar' . $k } = $v;
            $params[] = &${ 'varvar' . $k }; # provide references
        }
        call_user_func_array( array( $s, 'bind_param' ) , $params );

        $s->execute();

        $output = array();
        $res = $s->get_result();
        while ($row = $res->fetch_array(MYSQLI_NUM))
        {
            //error_log(print_r($row,1).' '.__FILE__.' '.__LINE__,0);
            $output []= array(
                'id' => $row[0],
                'format' => $row[1],
                'topic' => $row[2],
            );
        }

        $s->close();

        sanitize_output($output);
    }
    catch (\Exception $e) {
        error_log(print_r($e->getMessage(),1).' '.__FILE__.' '.__LINE__,0);
    }

    function makePlaceHolders($arr){
        $ph = '';
        for ($i = 1; $i <= count($arr); $i++) {
            $ph .= '?,';
        }
        return rtrim($ph,',');
    }

    function sanitize_output(array &$arr, array $args=array()) {
        array_walk_recursive($arr,'so',$args);
    }
    function so(&$v,$k,$args) {
        $excludes = isset($args['excludes']) ? $args['excludes'] : array();
        if (!in_array($k,$excludes)) {
            $v = trim($v);
            $v = (get_magic_quotes_gpc()) ? stripcslashes($v) : $v;
            $v = htmlspecialchars($v);
        }
    } 
?>
<html>
<body>
    <ul>
        <?php foreach($output as $k => $o) { ?>
        <li><?php echo $o['id']; echo $o['format']; echo $o['topic']; ?></li>
        <?php } ?>
    </ul>
</body>
</html>

thanks for the response. I am using mysqli as oppose to pdo – code_legend Sep 03 '15 at 22:59 — code_legend, Sep 03 '15 at 22:59

bind param count first argument

1 Answers1