• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

SELECT and then UPDATE prepared statement

I have decided that after trying procedural and object oriented ways I want to stick with prepared statements if possible. I am having trouble trying to update though after using select. Basically, if the record exists, an update needs to happen to that particular record:

if(isset($_GET['activecode'])) {
	
	
	
	$stmt = $link->prepare("SELECT `user_hash` FROM `db_users` WHERE `user_hash` = ?");
	$stmt->bind_param("s", $activate_url);
	$activate_url = urlencode($_GET['activecode']);
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	if($numRows === 1) {
	
		
	$stmt = $link->prepare("UPDATE user_active SET user_active = ? WHERE `user_hash` = '$activate_url'");
	$stmt->bind_param("i", 1);
	$stmt->execute();
	$stmt->close();

		
	set_message("<p>Your account has been activated.</p>");
	
	
} 

Open in new window


I am getting the following error:

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in /Applications/MAMP/htdocs/mysite/activate_account.php:20 Stack trace: #0 {main} thrown on line 20

Line 20 is:

$stmt->bind_param("i", 1);

Open in new window

0
Black Sulfur
Asked:
Black Sulfur
  • 5
  • 4
1 Solution
 
bigeven2002Commented:
Hello,
From what I read online, the prepare() statement can return false so there should be a check for that with a conditional statement.  If it does indeed return false, then perhaps there is a typo in the Select or Where clause for the table or column names.

So assuming the error corresponds with line 14 in your code sample, I would start there.
0
 
Black SulfurAuthor Commented:
Not sure if I am doing it right but I added an if statement to show me
var_dump($link->error);

Open in new window


The result is:

string(0) ""
0
 
bigeven2002Commented:
OK, if i remember correctly, a blank result or 0 is the same as false.  I'm a bit rusty with these object oriented methods so please forgive my ignorance.

Curious, instead of using bind_param in the Update query, what if you statically enter the 1 in place of the ? in the SET clause?
$stmt = $link->prepare("UPDATE user_active SET user_active = 1 WHERE `user_hash` = '$activate_url'");
#$stmt->bind_param("i", 1); # commented out

Open in new window


Also, in the UPDATE statement, is user_active also the name of the Table?  Shouldn't it be db_users?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Black SulfurAuthor Commented:
Ah, just realised my table name was wrong but even after changing i still get the same result:

if(isset($_GET['activecode'])) {
	
	
	
	$stmt = $link->prepare("SELECT `user_hash` FROM `db_users` WHERE `user_hash` = ?");
	$stmt->bind_param("s", $activate_url);
	$activate_url = urlencode($_GET['activecode']);
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	if($numRows === 1) {
	
		
	if($stmt = $link->prepare("UPDATE `db_users` SET `user_active` = ? WHERE `user_hash = '$activate_url'")) {
	$stmt->bind_param("i", 1);	
	$stmt->execute();
	$stmt->close();
	
	
	set_message("<p>Your account has been activated.</p>");
		
	} else {
		
		var_dump($link->error);
	}
	
} 
	
	else {
		
		header("location:index.php");
		
	} 

}

Open in new window

0
 
Black SulfurAuthor Commented:
Thanks for the input bigeven2002.

I am not sure I want to comment out the bind_param as I think that is the part that prevents SQL injection (I'm no expert). Am I wrong in saying that?
0
 
Black SulfurAuthor Commented:
I figured it out! It seems that you have to use a variable in the bind statement. I was just using 1.

This is what it looks like now and it seems to work:

if(isset($_GET['activecode'])) {
	
	
	
	$stmt = $link->prepare("SELECT `user_hash` FROM `db_users` WHERE `user_hash` = ?");
	$stmt->bind_param("s", $activate_url);
	$activate_url = urlencode($_GET['activecode']);
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	if($numRows === 1) {
	
		
	if($stmt = $link->prepare("UPDATE `db_users` SET `user_active` = ? WHERE `user_hash` = '$activate_url'")) {
	$stmt->bind_param("i", $user_active);	
	$user_active =1;
	$stmt->execute();
	$stmt->close();
		
	set_message("<p>Your account has been activated.</p>");
		
	} else {
		
		var_dump($link->error);
	}
	
} 
	
	else {
		
		header("location:index.php");
		
	} 

}

Open in new window

0
 
bigeven2002Commented:
OK, no problem, let's try this, which will slightly rewrite the concatenation.

$stmt = $link->prepare("UPDATE `db_users` SET `user_active` = ? WHERE `user_hash = ".$activate_url));
$stmt->bind_param("i", 1);	

Open in new window


If that still fails, then type in the manual values and comment out the bind_param line like so.  Since these are static values, that should negate SQL injection, but this is just for testing anyways.

$stmt = $link->prepare("UPDATE `db_users` SET `user_active` = 1 WHERE `user_hash = '1234567890'));
# $stmt->bind_param("i", 1);	# commented entire line out with the # sign

Open in new window

0
 
bigeven2002Commented:
OK great!  Glad you got it figured out.
0
 
Black SulfurAuthor Commented:
I figured out that I had to put 1 into a variable in order for the prepared statement to work.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now