title: Mysql execute data. What separator to choose for a string, further to explode to create an array?
body: <p>MySQL query is </p>
  
  <pre><code>SELECT CompanyName, RegistrationNumber, VatCode 
  FROM 18_6_TransactionPartners 
  WHERE (CompanyName = ? OR RegistrationNumber = ? OR VatCode = ?) 
  </code></pre>
  
  <p><code>?</code> replace with <code>$data_show_existing_records</code>. But initially <code>$data_show_existing_records</code> is in form of string (user input).</p>
  
  <p>At first decided to create such <code>$data_show_existing_records = $data_show_existing_records. $data_a[$i]. ','. $data_b[$i]. ','. $data_d[$i]. ',';</code> (this is created within foreach, because <code>$i</code> may be only 0, but may also be 100)</p>
  
  <p>And convert to array in such way</p>
  
  <pre><code>$data_show_existing_records = substr($data_show_existing_records, 0, -1);
  
  $data_show_existing_records = explode(",", $data_show_existing_records);
  </code></pre>
  
  <p>But for example for <code>CompanyName</code> user may input <code>My, company</code> or leave some input empty.</p>
  
  <p>In such case I will get error <code>SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens</code>. Because there would be more <code>$data</code> in array than <code>?</code>.</p>
  
  <p>Then decided replace <code>,</code> with <code>|</code> (because suppose <code>|</code> is less used than <code>,</code>).</p>
  
  <p>But this seems not good solution. What would be better (what to use as string/user input separator)? May be just need to escape <code>,</code> (but how)? Please, advice</p>
  
  <p><strong>Update</strong>
  Found solution for empty input: <code>$data_show_existing_records = $data_show_existing_records. "$data_a[$i]". ','. "$data_b[$i]". ','. "$data_d[$i]". ',';</code></p>
  
  <p>But what to do with <code>,</code>?</p>
  
  <p><strong>Next update</strong></p>
  
  <p>Create initial query(shortened code) and data for the query </p>
  
  <pre><code>foreach ($num_row_1 as $i =&gt; $row) {//$num_row_1 is 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]". '"|"';
  }
  </code></pre>
  
  <p>Get for example: </p>
  
  <p><code>$query_to_show =</code></p>
  
  <p><code>WHERE (CompanyName = ? OR RegistrationNumber = ? OR VatCode = ?) OR (CompanyName = ? OR RegistrationNumber = ? OR VatCode = ?)</code></p>
  
  <p>and </p>
  
  <p><code>$data_show_existing_records =</code></p>
  
  <p><code>first name"|"123"|"112233"|"second name"|"456"|"445566"|"</code></p>
  
  <p>Then convert to array</p>
  
  <pre><code>$data_show_existing_records = substr($data_show_existing_records, 0, -1);
  $data_show_existing_records = explode('"|"', $data_show_existing_records);
  </code></pre>
  
  <p>And then final query</p>
  
  <pre><code>$query_show_existing_records = "
  SELECT CompanyName, RegistrationNumber, VatCode 
  FROM 18_6_TransactionPartners 
  $query_to_show
  ";
  </code></pre>
  
  <p>And prepare/execute </p>
  
  <pre><code>$sql_show_existing_records = $db-&gt;prepare($query_show_existing_records);
  $sql_show_existing_records-&gt;execute($data_show_existing_records);
  $data_show_existing_records1 = $sql_show_existing_records-&gt;fetchAll(PDO::FETCH_ASSOC);
  </code></pre>
  
  <p><strong>Solution</strong></p>
  
  <p>Changed preparation for query and data to following:</p>
  
  <pre><code>$flag = 0;//Set as WHERE
  $data_show_existing_records = array();
  
  foreach ($num_row_1 as $i =&gt; $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];
  
  }
  </code></pre>