Doubt about white spaces within PHP code

Question

I’m making the connection with prepared statments to insert many variables, and in some cases it takes me a long time to find the mistakes, and now I put in one here and hit me a question:

White spaces make a difference, I can break lines and give spaces quietly?

For example, I can do:

mysqli_stmt_bind_param($stmt,
    'sisss
    siiii
    iissi

Or has to be:

mysqli_stmt_bind_param($stmt, 'sissssiiiiiissi ....

The same for variables and field declaration. I’m putting 3 in 3 in each row for everything, to give to count easier and visualize the lack of some variable etc...

$sql = "INSERT INTO tabela(
campo1, campo2, campo3,
campo4, campo5, campo6 ....

Only the values that I’m putting 10 out of 10:

VALUES
(
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ....

In the case of the error in question, I have already checked a million times, so I know that nothing is missing, everything has 138oooo!! (:/) So I think there must be some mistake in the identation, because there are spaces and line breaks too...

The error is as follows:

"Number of Elements in type Definition string doesn’t match number of bind variables"

(This number of bind variables are the ?, ?, or are the ssss . Do you have any way of knowing at least what the difference is, whether of a field or 20?)

So here in this case the question is: Might white spaces be affecting the code? How PHP interprets the whitespace inside the script (I searched, but only found explanations about the trim() :) :/ ) ?

Answer 1

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

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

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

But in other cases, not so much. Let’s take for example when it comes to Strings, Whole, and others, usually you will get any error as a return, see this example:

$n = 1.     50; // Quebra o código e retorna um erro de sintaxe;
$n1 = 1.50;     // retorna 1.5 como deveria ser
$string = "E                       u"; // Retorna (E     u) com um espaço inserido
$string1 = "Eu"; // Retorna (eu) do jeito como foi formatado

echo $n . "<br/>"; // A execução do código vai parar nesta linha, por causa da quebra
echo $n1 . "<br/>";
echo $string . "<br/>";
echo $string1 . "<br/>";

It turns out that sometimes it’s not about writing the code the way you want it to be, it’s about good practice, so you can avoid as many mistakes as possible, because you’re not going to write the code in an inconvenient way either, that even we may have problems to analyze. So it’s good to always keep the things that are supposed to be connected, connected, and the things that are supposed to be separate, separate, because even though we understand the code, despite the spaces that are inserted, the interpreter most of the time can’t, a reference to that, is the example I passed above.

The only advice here is to maintain good practice, and if people write the codes without the spaces to this day, it’s because they themselves must have tested it. Otherwise we would be seeing spaces in the official examples of the PHP, and others.

Answer 2

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

Number of Elements in type Definition string doesn’t match number of bind variables in

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

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 variant understands that four types have been defined and only three values have passed (variables).

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

is the same:

Number of Elements in type Definition string doesn’t match number of bind variables in

Other curious examples

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

Exit:

Undefined fieldtype to (Parameter 2) in

Solutions

To keep the type setting organized, I suggest you set a pattern and assign it to a variable and remove 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 smoothly. Another simpler solution is to pass the number of parameters to str_repeat() do the work, in the example I purposefully used all values as s I believe that this typing is of no use at 90% or their use is not at all obvious.

Workaround

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