Oh boi SQLSTATE[HY093] I Stuck

[AzeS]

Exception:
PDOException:
SQLSTATE[HY093]:
Invalid parameter number in *.php:1732 Stack trace: #0 *.php(1732):
PDOStatement->execute(Array)
#1 crm_for.php(23): *->crm(0, 1, Array) #2 *.php(1):
require_once('/var/www/html/C...') #3 {main}

Hello fellow people,

i need a rant. An error is appearing in my new CRM system. there are No Numeric values in transfer on the pdo statement but the compiler isn't on my side.
the following SQL Statement is for concating an intitated Null value in the CRM table i have a workaround but its inefficiant and dataconsuming.
I would like to estimate if there is an null value and overrite it with general text for later sentiment analysis.

Error throwing Code:

					try {
  						$sql = $this->Db->prepare("UPDATE *** SET data =(CASE WHEN (NULL) THEN :f ELSE (CONCAT(data,:f)) END) WHERE lead=:e");
 						$preset = array(':e' => $data["lead"], ':f' => $data["comment"] . $data["text"]);
 						$sql->execute(array(':e' => $data["lead"], ':f' => $data["comment"] . $data["text"]));
 					} catch (PDOException $e) {
 						$this->report("crm_err: " . $e);
 						return $e . " Fehler bei migration in die Datenbank, bitte informieren Sie einen Administrator." . $current_date_frep;
 					}
 					return "Ihr Text wurde erfolgreich in die Datenbank migriert, weiter so!" . var_dump($preset);
 					break;

Initiating Code:

echo *->crm(0,1,array('lead' => "account", 'comment' => "send:",'text' => "text")) . "<br>";
echo *->crm(0,1,array('lead' => "account", 'comment' => "recv:",'text' => "text")) . "<br>";

 

[Barand]

First thing to do is correct your case statement

CASE WHEN data IS NULL THEN :f ELSE (CONCAT(data,:f)) END

Check content of $data array.

[AzeS]

Corrected and checked. I want to smash my head in to the keyboard untill it all stops ;-;

Error: 

array(3) { ["lead"]=> string(15) "support@web.web" ["comment"]=> string(5) "send:" ["text"]=> string(4) "text" } 
PDOException: 
SQLSTATE[HY093]: Invalid parameter number in /var/www/html/**/**.php: 
1732 Stack trace: #0 /var/www/html/**/**.php(1732): 
PDOStatement->execute(Array) #1 /var/www/html/**/**/**/**.php(23): 
**->crm(0, 1, Array) #2 /var/www/html/**/**.php(1): require_once('/var/www/html/C...') #3 {main} 
Fehler bei migration in die Datenbank, bitte informieren Sie einen Administrator. 05-20-2019 09:31:07

Error throwing, Adjusted Code:
 

 					try {
  						$sql = $this->Db->prepare("UPDATE ** SET data = CASE WHEN data IS NULL THEN :f ELSE (CONCAT(data,:f)) END WHERE lead=:e");
 						$preset = array(':e' => $data["lead"], ':f' => $data["comment"] . $data["text"]);
 						$sql->execute(array(':e' => $data["lead"], ':f' => $data["comment"] . $data["text"]));
 					} catch (PDOException $e) {
 						$this->report("crm_err: " . $e);
 						return $e . " Fehler bei migration in die Datenbank, bitte informieren Sie einen Administrator. " . $current_date_frep . 									var_dump($data);
 					}

Initiating Code:

echo *->crm(0,1,array('lead' => "support@web.web", 'comment' => "send:",'text' => "text")) . "<br>";
echo *->crm(0,1,array('lead' => "support@web.web", 'comment' => "recv:",'text' => "text")) . "<br>";

The strange thing is, that there isnt even a number in transfer to the PDO execution, so where does it get the parameter?!

Edited May 20, 2019 by AzeS

[Barand]

It's objecting to the number of parameters. Your query needs 3 you only provided 2. (You cannot re-use :f (you'll need :e, :f, :g)

$sql = $this->Db->prepare("UPDATE ** SET data = CASE WHEN data IS NULL THEN :f ELSE (CONCAT(data,:g)) END WHERE lead=:e");

$sql->execute(array( ':e' => $data["lead"], 
                     ':f' => $data["comment"] . $data["text"],
                     ':g' => $data["comment"] . $data["text"]
              ));

 

[AzeS]

As soon as i have generated proper income ill donate a little

SOLVED BY BARAND BITCHES!

[Barand]

You appear to be suffering from a sufeit of exuberance.