PHP PDO Database class parameter passing

Question

I would like to setup an array with multiple parameters in it, pass it to my database class. Currently I can only send one parameter, but generally my sql queries have multiple parameters.

Sample database method:

public function fetchQuery($sql,$params,$conn){
    try{
        $queryparams=array($params);
        $x=$conn->prepare($sql);
        $x->execute($queryparams);
        $rArray = $x->fetchAll(PDO::FETCH_ASSOC);
        return $rArray;
    }catch (Exception $e){
        die( print_r( $e->getMessage() ) );
    }
}

Call to database method:

        $params=$vendID;
        $sql = "SELECT Email from Contact WHERE VendID = ?";

 try{
          $emails=$db->fetchQuery($sql,$params,$conn);
          $eCount = count($emails);
           if($eCount>0){
                foreach($emails as $row){
                    $eArray[]=$row['Email'];
                }
            }else{
                echo "No email addresses associated with this vendor";
            }
        }
        catch(Exception $e){
            die( print_r( $e->getMessage() ) );
        }

Now say my sql query was like: "Select * from Contact where CustID=? and Expired=?" How would I build an array and pass the into my function?

Every time I try, I get an array to string conversion error.

Thanks

meda · Accepted Answer · 2014-04-21 14:36:17Z

3

Now say my sql query was like: "Select * from Contact where CustID=? and Expired=?" How would I build an array and pass the into my function?

I would remove the $queryparams=array($params); part of your code, and always pass the function an array even if it has only one elements.

$params= array($custID);
$sql = "SELECT Email from Contact WHERE VendID = ?";

try{
    $emails=$db->fetchQuery($sql,$params,$conn);

mutiple

$params= array($vendID, $expired);
$sql = "Select * from Contact where CustID=? and Expired=?";

try{
    $emails=$db->fetchQuery($sql,$params,$conn);

I personaly prefer the named parameter, where I can use the associative arrays:

$sql = "Select * from Contact where CustID= :custId and Expired= :expired";

$params= array(':custId'=>$custID, 
               ':expired'=>$expired
);

edited Apr 21, 2014 at 14:36

answered Apr 21, 2014 at 14:17

meda

45.5k14 gold badges96 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

meda Over a year ago

please explain the downvote, Im getting tired of this.. if you see something wrong SAY something!!!

Michael Butler · Accepted Answer · 2014-04-21 14:08:52Z

2

In your fetchQuery method you can check if $params is already an array or not.

<?php
if (is_array($params)) {
    $queryParams = $params;
}
else {
    $queryParams = array($params);
}

answered Apr 21, 2014 at 14:08

Michael Butler

6,3295 gold badges42 silver badges49 bronze badges

1 Comment

Michael Butler Over a year ago

Please explain how I can improve my answer, or what is unclear, instead of (or in addition to) downvoting

Your Common Sense · Accepted Answer · 2014-04-21 14:48:50Z

0

You don't actually need this function. it saves you typing of just a couple words.
Yet the same time it is limiting you in using PDO. Look at your code with raw pdo

$stmt = $con->prepare("SELECT Email from Contact WHERE VendID = ?");
$stmt->execute([$vendID]);
$emails = $stmt->fetchAll(PDO::FETCH_COLUMN);
if(!$emails){
    echo "No email addresses associated with this vendor";
}

it's 3 times shorter than with your function.
PDO already is a wrapper by itself. One need VERY strong reasons and too much experience to be able to extend it proficiently.

answered Apr 21, 2014 at 14:48

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Collectives™ on Stack Overflow

PHP PDO Database class parameter passing

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related