PDO not returning record

[andrewsmd]

So I'm trying to create a function to execute PDO statements for me, per your guys' suggestion to switch from mysql. The issue is, when I add more than one param, I get no results back. Here is my function

//this function takes in a sql statement and an array of parameter names and values
//and returns the 2d array of the mysql data
function fetchRows($sql, $paramArr){

$pdo = connect();
$statement = $pdo->prepare($sql);

//if there's anything in the array loop through and bind the params
foreach ($paramArr as $key=>$i)
{
echo($key . " => " . $i ." test<br />");

$statement->bindParam($key, $i);
}


$statement->execute();

//get's 1 record
//$row = $statement->fetch(PDO::FETCH_ASSOC);

//get's all
return($statement->fetchAll());

}//getData

Now here is the code I'm testing, you can see running either of the two statements with one parameter works, but when I try to add both, it doesn't.


//this one doesn't work
$params = array(":type"=>"2", ":name"=>"testuser1");
$sql = "SELECT * FROM users WHERE userType=:type AND username=:name;";

//this one works
//$params = array(":name"=>"testuser1");
//$sql = "SELECT * FROM users WHERE username=:name;";

//this one works too
//$params = array(":type"=>"2");
//$sql = "SELECT * FROM users WHERE userType=:type;";

$arr = fetchRows($sql, $params);
var_dump($arr);

[penders]

I think you need to call bindValue() rather than bindParam(). You want to bind the actual values you have passed to the function, rather than binding PHP variables ($i) - which are getting overwritten by your loop.

[andrewsmd]

I had already tried bindValue as well. it gives me the exact same result. The vars are supposed to get over written in my loop. I'm actually outputting my array at this point to see what's going on and it is correct. Technically it should be this

:type => 2
:name => testuser1

so binding the param :type to 2 and :name to testuser1

[andrewsmd]

So I think this has something to do with my for loop, but I don't understand what. Check out this code. i marked what one works and what one doesn't

try {

$pdo = connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //Throw PDOException.


$query = "SELECT * FROM users WHERE userType=:type AND username=:name;";
$stmt = $pdo->prepare($query);

//***going this route works

$name = 'testuser1';
$type = 2;
$stmt->bindParam(':name', $name);
$stmt->bindParam(':type' , $type);


//***this one does not work

//$params = array(":type"=>2, ":name"=>"testuser1");
//foreach($params as $key=>$i){
// $stmt->bindParam($key, $i);
//}//for each


$stmt->execute();


$result = $stmt->fetchAll();
var_dump($result);
foreach($result as $row) {
echo($row['username']."\n");
}


$dbh = null;

}
catch(PDOException $e) { echo($e->getMessage()); }

[penders]

Exactly, which is why bindParam() will not work in your for loop, you would need to use bindValue() here (although you say you already tried this, which is admittedly rather confusing at the mo, unless something else has changed in the code?).

bindParam() won't work in the for loop, since it literally binds the PHP variable $i to the parameter... you end up binding both :type and :name to "testuser1" which is the final value of $i in the loop. (The second argument to bindParam() is passed by reference, not value, as it is with bindValue())

[andrewsmd]

I got it working. This function works well because you can pass in null for the param array and execute statements with no conditions, as well as having as many paramters as you'd like, just pass in a proper array!

//this function takes in a sql statement and an array of parameter names and values
//and returns the 2d array of the mysql data
function fetchRows($sql, $paramArr){
$retVal="";
try {

$pdo = connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //Throw PDOException.


$stmt = $pdo->prepare($sql);

if($paramArr != null){
$stmt->execute($paramArr);
}//if $paramArr
else{
$stmt->execute();
}


$result = $stmt->fetchAll();

$retVal = $result;


$dbh = null;

}
catch(PDOException $e)
{
$retVal = ($e->getMessage());
}//try

return $retVal;

}//fetchRows

[andrewsmd]

I got it working. This function works well because you can pass in null for the param array and execute statements with no conditions, as well as having as many paramters as you'd like, just pass in a proper array!

//this function takes in a sql statement and an array of parameter names and values
//and returns the 2d array of the mysql data
function fetchRows($sql, $paramArr){
$retVal="";
try {

$pdo = connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //Throw PDOException.


$stmt = $pdo->prepare($sql);

if($paramArr != null){
$stmt->execute($paramArr);
}//if $paramArr
else{
$stmt->execute();
}


$result = $stmt->fetchAll();

$retVal = $result;


$dbh = null;

}
catch(PDOException $e)
{
$retVal = ($e->getMessage());
}//try

return $retVal;

}//fetchRows

[penders]

Good stuff. You could make $paramArr entirely optional, by specifying a default parameter, without having to explicitly pass NULL - if that is required.

function fetchRows($sql, $paramArr=null) { ... }

You can then call fetchRows() with just 1 argument.

[andrewsmd]

Well for that matter, you can just not pass in anything and make sure warnings are turned off and it'll still work. I never was a fan of not passing in every var into functions or defining the same function and overloading them. I always thought it made things too difficult to debug. I.e. if someone is looking at my code and they see fetchRows($sql) and then fetchRows($sql, $arr) they'd wonder what happens and have to look at the function. If you're always passing in $arr or null, then it's pretty obvious that you're passing nothing with null. Just a personal preference though.

[penders]

Well for that matter, you can just not pass in anything and make sure warnings are turned off and it'll still work.

...and get shot by the code police! (Needless to say, that would be extremely bad practise.)

[andrewsmd]

Bad practice, absolutely. If bad practices meant code never went into production, we'd probably have to nix about 90% of the systems out there!