Doubt on white space within PHP code

Spaces or line breaks are interpreted by MYSQLi as one of the types of placeholder the error generated is:

  

Number of elements in type definition string does not match number of bind variables in

Anything other than the types defined in documentation sidb generates the error, x is invalid character and n is position +1.

  

Undefined fieldtype x (parameter n)

Error simulation

CREATE TABLE 'pessoas' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'nome' varchar(45) DEFAULT NULL,
  'idade' int(11) DEFAULT NULL,
  'profissao' varchar(45) DEFAULT NULL,
  'data' timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ('id'),
  UNIQUE KEY 'id_UNIQUE' ('id')
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

Test file

<?php

$nome = 'teste '. rand(0, 5000);
$idade = rand(1,120);
$profissao = 'pro';


$db = new mysqli('localhost', 'root', 'root', 'web');
$sql = "INSERT INTO pessoas (nome, idade, profissao) VALUES(?,?,?)";
$stmt = $db->prepare($sql);

//quebra de linha proposital
$stmt->bind_param('s
ss', $nome, $idade, $profissao);

if(!$stmt->execute()){
    echo '<br>Erro: SQLState:'. $db->errno . ' Descrição: '. $db->error;
}else{
    echo 'sucesso';
}

The output of the above test and the variant assumes that four types have been defined and only three values (variables) are passed.

$stmt->bind_param('s ss', $nome, $idade, $profissao);
--------------------^ 

is the same:

  

Number of elements in type definition string does not match number of bind variables in

Other curious examples

$stmt->bind_param('aaa', $nome, $idade, $profissao);
$stmt->bind_param('   ', $nome, $idade, $profissao);

Output:

  

Undefined fieldtype a (parameter 2 ) in

Solutions

To keep the type definition organized, I suggest you set a pattern and assign it to a variable and remove the invalid characters with str_replace() .

$invalidos = array("\n","\r", "\r\n", " ");
$tipos ='s
s
s';

$tipos = str_replace($invalidos, "",$tipos);
$stmt->bind_param($tipos, $nome, $idade, $profissao);

The insert is performed without problems. Another simple solution is to pass the number of parameters to str_repeat() do the work, in the example I purposely used all the values as s I believe that this typing is not good at 90% or the use of them is not obvious at all .

Workaround

$tipos = str_repeat('s', 3);
$stmt->bind_param($tipos, $nome, $idade, $profissao);

In PHP , you can break spaces in some functions, there is no problem, for example in an array :

$array = array(
              'indice'=>                     'Valor',
              'indice1'                 => 'Valor' 
              );

In this function for example, you can place spaces, as long as the values in it keep the structure, it continues to work, and without any problems.

But in other cases, not even that. Let's see for example when it comes to Strings , Integers , and others, you will usually get a return error, see this example: