php - Mysql execute data. What separator to choose for a string, further to explode to create an array? -

mysql query

select companyname, registrationnumber, vatcode  18_6_transactionpartners  (companyname = ? or registrationnumber = ? or vatcode = ?)  

? replace $data_show_existing_records. $data_show_existing_records in form of string (user input).

at first decided create such $data_show_existing_records = $data_show_existing_records. $data_a[$i]. ','. $data_b[$i]. ','. $data_d[$i]. ','; (this created within foreach, because $i may 0, may 100)

and convert array in such way

$data_show_existing_records = substr($data_show_existing_records, 0, -1);  $data_show_existing_records = explode(",", $data_show_existing_records); 

but example companyname user may input my, company or leave input empty.

in such case error sqlstate[hy093]: invalid parameter number: number of bound variables not match number of tokens. because there more $data in array ?.

then decided replace , | (because suppose | less used ,).

but seems not solution. better (what use string/user input separator)? may need escape , (but how)? please, advice

update found solution empty input: $data_show_existing_records = $data_show_existing_records. "$data_a[$i]". ','. "$data_b[$i]". ','. "$data_d[$i]". ',';

but ,?

next update

create initial query(shortened code) , data query

foreach ($num_row_1 $i => $row) {//$num_row_1 number of rows in user input $query_to_show = $query_to_show. 'where (companyname = ? or registrationnumber = ? or vatcode = ?) '; $data_show_existing_records = $data_show_existing_records. "$data_a[$i]". '"|"'. "$data_b[$i]". '"|"'. "$data_d[$i]". '"|"'; } 

get example:

$query_to_show =

where (companyname = ? or registrationnumber = ? or vatcode = ?) or (companyname = ? or registrationnumber = ? or vatcode = ?)

and

$data_show_existing_records =

first name"|"123"|"112233"|"second name"|"456"|"445566"|"

then convert array

$data_show_existing_records = substr($data_show_existing_records, 0, -1); $data_show_existing_records = explode('"|"', $data_show_existing_records); 

and final query

$query_show_existing_records = " select companyname, registrationnumber, vatcode  18_6_transactionpartners  $query_to_show "; 

and prepare/execute

$sql_show_existing_records = $db->prepare($query_show_existing_records); $sql_show_existing_records->execute($data_show_existing_records); $data_show_existing_records1 = $sql_show_existing_records->fetchall(pdo::fetch_assoc); 

solution

changed preparation query , data following:

$flag = 0;//set $data_show_existing_records = array();  foreach ($num_row_1 $i => $row) {  if($flag == 0) { $query_to_show = $query_to_show. 'where (companyname = ? or registrationnumber = ? or vatcode = ?) '; $flag = 1; } else { $query_to_show = $query_to_show. 'or (companyname = ? or registrationnumber = ? or vatcode = ?) '; }  $data_show_existing_records[] = $data_a[$i]; $data_show_existing_records[] = $data_b[$i]; $data_show_existing_records[] = $data_d[$i];  } 

sqlstate[hy093]: invalid parameter number: number of bound variables not match number of tokens means supplied more variables there ? can bound them. looking @ code , explanation, making comma seperated list can contain 3 values, can contain 300 values if happens contain 100 rows.

to answer question: choose character, or serie of characters seperator cannot in data. choosing seperator in data, or 'unlikely' in data can cause security issue.

in specific situation, there no reason transform data comma seperated string, explode again array. add directly final format (most 2-dimensional array if want perform multiple queries):

$output = array(); for( $i = 0; $i < count( $data_a ); $i++ ) {   $output[] = array( $data_a[$i], $data_b[$i], $data_d[$i] ); }  /*** var_dump( $output );    * array(    *   array( 1, 2, 3 ),    *   array( 4, 5, 6 ),    *   array( 7, 8, 9 ), etc...    * );  ***/  foreach( $output $k => $row ) {   /*** query      * $row contains 3 values query; next loop contains next 3 values    ***/ }