Asked  6 Months ago    Answers:  5   Viewed   95 times

I'm getting the error "SQLSTATE[HY093]: Invalid parameter number" when I try to run the below function:

function add_persist($db, $user_id) {
    $hash = md5("per11".$user_id."sist11".time());
    $future = time()+(60*60*24*14);
    $sql = "INSERT INTO persist (user_id, hash, expire) VALUES (:user_id, :hash, :expire) ON DUPLICATE KEY UPDATE hash=:hash";
    $stm = $db->prepare($sql);
    $stm->execute(array(":user_id" => $user_id, ":hash" => $hash, ":expire" => $future));
    return $hash;
}

I feel like it's something simple that I'm just not catching. Any ideas?

 Answers

61

Try:

$sql = "INSERT INTO persist (user_id, hash, expire)
        VALUES (:user_id, :hash, :expire)
        ON DUPLICATE KEY UPDATE hash=:hash2";

and

$stm->execute(
    array(":user_id" => $user_id, 
          ":hash" => $hash, 
          ":expire" => $future,
          ":hash2" => $hash)
);

Excerpt from the documentation (http://php.net/manual/en/pdo.prepare.php):

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

Sunday, June 6, 2021
 
Kevin_Kinsey
answered 6 Months ago
89

Your Query

   $select = "INSERT INTO registro (nome, usuario, email, senha, cpf, rg)
                                    VALUES (:nome, :usuario, email, :senha, :cpf, :rg)";

After change Missing : in email

  $select = "INSERT INTO registro (nome, usuario, email, senha, cpf, rg)
                                        VALUES (:nome, :usuario, :email, :senha, :cpf, :rg)";
Wednesday, March 31, 2021
 
Whakkee
answered 9 Months ago
53

I guess using a reference &$val instead of a value $val is what causes the issue.

Please try this code instead:

public function bindParam($place, $val, $dataType)
{
    if(!$this->stmt) throw new Exception('PDO Statement is empty');
    $this->stmt->bindParam($place, $val, $dataType);
    return $this;
}

EDIT

My above answer is wrong.

Try modifying the execute method:

public function execute(array $params = array()){
    if(!$this->stmt) throw new Exception('PDO Statement is empty');
    $this->stmt->execute();
    return $this;
}

Passing an empty array as parametre to the execute method removes all previous bindings. This is why bindParam returned true (successfully bound), yet the "no params were bound" error appeared as soon as you called execute.

Wednesday, March 31, 2021
 
muffe
answered 9 Months ago
72

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.
mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.
PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.


* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

Thursday, June 10, 2021
 
Zulakis
answered 6 Months ago
29

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Wednesday, August 18, 2021
 
derobert
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share