php PDO query with several parameters to bind

Question

I am currently learning PHP concepts and just can not figure out how to properly bind a query. According to PDO manual, queries are bound only if they are of type string, int, float, etc. My first question is: "Do I need to bind date?" If yes, which parameters use. Otherwise, I need to bind some attributes of a relation to insert and what to do with remaining which are not of these types mentioned above? Here is my code:

public function addCustomer($fname, $lname, $email, $dob, $hashedPwd, $hash)
{
    $customer = new Customer($fname, $lname, $email, $dob, $hashedPwd);

    $sql = $this->pdo->prepare("INSERT INTO customer(fname, lname, email, date_of_birth, password, hash, active)"
        . " VALUES(:fname, :lname, :date_of_birth, :email, :password, :hash, :active)");

    $sql->bindValue(':fname', $customer->getFname(), PDO::PARAM_STR);
    $sql->bindValue(':lname', $customer->getLname(), PDO::PARAM_STR);
    $sql->bindValue(':email', $customer->getEmail(), PDO::PARAM_STR);
    $sql->bindValue(':password', $customer->getPassword(), PDO::PARAM_STR);
    $sql->bindValue(':hash', $hash, PDO::PARAM_STR);
    $sql->bindValue(':active', 0, PDO::PARAM_INT);

    try {
        $sql->execute(['date_of_birth' => $dob]);
        echo "SUCCESS" . "<br>";
    }catch (PDOException $e) {
        $e->getMessage();
    }


}

just treat it as a string, and just stick with one way, either load them all up inside the `->execute()` or use `->bindValue`, don't combine both — Kevin, May 28 '18 at 03:13
@Ghost just changed to this: '$sql->bindValue(':date_of_birth', $customer->getDob());$sql->execute();' and it worked. However, according to manual, data types like date should not be bound. So, what's the proper way? — Sergey Kim, May 28 '18 at 04:12
@SergeyKim Where did you see that dates should not be bound? They should not be bound as integers but binding them should be fine. Maybe this thread would be useful https://stackoverflow.com/questions/2374631/pdoparam-for-dates — user3783243, May 28 '18 at 05:27

score 0 · Accepted Answer · answered May 28 '18 at 04:04

I do it by passing an array, and using question marks as my place holders

I also recommend working up a set of generic functions or a db handler class that you can simply pass a query and array (or maybe a 2nd array with db connection info) and get back an array with a true or false at element 0 and either an error message at element 1 or data from element 1 on (in the case ofa select).

Here's a snippet of mine, modified to take out all the other handling, but it shows how I paramaterize and prepare the query.

    <?php
    $query="insert into tablename(first_name,last_name) values(?,?)";
    $array=array("John","Doe");

    $dbconn = new PDO('mysql:host=' . $hostname . ';port=' . $dbPort . ';dbname=' . $dbName . ';charset=utf8', $username, $password, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
    $dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $result = $dbconn->prepare($query);
    $result->execute($arr);
    if (!$result) {
        $retVal[] = false;
        $retVal[] = "some sort of error on execute";
        $retVal[] = $dbconn->errorInfo();
        return;
    }
    $retVal[] = true;
    $retVal[] = $dbconn->lastInsertId();

    return;

    ?>

php PDO query with several parameters to bind

1 Answers1