SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PDO and prepared statements

    I'm working on improving my database query code by using prepared statements with PDO. As this is a new realm for me, I was hoping someone could take a look at my snippets of code and let me know if there is anything missing or could be improved. It's the use of the for loop to echo the results that doesn't 'feel' right.

    Any suggestions would be appreciated.

    PHP Code:
    $dbh = new PDO('mysql:host=localhost;dbname=thedatabase'DBUSERDBPASSWORD);
            
    $query "
            INSERT INTO payment (relfileID, deposit1, chqnum1, deposit2, chqnum2, confirmation, confirmchqnum)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            "
    ;
            
    $sth $dbh->prepare($query);
            
    $sth->bindParam(1$ptfidPDO::PARAM_INT);
            
    $sth->bindParam(2$deposit1PDO::PARAM_INT6);
            
    $sth->bindParam(3$chqnum1PDO::PARAM_STR15);
            
    $sth->bindParam(4$deposit2PDO::PARAM_INT6);
            
    $sth->bindParam(5$chqnum2PDO::PARAM_STR15);
            
    $sth->bindParam(6$confirmationPDO::PARAM_INT5);
            
    $sth->bindParam(7$confirmchqnumPDO::PARAM_STR15);
            
    $sth->execute();
    $dbh NULL
    PHP Code:
    <?php
    $dbh 
    = new PDO('mysql:host=localhost;dbname=thedatabase'DBUSERDBPASSWORD);
        
    $query "
        SELECT * FROM project WHERE relprojectstatusID = ?
        "
    ;
        
    $sth $dbh->prepare($query);
        
    $sth->bindParam(1$psidPDO::PARAM_INT2);
        
    $sth->execute();
        
    $projectlist $sth->fetchAll(PDO::FETCH_ASSOC);

        for (
    $i 0$i count($projectlist); $i++) {
    ?>
        <OPTION value="<?php echo $projectlist[$i]['projectID']; ?>"<?php echo $chkval?>><?php echo $projectlist[$i]['projectname']; ?></OPTION>
    <?php
        
    }
    $dbh NULL;
    ?>

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    error handling and I would use named placeholders instead of question marks because they are more concrete. Generally, I do something like this:

    PHP Code:
    $sql 'SELECT id FROM blahs WHERE id = :id ';
    $id 1;
    if(
    $stmt $pdo->prepare($sql)) {
      
    // bind data
      
    $stmt->bindParam(':id',$id,PDO::PARAM_INT);

      if(
    $stmt->execute()) {
         
    // collect result (select) or return true (insert,update,delete)
      
    } else {
        
    // throw some of exception
      
    }
    } else {
      
    // throw some type of exception

    I only recommend using named placeholders because the bind data isn't dynamic so you might as well. When you have dynamic data that can become a problem though.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As for the for loop, you can use foreach
    PHP Code:
    foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) {



  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    $rows = array();
    while(
    $row=$stmt->fetch(PDO::FETCH_ASSOC)) {
      
    $rows[] = $row;


  5. #5
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    As for the for loop, you can use foreach
    PHP Code:
    foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) {


    Actually, you can just use:

    PHP Code:
    foreach ($sth as $row) {


    .. since a PdoStatement implements Iterator.

  6. #6
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I too recommend using named parameters, and never use bindParam - Either use bindValue or pass an array to the execute method (I prefer the latter). bindParam has reference-semantics, so it can make your application really hard to understand and introduce subtle bugs.

    PHP Code:
    $dbh = new PDO('mysql:host=localhost;dbname=thedatabase'DBUSERDBPASSWORD);
    $query "INSERT INTO payment (relfileID, deposit1, chqnum1, deposit2, chqnum2, confirmation, confirmchqnum) VALUES (:relfileID, :deposit1, :chqnum1, :deposit2, :chqnum2, :confirmation, :confirmchqnum)";
    $sth $dbh->prepare($query);
    $sth->execute(
      array(
        
    ':relfileID' => $ptfid,
        
    ':deposit1' => $deposit1,
        
    ':chqnum1' => $chqnum1,
        
    ':deposit2' => $deposit2,
        
    ':chqnum2' => $chqnum2,
        
    ':confirmation' => $confirmation,
        
    ':confirmchqnum' => $confirmchqnum)); 
    As for error handling, I'd recommend that you turn on exception-mode for your connection. When that is enabled, PDO will raise an exception if a query fails; Makes it a lot easier to detect failed queries without having to clutter your code with error-handlers all over:

    PHP Code:
    $dbh->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION); 

  7. #7
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks everyone for the input!

    I've made the changes using
    PHP Code:
    foreach ($sth as $row) {


    and passing the array to the execute method, as well as the error handling.

  8. #8
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a question about error handling. In the manual it says:

    Calling PDO:: prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.
    To what extent does it help to prevent SQL injection attacks?

    In using the old mysql functions, using mysql_real_escape_string() was needed. Does this mean, it's not anymore?

    Am I behind?
    No, I REALLY dislike having to use Joomla.

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  10. #10
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crowden View Post
    In using the old mysql functions, using mysql_real_escape_string() was needed. Does this mean, it's not anymore?
    Yes, you don't have to escape anything with parameterised queries.
    Quote Originally Posted by crowden View Post
    Am I behind?
    Yes, parameterised queries are easier to work with, safer and in some cases they perform better (reuse of prepared statements).

  11. #11
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    very well. thank you both.
    No, I REALLY dislike having to use Joomla.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •