1

The UserId field does not get updated with the value passed by the application, whereas all the other fields get updated.

This is my insert query and other related code from my app:

     $sql = "INSERT INTO usermaster (UserId, UserName, Password,OwnerOrEmp, DBName, DeleteFlag, CreateDate, UpdateDate) values(:id, :userName, :password, :ownerOrEmp, :dBName, :deleteFlag, :createDate, :updateDate)";
                    $q = $pdo->prepare($sql);
                    //echo $uid;
                    $q->bindParam(':id', $uid);
                    $q->bindParam(':userName', $name);
                    $q->bindParam(':password', $pass);
                    $q->bindParam(':ownerOrEmp', $ownEmp);
                    $q->bindParam(':dBName', $dbName);
                    $q->bindParam(':deleteFlag', $delFlag);
                    $q->bindParam(':createDate', $curr_date);
                    $q->bindParam(':updateDate', $curr_date);
                    $q->execute();

This is how I create my table:

CREATE TABLE `USER_MASTER` (`UserID`      varchar(8)    NOT NULL,
                            `Password`    varchar(10)   NOT NULL,
                            `UserName`    varchar(100)  CHARACTER SET utf8 NOT NULL,
                            `OwnerOrEmp`  char(1)       NOT NULL,
                            `DBName`      varchar(100)  NOT NULL,
                            `DeleteFlag`  char(1)       NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And the result is

result

Fabio says Reinstate Monica
  • 5,271
  • 9
  • 40
  • 61
SSUser
  • 31
  • 3
  • echo $uid; What is the value? – Kostas Mitsarakis Aug 30 '16 at 21:40
  • $uid : 00100001 is the value and the data type is varchar in mysql, string in my application – SSUser Aug 30 '16 at 21:42
  • 2
    Try: $q->bindParam(':id', $uid, PDO::PARAM_STR, 8); – Kostas Mitsarakis Aug 30 '16 at 21:50
  • I added the line. the result still remains the same. With reference to my result pic there seems some blank spaces added into the UserId field each time a record is inserted. – SSUser Aug 30 '16 at 22:05
  • Well, try this: $q->bindParam(':id', '\''.$uid.'\'', PDO::PARAM_STR, 8); – Kostas Mitsarakis Aug 30 '16 at 22:07
  • @KostasMitsarakis Quoting a value being supplied as a param is an extremely odd thing to be doing. This is probably a mistake. – tadman Aug 30 '16 at 23:51
  • 1
    A) Use the [`trim`](http://php.net/manual/en/function.trim.php) function on your data if it has stray spaces. B) Don't use `latin1` as an encoding. Use UTF-8 if possible. You clearly have non-Latin characters in this data and setting encoding per-column is going to cause trouble. C) 10 characters for a password is atrocious. I'm presuming you're using **plain text** passwords since even a SHA1 hash will have no hope of fitting in there. At the very least use [`password_hash`](http://php.net/manual/en/function.password-hash.php). – tadman Aug 30 '16 at 23:52
  • thank you all for your kind insight. @tadman your point B) about encoding to UTF-8 did the trick! thank you! Now the current situation is as follows, as I am trying out further, meanwhile I though of posting it as well: I am able to store the UserId in db, but my php $uid is of type string, but as you can see from the above pic UserId although being varchar and should store the numeral as string, somewhere in between the "00100002" type string-numeral is converting to just numeral as "100002", drops the initial "00". so looking out for where is the internal casting occurring. Thanks again all. – SSUser Aug 31 '16 at 02:22

0 Answers0