PDO help

[zerotolerance]

Hi guys, I have this code:

 

$query = "INSERT INTO ".TBL_USERS." SET username = :username, password = :password, usersalt = :usersalt, userid = 0, userlevel = $ulevel, email = :email, timestamp = $time, actkey = :token, ip = '$userip', regdate = $time"; 
      $stmt = $this->connection->prepare($query);
      return $stmt->execute(array(':username' => $username, ':password' => $password, ':usersalt' => $usersalt, ':alliance' => $alliance, ':email' => $email, ':token' => $token));

 

however, i added a new column called "alliance", which is null. However when I try to submit my data, this error pops up:

 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

 

If I remove the alliance, it seems to work just fine.. have I done something wrong? The error on the page seems to be showing the value of the alliance I orginally put in the registeration form, but it won't get recorded due to this error.

[creata.physics]

If I'm wrong about this then the reason is because of my PDO ignorance but your query is wrong.

Your query looks like an update quey, not an insert.

Insert queries you need to define the keys in one bracket and have the values in the next properly corresponding to the keys.

INSERT INTO ".TBL_USERS." ( username, password, usersalt, userid, userlevel, email, timestamp, actkey, token ) VALUES ( :username, :password, :usersalt, :userid, :userlevel, :email, :timestamp, :actkey, :token )

[zerotolerance]

But how is it updating when I'm saying my query to "INSERT INTO ".TBL_USERS.""? o.o

[zerotolerance]

Sorry for double posting but I sorta do have it in 2 brackets, my null fields are in the second bracket.

[cpd]

You haven't actually added Alliance to your query. You've added it as a parameter but not to the query.

 

In future use a try statement and catch the exception where you can echo it out.

 

try {
    $stmt->execute(PARAMS);
} catch(PDOException $e){
    echo $e->getMessage();
}

[zerotolerance]

You haven't actually added Alliance to your query. You've added it as a parameter but not to the query.

 

In future use a try statement and catch the exception where you can echo it out.

 

try {
    $stmt->execute(PARAMS);
} catch(PDOException $e){
    echo $e->getMessage();
}

 

So how would I add it to query? Isn't it inside the query if there's a variable defenfing it inside the code?

[cpd]

I'm gonna go ahead and assume you've taken this query from somewhere else then because you should be able to add another parameter to your query seeming as you've done it for all the others...

 

And creata was right, your insert query structure is incorrect. Review what creata has said.

[kicken]

If I'm wrong about this then the reason is because of my PDO ignorance but your query is wrong.

Your query looks like an update quey, not an insert.

 

That syntax for an INSERT query is valid (for mysql at least), it's not just commonly used.

 

So how would I add it to query? Isn't it inside the query if there's a variable defenfing it inside the code?

 

No, defining a variable does not automatically make it part of the query.  You need to edit the query to include that field and value.  Let's make the query a little more readable first:

 

$query = "
INSERT INTO ".TBL_USERS." 
SET 
username = :username
, password = :password
, usersalt = :usersalt
, userid = 0
, userlevel = $ulevel
, email = :email
, timestamp = $time
, actkey = :token
, ip = '$userip'
, regdate = $time
"; 

 

Notice how you do not have anywhere in that query where you are setting your alliance column?  You need to add that in addition to adding the variable to the parameters array (which you did do).  While your at it, you should use parameters for your other variables as well rather than embed them in the query (unless you have a specific / valid reason for it).

 

$query = "
INSERT INTO ".TBL_USERS." 
SET 
username = :username
, password = :password
, usersalt = :usersalt
, userid = 0
, userlevel = :ulevel
, email = :email
, timestamp = :time
, actkey = :token
, ip = :userip
, regdate = :time2
, alliance=:alliance
"; 
      
$stmt = $this->connection->prepare($query);
return $stmt->execute(array(
':username' => $username
, ':password' => $password
, ':usersalt' => $usersalt
, ':alliance' => $alliance
, ':email' => $email
, ':token' => $token
, ':ulevel' => $ulevel
, ':time' => $time
, ':time2' => $time
, ':userip' => $userip
));