rowCount returns 0 even if it should not

Question

$this->db->beginTransaction(); $this->db->query ('LOCK TABLES users WRITE'); $sql = 'INSERT INTO users (uname) VALUES (:uname)'; $sth = $this->db->prepare ($sql); $sth->bindParam (':uname', $uname); $sth->execute (); if ($sth->rowCount()==0) { $this->db->rollBack(); $this->db->query ('UNLOCK TABLES'); throw new Exception('<strong>Oh snap!</strong> User name is taken! Try again.'); }

I set up a user in my database manually that is called "test". And when I created a user called "test2" it worked. But whenever I try to create a third user I get rowCount = 0.

The uname in the db is varchar(15).

if(isset($_POST['regUser']) && isset($_POST['regPwd']) && isset($_POST['regConfirmPwd'])) { if($_POST['regPwd'] == $_POST['regConfirmPwd'] ) { $user->newUser($_POST['regUser'], $_POST['regPwd']); } else { $user->error = "<strong>Oh snap!</strong> The passwords don't match!"; } }

Send the post info to my newuser function and it stops on the first bit of code there. Any ideas?

Are you looking at your actual request? echo your full request before you try it in a query on the database. — L0j1k
– L0j1k, Commented Feb 27, 2013 at 13:35
How do I echo the full request? I did a debugDumpParams and got SQL: [41] INSERT INTO users (uname) VALUES (:uname) Params: 1 Key: Name: [6] :uname paramno=-1 name=[6] ":uname" is_param=1 param_type=2 in return — Fumler
– Fumler, Commented Feb 27, 2013 at 13:46
Totally missed the database structure comment. uid int(PK), uname varchar(15), pwd varchar(300), fname, lname, adress etc. Had the uname as unique index but removed it for testing purposes. — Fumler
– Fumler, Commented Feb 27, 2013 at 13:54

acutesoftware · Accepted Answer · 2013-02-27 13:36:09Z

1

Instead of inserting the user and then rolling back if taken, it might be better to test if that user is available (after locking the table), and then doing the insert with confidence.

answered Feb 27, 2013 at 13:36

acutesoftware

1,0913 gold badges15 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

crush Over a year ago

I'm not sure why he is doing a rollback at all. Clearly, if rowCount == 0, that means that the INSERT failed, which also means that he must be keying on the username (making it unique). Therefore, if the INSERT failed, why rollBack() at all? Also, I'm not sure that table locking is beneficial in this situation, nor making a transaction. Using a transaction for a single INSERT doesn't seem to make any sense.

Fumler Over a year ago

rollbacking to get in the habit of doing it for later ventures, it does nothing in this case, yes. Locking the tables to be safe.

crush Over a year ago

You don't need to lock the table here because your INSERT is designed to fail if another username exists in the table. If another thread performs an INSERT with the same username 1 nanosecond before this thread, then you still want this query to fail (and it will). You shouldn't LOCK the table unless you it is necessity.

Collectives™ on Stack Overflow

rowCount returns 0 even if it should not

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related