PDO prepare: binding a parameter after SELECT

[junkomatic]

I am a beginner programmer, reading the manual and slamming my head against the wall isnt helping me with this one.

 

I am doing a phpAcademy tutorial on 'Regester and Login' scripts and the tutorial uses mysql_connect, but I am trying to crossover to PDO and Im trying to make the functions work.

 

Having trouble with this one, heres what I tried:

The desired result is to retrive the $data array with the listed $fields selected.

function user_data($user_id) {
	$data = array();
	$user_id = (int)$user_id;
	
	$func_num_args = func_num_args();
	$func_get_args = func_get_args();
	
	if ($func_num_args > 1) {
		unset($func_get_args[0]);
		
		$fields = '`' . implode('`, `', $func_get_args) . '`';              //?? THIS LINE FORMATS THE FIELDS WITH BACKTICKS AND COMMAS
		
		$data = $db->prepare('SELECT ? FROM `users` WHERE `user_id` = ?');  //?? IM HAVING TROUBLE GETTING THIS TO BE AN OBJECT
		$data->bindParam(1, $fields);                                       //?? I THINK THE VARIABLE '?' AFTER SELECT IS MESSING IT UP
		$data->bindParam(2, $user_id);
		$data->execute();
		return ($data->fetchColumn()); 
	}
}

function logged_in() {
	return (isset($_SESSION['user_id'])) ? true : false;
}

//-----------------------------------------------------------------------------

if (logged_in() === true) {
	$session_user_id = $_SESSION['user_id'];
	$user_data = user_data($session_user_id, 'user_id', 'username', 'password', 'first_name', 'last_name', 'email');
}

The tutorial code, using a mysql_connect looks like this:

function user_data($user_id) {
	$data = array();
	$user_id = (int)$user_id;
	
	$func_num_args = func_num_args();
	$func_get_args = func_get_args();
	
	if ($func_num_args > 1) {
		unset($func_get_args[0]);
		
		$fields = '`' . implode('`, `', $func_get_args) . '`';
		
		$data = mysql_fetch_assoc(mysql_query("SELECT $FIELDS FROM `users` WHERE `user_id` = $user_id"));
                return $data;
	}
}

Probably not necessary for answering my question, but for good measure  I will include the rest of my functions that I have already managed to recode to work with my PDO connection:

function user_exists(PDO $db, $username) {
	$stmt = $db->prepare('SELECT COUNT(1) FROM `users` WHERE `username` = ?');
	$stmt->bindParam(1, $username);
	$stmt->execute();
	return (bool) $stmt->fetchColumn();
}

function user_active(PDO $db, $username) {
	$stmt = $db->prepare('SELECT COUNT(1) FROM `users` WHERE `username` = ? AND `active` = 1');
	$stmt->bindParam(1, $username);
	$stmt->execute();
	return (bool) $stmt->fetchColumn();
}

function user_id_from_username(PDO $db, $username) {
	$stmt = $db->prepare('SELECT `user_id` FROM `users` WHERE `username` = ?');
	$stmt->bindParam(1, $username);
	$stmt->execute();
	return ($stmt->fetchColumn());
}				

function login(PDO $db, $username, $password) {
	$user_id = user_id_from_username($db, $username);
	$password = md5($password);
	
	$stmt = $db->prepare('SELECT COUNT(`user_id`) FROM `users` WHERE `username` = ? AND `password` = ?');
	$stmt->bindParam(1, $username);
	$stmt->bindParam(2, $password);
	$stmt->execute();
	if($stmt->fetchColumn() > 0) {
		return $user_id;
	} else { 
		return false;
	}
}

Thanks in advance!

[kicken]

 

		$fields = '`' . implode('`, `', $func_get_args) . '`';              //?? THIS LINE FORMATS THE FIELDS WITH BACKTICKS AND COMMAS
		
		$data = $db->prepare('SELECT ? FROM `users` WHERE `user_id` = ?');  //?? IM HAVING TROUBLE GETTING THIS TO BE AN OBJECT
		$data->bindParam(1, $fields);                                       //?? I THINK THE VARIABLE '?' AFTER SELECT IS MESSING IT UP
		$data->bindParam(2, $user_id);

 

You can't parameterize (bindParam/bindValue) things like field names, table names, SQL code, etc in a query. The only things you can parameterize are values (strings, numbers, NULL).

[junkomatic]

You can't parameterize (bindParam/bindValue) things like field names, table names, SQL code, etc in a query. The only things you can parameterize are values (strings, numbers, NULL).

In the tutorial's code they just put the variable $fields right after SELECT (see original post). Is there a way that this translates to my prepare?

 

Also, is the return with the ->fetchColumn() still correct when im trying to retrieve data in an array from multiple fields? i just sort of added that on because its working with the other similiar functions.

[kicken]

In the tutorial's code they just put the variable $fields right after SELECT (see original post). Is there a way that this translates to my prepare?

If you want to use dynamic field/table names, you still just concatenate the variables into the string, same as what was done before with the mysql extension:

$sql = 'SELECT '.$fields.' FROM `users` WHERE `user_id` = ?';
$data = $db->prepare($sql);

Also, is the return with the ->fetchColumn() still correct when im trying to retrieve data in an array from multiple fields? i just sort of added that on because its working with the other similiar functions.

->fetchColumn will fetch just a single column from the first row of returned data. If you want to fetch every column of the first row, use ->fetch(). If you want to return every column in every row, use ->fetchAll()

[junkomatic]

ok. im getting really close, but im still getting the same non object error.  this is what i changed it to:

function user_data($user_id) {
	$data = array();
	$user_id = (int)$user_id;
	
	$func_num_args = func_num_args();
	$func_get_args = func_get_args();
	
	if ($func_num_args > 1) {
		unset($func_get_args[0]);
		
		$fields = '`' . implode('`, `', $func_get_args) . '`';
		
		$sql = 'SELECT '.$fields.' FROM `users` WHERE `user_id` = ?';
		$data = $db->prepare($sql);
		$data->bindParam(1, $user_id);
		$data->execute();
		return ($data->fetch()); 
	} 
}

with the error on the prepare line still.

[junkomatic]

but when i add die($sql); and comment out the rest of the function, it shows me the select statement i want. when i plug it into phpmyadmin and replace the ? with a user_id, it sucessfully retrieves the information. also i know the function that defines $user_id is tested and works.

[mac_gyver]

you are not passing the $db instance of the pdo class into the function (like you are doing in the other functions that work), so of course there will be a not an object error.

[junkomatic]

ok, my function line is now:

function user_data(PDO $db, $user_id) {

 

and where im calling the function i changed it to:

if (logged_in() === true) {
$session_user_id = $_SESSION['user_id'];
$user_data = user_data($db, $session_user_id, 'user_id', 'username', 'password', 'first_name', 'last_name', 'email');
}

but thats not right, how should i be listing those feild names?

"PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column '4' in 'field list''
(4 is the user_id of the row im trying to fetch)

 

thank you so much for your help, ill try to make a donation.

[junkomatic]

how do i list the field names i want to retrieve as arguments of :

$user_data = user_data($session_user_id...

after ive changed it to:

$user_data = user_data($db, $session_user_id...

without it thinking that the $session_user_id is one of the fields?

[junkomatic]

i got it!

 

i just had to take yet another look at the code and i changed this part:

	if ($func_num_args > 1) {
unset($func_get_args[0]);

to:

if ($func_num_args > 2) {
unset($func_get_args[0]);
unset($func_get_args[1]);

so it unsets the first 2 arguments instead just the first one.

 

awesome.  can i condense these 2 lines into one line?

[kicken]

awesome.  can i condense these 2 lines into one line?

You can pass multiple variables to unset().

unset($func_get_args[0], $func_get_args[1]);

[Hall of Famer]

Your design looks flawed to begin with. When you have as complex domain logic as yours, Id say using a domain model and data mapper, following the OOP enterprise architecture design pattern. If all of your functions/methods accept the same argument $pdo and $username, it is a strong implication that refactoring is necessary.