Search More than 1 value inside 1 input text of HTML search form

Question

I have an usual search form using HTML that will be extract the data from mysql (the connection is using PHP and mysqli). The function works well in searching 1 value, but I want to make user can search more than 1 value that will be separated by comma (,).

For example: Searching 1 value works well.

But I want to search 2 or more values inside the search box, like: 42-7278954,53-1217544,07-2517487,...

I hope user can input something like in the pic below and the result will have 2 rows --> CN_no 42-7278954 and 53-1217544:

The query I have so far is:

$sql = "SELECT * FROM mock_data WHERE CN_no IN ('{$CN_no}') OR doc_no IN ('{$doc_no}')";

Notes: CN_no is "Shipment Code" and doc_no is "Reference No"

But well... it's obviously give me an error because of the incorrect syntax.

Please help me to revise it. Thank you.

======== update query based on vp_arth's answer ========

$cn = explode(',', $CN_no);
$incn = str_repeat('?, ', count($cn)-1).'?';
$doc = explode(',', $doc_no);
$indoc = str_repeat('?, ', count($doc)-1).'?';

$query = "SELECT * FROM mock_data WHERE CN_no IN ({$incn}) or doc_no IN ({$indoc})";
$result = $conn->query($query , array_merge($incn, $indoc));

But it give me an error

Answer 1

This is a possible solution to use prepared statements with dynamic input in mysqli. The typing for the parameter binding is static though, in this case the parameters are strings.

/**
 * connecting to the database
 * defining in how manye columns you want to search (important to create the correct amount of arguments)
 */
$conn = new mysqli('localhost', 'root', '', 'test');
$columnsToSearch = 2;

/**
 * the numbers you want to search delimited by ","
 */
$CN_no = '42-7278954,53-1217544,07-2517487';
$cn = explode(',', $CN_no);

/**
 * writing the numbers to search into variables
 * putting the references of those variables into an array
 * 
 * the references will be used as arguments for the prepared statement
 */
$values = array();
for ($i = 0; $i < $columnsToSearch; $i++) {
    foreach ($cn as $k => $value) {
        $temp{$k}{$i} = $value;
        $values[] = &$temp{$k}{$i};
    }
}
/**
 * putting together the "types"-part for the binding of the prepared statement
 */
$types = array(str_repeat('s', count($cn) * $columnsToSearch - 1) . 's');
/**
 * merging types and references
 */
$argumentsArray = array_merge($types, $values);
/**
 * creating placeholder string for the query
 */
$placeholder = str_repeat('?, ', count($cn) - 1) . '?';

$stmt = $conn->prepare('SELECT CN_no, doc_no FROM mock_data WHERE CN_no IN (' . $placeholder . ') or doc_no IN (' . $placeholder . ')');

/**
 * check http://us3.php.net/manual/en/mysqli-stmt.bind-param.php#104073 to read what is happening here
 */
$ref = new ReflectionClass('mysqli_stmt');
$method = $ref->getMethod("bind_param");
$method->invokeArgs($stmt, $argumentsArray); 

$stmt->execute();

/**
 * fetching the result
 */
$stmt->bind_result($CN_no, $doc_no);
$row_set = array();
while ($row = $stmt->fetch()) {
    $row_set[] = array('CN_no' => $CN_no, 'doc_no' => $doc_no);
}
var_dump($row_set);
exit;

I adjusted the comment from http://us3.php.net/manual/en/mysqli-stmt.bind-param.php#104073 so it fits to your scenario.

Btw. With PDO as database-API this would be A LOT easier to write and to read. I might add an example for PDO later.

Answer 2

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
// search input
$input = '42-7278954,53-1217544,07-2517487';
// create and filter your search array
$iArr = array_filter(array_map('trim', explode(",", $input)), 'strlen');
// create your search string from CN_no array 
$CN_no = implode("|",$iArr); 

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT * FROM `mock_data` WHERE `CN_no` RLIKE ?")) {

    /* bind parameters for search */
    $stmt->bind_param("s", $CN_no);

    /* execute query */
    $stmt->execute();
    // get all the rows returned
    // IMPORTANT
    // Use the below syntax in case you do use native mysqlnd driver.
    // If you don't have mysqlnd installed/loaded, you will get an error
    // that "mysqli_stmt_get_result()" method is undefined. 
    $result = $stmt->get_result()->fetch_all();
    // in case you don't have native mysqlnd loaded uncomment 
    // and try the below syntax to get the result
    /* $meta = $stmt->result_metadata(); 
    while ($field = $meta->fetch_field()) { 
        $params[] = &$row[$field->name]; 
    } 
    call_user_func_array(array($stmt, 'bind_result'), $params); 
    while ($stmt->fetch()) { 
        foreach($row as $key => $val) { 
            $c[$key] = $val; 
        } 
    $result[] = $c; 
    } */
    /* *********** */
    // print output
    print_r($result);

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();

Answer 3

Some abstract php code:

  $cn = explode(',', $input1);
  $incn = str_repeat('?, ', count($cn)-1).'?'
  $doc = explode(',', $input2);
  $indoc = str_repeat('?, ', count($doc)-1).'?'
  $sql = "SELECT ... WHERE cn_no IN ({$incn}) or doc_no IN ({$indoc})";
  $result = $db->rows($sql, array_merge($cn, $doc));

Answer 4

Use FIND_IN_SET() function

Syntax : FIND_IN_SET (YOUR_INPUT_STRING_NAME, COLUMN_NAME);

YOUR_INPUT_STRING_NAME it may be 42-7278954,53-1217544,07-2517487,...

Let me know if it helps you.

Answer 5

$input = '42-7278954,53-1217544,07-2517487';
$inputs = explode(',', $input);

$new_inputs = array_map(function ($item){
    $item = trim($item);
    return "'" . $item . "'";
}, $inputs);

$string = implode(',', $new_inputs);

// string is  '42-7278954','53-1217544','07-2517487'

$sql = "SELECT * FROM mock_data WHERE CN_no IN ({$string})";

Answer 6

$input = '42-7278954,53-1217544,07-2517487';

echo $strNew=str_replace(",","','",$input);

$sql = "SELECT * FROM mock_data WHERE CN_no IN ('".$strNew."')";
$row=mysqli_query($conn,$query)or die("not fire");

try this code

Answer 7

It seems that in-clause in not getting generated correctly by your implementation.

To generate the in-clause correctly, you need to split the user input into array and then join back in-list. For example, if user have given 42-7278954,53-1217544,07-2517487 as input, in-list should be look like:

CN_No in ( '42-7278954' , '53-1217544' , '07-2517487');

Given that Shipment code and Reference code are string value, you have to enclose them in quotes ' '.

Following example may help you to generate in-list expression using implode and explode (php is very little known to me)

$in_list_cns = implode( "' , '", explode(",", $CN_no)); 
$sql = "SELECT * FROM mock_data WHERE CN_no IN ( '{$in_list_cns}')" 

Hope this help.

Also, as mentioned by other too, you may need to sanitize user input to prevent SQL-Injection attacks.

Answer 8

try

$cn = explode(',', $CN_no);
$incn = str_repeat('?, ', count($cn)-1).'?';
$doc = explode(',', $doc_no);
$indoc = str_repeat('?, ', count($doc)-1).'?';

$query = "SELECT * FROM mock_data WHERE CN_no IN ('.$incn.') or doc_no IN ({$indoc})";
$result = $conn->query($query , array_merge($incn, $indoc));