Solved

SELECT and then UPDATE prepared statement

Posted on 2016-09-24
9
33 Views
Last Modified: 2016-09-29
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
Comment
Question by:Black Sulfur
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:bigeven2002
ID: 41813801
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
 

Author Comment

by:Black Sulfur
ID: 41813824
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
 
LVL 17

Expert Comment

by:bigeven2002
ID: 41813834
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Black Sulfur
ID: 41813838
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
 

Author Comment

by:Black Sulfur
ID: 41813845
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
 

Accepted Solution

by:
Black Sulfur earned 0 total points
ID: 41813850
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
 
LVL 17

Expert Comment

by:bigeven2002
ID: 41813851
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
 
LVL 17

Expert Comment

by:bigeven2002
ID: 41813853
OK great!  Glad you got it figured out.
0
 

Author Closing Comment

by:Black Sulfur
ID: 41821344
I figured out that I had to put 1 into a variable in order for the prepared statement to work.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
showing loader for php/mysql/ajax live search 13 26
PHP Form Calculate Total Price 10 43
How do I fix this UPDATE error? 7 24
Present Absent from working date rage 11 21
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question