Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SELECT and then UPDATE prepared statement

Posted on 2016-09-24
9
Medium Priority
?
48 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 
LVL 1

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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 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