homepage Welcome to WebmasterWorld Guest from 54.167.75.155
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PDO not returning record
andrewsmd




msg:4612045
 2:59 pm on Sep 23, 2013 (gmt 0)

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




msg:4612059
 4:01 pm on Sep 23, 2013 (gmt 0)

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




msg:4612077
 4:25 pm on Sep 23, 2013 (gmt 0)

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




msg:4612087
 4:58 pm on Sep 23, 2013 (gmt 0)

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




msg:4612095
 5:22 pm on Sep 23, 2013 (gmt 0)

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




msg:4612099
 5:25 pm on Sep 23, 2013 (gmt 0)

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




msg:4612100
 5:25 pm on Sep 23, 2013 (gmt 0)

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




msg:4612109
 5:53 pm on Sep 23, 2013 (gmt 0)

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




msg:4612281
 1:04 pm on Sep 24, 2013 (gmt 0)

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




msg:4612332
 4:28 pm on Sep 24, 2013 (gmt 0)

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




msg:4612345
 5:47 pm on Sep 24, 2013 (gmt 0)

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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved