CODEWITHSUNDEEP
phpmysqlarrays
Hexana
Hexana

Reputation: 1135

Explode Textarea and Pass IN Select Statement

I have a text area called mycode which receives a list of codes:

1234
3456
7654
3456

etc...

My php process.php script accepts codes:

  $codes = $_POST['mycode'];

I then attempt to explode the $code into a variable called $arr:

  $arr = explode("\n", $codes);

Next I then try to pass in the $arr array into my MySQL SELECT statement:

  $sql = "SELECT code, name, FROM rawcodes where code IN ($arr)";
  $rs=$conn->query($sql);

I get data returned from the database, BUT its a record that has no code but has a name, therefore its clear that an empty array is getting created therefore passed to the mysql statement.

I have also tried: 

  $arr = explode(",", $codes);

Which also returns an error.

EDIT:

I have also tried:

implode('","',$arr)

So I need the data to actually exist in the array?

Any help appreciated.

Cheers

Upvotes: 1

Views: 269

Answers (3)

RobP
RobP

Reputation: 9522

If $_POST['mycode'] is indeed a string like 1234 3456 7654 3456 with space separators, then explode on " ". You changed the post to newlines, so I changed accordingly.

Furthermore, I recommend jumping through some extra hoops to bind each part as a parameter to prevent injection attacks. (Actually, you should validate each part to make sure it contains only digits!!)

The tricky part is that mysqli does not let you bind an array of parameters to a set of markers. This is much easier with PDO where you can have 'named labels' like ':param' in the SQL. With mysqli you have to use call_user_func_array to get the variable number of arguments. 

// removed extra comma after 'name'
$values = explode("\n", $_POST['mycode']);
$placeholders = "";
$bindTypes = str_repeat("s", count($values));
$params = array(&$bindTypes);
for($i = 0, $len = count($values), $i < $len, $i++) {
    // validate $values[$i] here! 
    if(strlen($placeholders)) $placeholders .= ", ";
    $placeholders .= "p".$i;
    $params[] = &$values[$i];
}

$sql = "SELECT code, name FROM rawcodes where code IN ($placeholders)";
$statement = $conn->prepare($sql);
// make params array act like a comma-separated argument list
call_user_func_array(array($stmt, 'bindparams'), $params);
$rs=$statement->execute();
// check return code, process results here

Upvotes: 1

MH2K9
MH2K9

Reputation: 12039

Can try using this

$in = "'".implode("','", array_filter(explode(" ", $codes))) . "'";
$sql = "SELECT code, name FROM rawcodes where code IN ($in)";

Upvotes: 1

fortune
fortune

Reputation: 3372

You have an unwanted , after name in sql

$sql = "SELECT code, name, FROM rawcodes where code IN ($arr)";

To be

$sql = "SELECT code, name FROM rawcodes where code IN ($arr)";

Upvotes: 1

Related Questions