10

I am trying to insert UUID() together with my INSERT query.

$handle->beginTransaction();
// Define query
$query = "INSERT INTO users (users_uuid, type_id) VALUES (:uuid, :type_id)";
// Prepare statement
$stmt = $handle->prepare($query);
// Bind parameters
$stmt->bindParam(':uuid',"SELECT UUID()",PDO::PARAM_STR);
$stmt->bindParam(':type_id',1,PDO::PARAM_INT);
// Execute query
$stmt->execute();
$handle->commit();

This query return this error Cannot pass parameter 2 by reference ... on line 51. And it points to the line $stmt->bindParam(':uuid',"SELECT UUID()",PDO::PARAM_STR);

What am I doing wrong in here?

fishcracker
  • 2,401
  • 5
  • 23
  • 28
  • Possible duplicate of [PHP error: "Cannot pass parameter 2 by reference"](https://stackoverflow.com/questions/13105373/php-error-cannot-pass-parameter-2-by-reference) – Cees Timmerman Sep 22 '17 at 08:40

4 Answers4

33

The second argument to bindParam is passed by reference and should be a variable. You are directly passing the values which is not allowed.

Place UUID() directly in the query because if it is bound as a parameter, it would be placed in the query as a quoted string and will not be evaluated to a UUID value.

You can place the 1 directly in the query too. Or assign 1 to a variable and give that variable as the second argument while binding the parameter :type_id. 

$type_id = 1;
$stmt->bindParam(':type_id', $type_id, PDO::PARAM_INT);
air4x
  • 5,618
  • 1
  • 23
  • 36
  • Wait I'm lost, pardon my technical terms insufficiency. But can you show what you mean in code? – fishcracker Nov 16 '12 at 00:14
  • It does works now, as you suggested, however I am receving a `NULL` return from `var_dump($stmt->execute())` – fishcracker Nov 16 '12 at 00:31
  • Try `var_dump($stmt->errorInfo());` after execution and check the error message. – air4x Nov 16 '12 at 00:34
  • Ahh seen it, thanks it works now! Added question (if okay) what if the generate UUID collides, is there any way it will regenerate/attempt again? I'm looking on this `ON DUPLICATE KEY UPDATE` syntax, but not sure if this is what I'm looking for. – fishcracker Nov 16 '12 at 00:38
  • I have an error when I use `errorInfo()` but none with `getMessage()` even if I have an attribute set `$handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` – fishcracker Nov 16 '12 at 00:42
  • It is highly unlikely that generated UUIDs will collide. There are many questions regarding possiblity of UUID collision for more details. – air4x Nov 16 '12 at 00:48
  • 1
    You should be getting an exception in this case, unless you have put them inside `try/catch`. If you have them in `try/catch` catching `PDOException $e`, try `echo $e->getMessage();die;` inside `catch`. – air4x Nov 16 '12 at 00:54
2

There's no need to bind it in this case, simply include it in your query:

$query = "INSERT INTO users (users_uuid, type_id) VALUES (UUID(), :type_id)";

..then bind :type_id as you already are.

Madbreaks
  • 19,094
  • 7
  • 58
  • 72
  • Even if I did that still it returs the same error, I even replace `SELECT UUID()` with `789EF1A1-0063-44D4-B300-84CDD8D875A5` as string but no luck. – fishcracker Nov 16 '12 at 00:10
  • But did you remove `$stmt->bindParam(':uuid',"SELECT UUID()",PDO::PARAM_STR);`? – Madbreaks Nov 16 '12 at 00:13
  • Yeah I did. Leaving `$stmt->bindParam(':type_id',1,PDO::PARAM_INT);` the only one left. – fishcracker Nov 16 '12 at 00:14
  • If you bind as a parameter, it will be escaped and will end up as a literal string. What you want to do is have `UUID()` directly in your query. No need for a placeholder for that. – tadman Nov 16 '12 at 00:21
0

your INSERT INTO users (users_uuid, type_id) VALUES (SELECT UUID(), 1)

is not a valid mysql query

try to get uuid() first, then insert the value into your users table

user1283182
  • 131
  • 3
0

If you are passing a value, use this:

$type_id = 1;
$stmt->bindValue(':type_id', $type_id, PDO::PARAM_INT);

If you are binding a parameter (where you pass a reference to the variable), then do this:

$stmt->bindParam(':type_id', $type_id, PDO::PARAM_INT);

// now you can update $type_id, since it is a reference, and execute multiple times:
foreach($id as $i){
    $type_id = $i;
    $stmt->execute();
}
Frank Forte
  • 2,031
  • 20
  • 19