Trying to update database using PDO connection, says successful but no update

[PineSmokes]

Not sure why I can't get this been trying to figure it out all evening :/  The included smconfig.php contains my database password.  Any ideas, I know it's gotta be easy, I must be missing something.

 

PDO connect.php:

<?php
session_start();
include 'smconfig.php';
$db_host = "127.0.0.1";
$db_username = "root";
$db_pass = "$dbpass";
$db_name = "golden_wand";

//  PDO  CONNECT
	$db = new PDO('mysql:host='.$db_host.';dbname='.$db_name,$db_username,$db_pass);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>

http://www.golden-wand.com/members/tester.php

<?php
include "../Scripts/connect.php";
$email = "test@gmail.com";
$stmt1 = $db->prepare("SELECT id, activated, username, email, password, token FROM members WHERE email=:email LIMIT 1");
$stmt1->bindParam(':email',$email,PDO::PARAM_STR);
$stmt1->execute();
$count = $stmt1->rowCount();
if($count > 0){
	while($row = $stmt1->fetch(PDO::FETCH_ASSOC)){
		$uid = $row['id'];
		$username = $row['username'];
		$email = $row['email'];
		$hash = $row['password'];
		$activated = $row['activated'];
		$token = $row['token'];
	}
}
echo "Before: <br>";
		echo 'UID = '.$uid;
		echo '<br>Token = '.$token;
		echo '<br>Activated = '.$activated;
		echo '<br>Hash = '.$hash;


$activated="1";
$token = "md5($hash)";


try{
	$db->beginTransaction();
	$updateSQL = $db->prepare("UPDATE members SET activated=':activated' WHERE id=':uid' LIMIT 1");
	$updateSQL->bindParam(':activated',$activated,PDO::PARAM_STR);
	$updateSQL->bindParam(':uid',$uid,PDO::PARAM_INT);
	$updateSQL->execute();
	$db->commit();
	echo "<br><br><br><br>Update Successful<br><br><br><br>";
}
catch(PDOException $e){
	$db->rollback();
	echo "<br><br><br><br>Update Failed<br><br><br><br>";
}

$stmt2 = $db->prepare("SELECT id, activated, username, email, password, token FROM members WHERE email=:email LIMIT 1");
$stmt2->bindParam(':email',$email,PDO::PARAM_STR);
$stmt2->execute();
$count = $stmt2->rowCount();
if($count > 0){
	while($row = $stmt2->fetch(PDO::FETCH_ASSOC)){
		$uid = $row['id'];
		$username = $row['username'];
		$email = $row['email'];
		$hash = $row['password'];
		$activated = $row['activated'];
		$token = $row['token'];
	}
}
echo "After: <br>";
		echo 'UID = '.$uid;
		echo '<br>Token = '.$token;
		echo '<br>Activated = '.$activated;
		echo '<br>Hash = '.$hash;

?>

I own this site:  http://www.golden-wand.com/phpfreaks.txt

[Jacques1]

Remove the single quotes surrounding the placeholders. You should also get rid of the useless transaction. A single UPDATE query either suceeds or fails, there's nothing in between.

 

To check if the update actually changed the data, use PDOStatement::rowCount().

[PineSmokes]

I don't understand why the PDO version I was attempting to use isn't working, I see now that the $db->commit(); isn't even necessary I'm assuming since I'm no longer using PDO. If anyone could explain what went wrong with my PDO attempt I would still appreciate it, I found an answer for the following works but is not the answer I'm looking for I believe:

 

http://www.golden-wand.com/members/test.php

<?php
include "../Scripts/connect.php";
$email = "test@gmail.com";

$stmt1 = $db->prepare("SELECT id, activated, username, email, password, token FROM members WHERE email=:email LIMIT 1");

$stmt1->bindParam(':email',$email,PDO::PARAM_STR);
$stmt1->execute();
$count = $stmt1->rowCount();
if($count > 0){
	while($row = $stmt1->fetch(PDO::FETCH_ASSOC)){
		$uid = $row['id'];
		$username = $row['username'];
		$email = $row['email'];
		$hash = $row['password'];
		$activated = $row['activated'];
		$token = $row['token'];
		echo "Before: <br>";
			echo 'UID = '.$uid;
			echo '<br>Token = '.$token;
			echo '<br>Activated = '.$activated;
			echo '<br>Hash = '.$hash;
	}
}


$stmt2 = $db->prepare("UPDATE members SET activated = ? WHERE id = ? ");
try{
	if($activated == '0'){
		$activated=1;
	} else {
		$activated=0;
	};   //  Activated Toggle from 0 to 1
	$stmt2->execute(array($activated,$uid));
	echo "<br><br><br><br>Update Successful<br><br><br><br>";
}
catch(PDOException $e){
	$db->rollback();
	echo "<br><br><br><br>Update Failed<br><br><br><br>";
}


$stmt3 = $db->prepare("SELECT id, activated, username, email, password, token FROM members WHERE email=:email LIMIT 1");

$stmt3->bindParam(':email',$email,PDO::PARAM_STR);
$stmt3->execute();
$count = $stmt3->rowCount();
if($count > 0){
	while($row = $stmt3->fetch(PDO::FETCH_ASSOC)){
		$uid = $row['id'];
		$username = $row['username'];
		$email = $row['email'];
		$hash = $row['password'];
		$activated = $row['activated'];
		$token = $row['token'];
		echo "After: <br>";
			echo 'UID = '.$uid;
			echo '<br>Token = '.$token;
			echo '<br>Activated = '.$activated;
			echo '<br>Hash = '.$hash;
	}
}

?>

Is this fine to use or is PDO somehow safer or better?

[Jacques1]

How is this not PDO? All you've done is replace the named placeholders with positional placeholders.

 

Did you read my comment regarding the misplaced single quotes in your previous query?

[PineSmokes]

Yes I did read your answer I just posted at the same time you wrote that so it took me a minute to make sure I got it all and had no more questions here.  It was just those quotes huh?  It's always gotta be something small, it's working now can't believe I didn't figure that out by myself I thought I had tried it all.  Where exactly was the useless statement the entire statement on the end that I removed right?  Is everything good now I think I removed it.

 

http://www.golden-wand.com/members/tester.php

<?php
include "../Scripts/connect.php";
$email = "test@gmail.com";
$stmt1 = $db->prepare("SELECT id, activated, username, email, password, token FROM members WHERE email=:email LIMIT 1");
$stmt1->bindParam(':email',$email,PDO::PARAM_STR);
$stmt1->execute();
$count = $stmt1->rowCount();
if($count > 0){
	while($row = $stmt1->fetch(PDO::FETCH_ASSOC)){
		$uid = $row['id'];
		$username = $row['username'];
		$email = $row['email'];
		$hash = $row['password'];
		$activated = $row['activated'];
		$token = $row['token'];
	}
}
echo "Before: <br>";
		echo 'UID = '.$uid;
		echo '<br>Token = '.$token;
		echo '<br>Activated = '.$activated;
		echo '<br>Hash = '.$hash;

if($activated==0){$activated=1;}else{$activated=0;};   //  Activated Toggle from 0 to 1
$token = md5($hash);


$db->beginTransaction();
$updateSQL = $db->prepare("UPDATE members SET activated=:activated, token=:token WHERE id=:uid LIMIT 1");
try{
	$updateSQL->bindParam(':activated',$activated,PDO::PARAM_STR);
	$updateSQL->bindParam(':token',$token,PDO::PARAM_INT);
	$updateSQL->bindParam(':uid',$uid,PDO::PARAM_INT);
	$updateSQL->execute();
	$db->commit();
	echo "<br><br><br><br> Update Successful <br><br><br><br>";
	echo "After: <br>";
	echo 'UID = '.$uid;
	echo '<br>Token = '.$token;
	echo '<br>Activated = '.$activated;
	echo '<br>Hash = '.$hash;

}
catch(PDOException $e){
	$db->rollback();
	echo "<br><br><br><br>Update Failed<br><br><br><br>";
}

?>

Thanks for everything you really are amazing and quick

 

Now back to the activate.php page, I've been trying to fix it up so the token is inside my members table instead of off by itself.  Wasn't as easy as I thought

Edited November 21, 2015 by PineSmokes