如果存在混合类型数组，如何为IN子句准备语句? [英] How to prepared statement for IN clause if there is mixed type array?

问题描述

我正在尝试为数组混合数组的IN子句编写准备好的语句.我能够为'call_user_func_array()'构建数组，但是我无法准备语句.没有输出显示.

I am trying to write prepared statement for IN clause where array is mixed array. i am able to build array for 'call_user_func_array()' but i am not able to prepare statement. no output is showing.

这是我的php代码:

    $search1 = array('pune','india','2014','mumbai','2015'); 

    print_r($search1);

    echo $param = implode(",",$search1);

    $ids = array_flip(array_flip(explode(',', $param)));

    var_dump($ids);

    $type1 = array();

    foreach ($ids as $element) {

    if(is_string($element)) {
        $type1[] ='s';
    }elseif(is_int($element)) {
        $type1[] ='i';
        }
    }

    print_r($type1);

    echo $type=implode("",$type1);

    $inputArray[] = &$type;
    $j = count($ids);
    for($i=0;$i<$j;$i++){
    $inputArray[] = &$ids[$i];
    }

    print_r($inputArray);

    echo $clause = implode(',', array_fill(0, count($ids), '?'));

    $construct .="(city in ('.$clause.') or state in ('.$clause.')
          or country in ('.$clause.')) AND year in ('.$clause.') order by year desc";

    $constructs ="SELECT * FROM info WHERE $construct";

    if($stmt1 = mysqli_prepare($conn, $constructs)){

    call_user_func_array(array($stmt1,'bind_param'),$inputArray);

    if(mysqli_stmt_execute($stmt1)){    

            $result = mysqli_stmt_get_result($stmt1);
            if(mysqli_num_rows($result) > 0){
            echo $foundnum = mysqli_num_rows($result);
     while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){

                echo $id = $row['id'];
                echo $name = $row['name'];

                }
            }
        }
    }
    mysqli_stmt_close($stmt1);

请告诉我我在这里做错了.

please tell me what i am doing wrong here.

推荐答案

100％经过测试且成功的代码:

$search1 = ['2014','pune','india','2014','mumbai','2015','mumbai'];

// Separate logically and remove duplicates
foreach ($search1 as $value) {
    if (strlen($value)==4 && ctype_digit($value)) {  // qualifies for the year column
        $years[$value] = null;
    } else {
        $strings[$value] = null;
    }
}

$years = array_keys($years);  // move keys to values
$years_count = sizeof($years);

$strings = array_keys($strings);  // move keys to values
$strings_count = sizeof($strings);

if (!$years_count || !$strings_count) {  // this is a matter of program logic
    echo "A minimum of one year value and one non-year value is required for search functionality.";
}elseif (!$conn = new mysqli("host", "user","pass","db")) {
    echo "Database Connection Error: " , $conn->connect_error;  // don't show to the public
} else {
    $years_csph = implode(',', array_fill(0, $years_count, '?'));  // comma-separated placeholders
    $strings_csph = implode(',', array_fill(0, $strings_count, '?'));  // comma-separated placeholders

    $total_count = $strings_count * 3 + $years_count;
    $total_params = array_merge($strings, $strings, $strings, $years);
    $param_string = str_repeat('s', $strings_count * 3) . str_repeat('i', $years_count);  // write s chars before i chars

    if(!$stmt = $conn->prepare("SELECT id, name FROM info WHERE (city IN ($strings_csph) OR state IN ($strings_csph) OR country IN ($strings_csph)) AND year IN ($years_csph) ORDER BY year DESC")) {
        echo "Syntax Error @ prepare: " , $conn->error;  // don't show to public
    }else{
        array_unshift($total_params, $param_string);  // prepend the type values string
        $ref = [];  // add references
        foreach ($total_params as $i => $v) {
            $ref[$i] = &$total_params[$i];  // pass by reference as required/advised by the manual
        }
        call_user_func_array([$stmt, 'bind_param'], $ref);    

        if (!$stmt->execute()) {
            echo "Error @ bind_param/execute: " , $stmt->error;  // don't show to public
        } elseif (!$stmt->bind_result($id, $name)) {
            echo "Error @ bind_result: " , $stmt->error;  // don't show to public
        } else {
            while ($stmt->fetch()) {
                echo "<div>$id : $name</div>"; 
            }
            $stmt->close();
        }
    }
}

在其他潜在问题中，'.$clause.'看起来不太好，因为它在占位符周围写了单引号和点.占位符永远不需要单引号，即使语法确实不正确，也是如此.

Among other potential problems, '.$clause.' didn't look good because it was writing single quotes and dots around your placeholders. Placeholder never need single quoting and that syntax would have been incorrect even if they did.

这篇关于如果存在混合类型数组，如何为IN子句准备语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！