Jump to content

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


PineSmokes
Go to solution Solved by Jacques1,

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by PineSmokes
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.