SQL选择具有多个值和可变数据

Question

I have a table that contains a Job Number field (jobno). This field contains data that looks like '123456' or it could look like '345678 Acme'. Users of the website will type in a request for a job number. I have no problem searching and returning the desired data using a simple SELECT - WHERE - LIKE command. My problem is, users want to be able to now enter multiple Job numbers in their request field. i.e.: 123456, 345678. Etc.

How would I go about creating a select for this? I understand the use of the "WHERE jobno IN" and this method works fine for job numbers that are only a number. However, this does not allow for wildcards therefore I'm unable to return data such as '345678 Acme'. Also, I cannot use a series of "OR" because I would not know how many job numbers the user might enter to search for.

I hope this is enough explanation. Any help would be great. Thanks!

Answer 1

You should first do an explode of the input from the user. Then you have different options of what you could do, you could run a query for each number, and then just add all the resulting rows to an array (you could get duplicated results), or you could build the query on the fly.

Example:

// Input $_POST['jobNo'] = '123,1234,abc,abcde'
$str = $_POST['jobNo'];
$pieces = explode(',',$str); // returns array("123","1234","abc","abcd")
$sql = "SELECT * FROM table WHERE (";
$i = 0;
foreach( $pieces as $piece )
{
    if( $i !== 0 )
        $sql .= 'OR ';
    $sql .= "column LIKE '%". $piece ."%' ";
    $i++;
}
if( $i === 0 )
    $sql .= "1 = 1";

$sql .= ")";

// $sql = SELECT * FROM table WHERE (column LIKE '%123%' OR column LIKE '%1234%' OR column LIKE '%abc%' OR column LIKE '%abcd%')