[RESOLVED] PDO statement fails

[igorlopez]

Hi,

Problem is solved, it was not possible using defines as values in pdo sql statements.
I have the following code (Edited by applying comment so it is easier to find where the pb is)

// password 1 is good, now it just has to be equal to password 2
/*
if ($_password1 == $_password2)
{
   $updateDB = TRUE;
   // password is OK, proceed with updating the DB
   // start by getting the users data from Register
   $stmt1 = $dbh->prepare("SELECT * FROM Register WHERE rkey = ?");
   $stmt1->execute(array($_SESSION['_key']));
   $row = $stmt1->fetch(PDO::FETCH_ASSOC );
   $name = $row['name'];
   $email = $row['email'];
   $_key  = $row['rkey'];
   $sessKey = $_SESSION['_key'];
  // Added clearing of stmt1 but it did not help
   $stmt1 = NULL;

   // Prepare the additional data
*/
   // Added revision as a variable assigned the value of a define
   $rev = _TBL_USERS_REV;
/*
   $salt = generateRandStr(_SALT_LENGTH);
   $encpassword = hash("sha256", $salt . $_password1);
   // file root for downloads is for the moment located at ../uploads/<uname>
   // so wee need to create that directory. The only reason for keeping uFileRoot
   // in DB is if we change the mapping of users directory.
   $froot = _DOC_UPLOAD_ROOT . $_uname;
   mkdir($froot);
   switch ($_SESSION['lang'])
   {
      case "en":
      $lang = 'EN';
      break;
      case "se":
      $lang = 'SV';
      break;
      case "ge":
      $lang = 'GE';
      break;
      case "es":
      $lang = 'SP';
      break;
   }
   $time = time();
*/				
   // Add user data into Users
   $stmt1 = $dbh->prepare(
     "INSERT INTO Users VALUES (NULL, :rev, :uname, :pass," .
     " :salt, 0, :rname, :email, :froot, NULL, 'N', 0, :lang, :time)");
   // Added :rev as bounded variabel
   $stmt1->bindParam(':rev', $rev);
   $stmt1->bindParam(':uname', $_uname);
   $stmt1->bindParam(':pass',  $encpassword);
   $stmt1->bindParam(':salt',  $salt);
   $stmt1->bindParam(':rname', $name);
   $stmt1->bindParam(':email', $email);
   $stmt1->bindParam(':froot', $froot);
   $stmt1->bindParam(':lang',  $lang);
   $stmt1->bindParam(':time',  $time);
   $stmt1->execute();
 /* Table Schema for Users
create table Users(
 uId           int(10) unsigned    NOT NULL auto_increment,
 uRev          tinyint unsigned    NOT NULL default 0,
 uName         varchar(16)         NOT NULL default '',
 uPwd          varchar(64)         NOT NULL default '',
 uSalt         char(16)            default '',
 uLoginFail    tinyint unsigned    NOT NULL default 0,
 uRealName     varchar(100)        NOT NULL default '',
 uEmail        varchar(100)        NOT NULL default '',
 uFileRoot     varchar(100)        NOT NULL default '',
 u_iId         int(10) unsigned,
 uClass	       enum('N', 'P', 'A') default 'N',
 uUlSize       int(16) unsigned    default 0,
 uDefLang      enum('SV', 'SP', 'EN', 'GE', 'FR') default 'SV',
 uActDate      int(10) unsigned,
 PRIMARY KEY (uId)
);
*/
   // Remove key from Register
/*   removeRegKey($dbh, $_key);

   $_passwordMatch = TRUE;
   $_passwordValid = TRUE; */
}

The first PDO statement goes thru OK, i.e

$row = $stmt1->fetch(PDO::FETCH_ASSOC );

works as expected but the second set where I want to update the Users table nothing happens. The Users table does not get what I beleive it should get. I am echoing out stuff in the output section depending on result from the functional logics and it all seems to behave as expected (doesn't it always...)
I am very new to PDO and don't know how to trouble shoot.
Maybe it is a very simple problem that I just don't see.

Edit:
I started thinking about the different mysql types in my table and that I maybe need to quote my strings.
I made a quick test where I put ' around each bind variable that is a string.

$podStr = "INSERT INTO Users VALUES (NULL, _TBL_USERS_REV, ':uname', ':pass'," .         

          " ':salt', 0, ':rname', ':email', ':froot', NULL, 'N', 0, ':lang', :time)";
$stmt1 = $dbh->prepare($podStr);

This change gave at least an error:

Warning: PDOStatement::execute() [function.PDOStatement-execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /home/igor/Dev/workspace/Lopez/webRoot/regist_confirm.php on line 212

Somehow is there a missmatch between my tokens in $podStr and number of bindParam variables or?????

Edit: Updated table schema to match DB (hash("sha256", string) produces 64 chars)

Edit: If I remove all markers in the sql statement like

$podStr = "INSERT INTO Users VALUES (NULL, 1, 'anyName', 'as1efGhIJ'," .
          " 'liJHyTGRe', 0, 'Mr Smith', '[e-mail]', '/home/igor/', NULL, 'N', 0, 'SV', 17)";
$stmt1 = $dbh->prepare($podStr);
$stmt1->execute();

I do get data into the Users table.
Something is definitely amiss with the :markers and the corresponding bindParam statements.

Edit: Commented out the part of the code which is working and just left the part where the problem is. Note, some of you don't like to read a lot of code and others want to see what is outside. Trying to accommodate both.
Thanx for reading so far