Thread: PDO n00b

    #1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    3

    PDO n00b


    I learned PHP and MYSQL before PDO and MySQLi were the standard for querying mysql. I have never used PDO but am trying to learn. I got a connection, can select a few records, but I'm having trouble selecting this row for some reason. Maybe somebody knows why?

    I'm getting error:

    Fatal error: Call to a member function setFetchMode() on a non-object in /home/user/domains/example.com/public_html/index.php on line 154

    Here is my code:

    PHP Code:
    $sthComments $dbh->query("SELECT DATE_FORMAT(news_comments.comment_date,'%D %M %Y') AS commentDate, news_comments.news_key, news_comments.comment_name, news_comments.comment_email, news_comments.comment_comment FROM news_comments WHERE news_comments.news_key='$news_key'");  $sthComments->setFetchMode(PDO::FETCH_ASSOC); 
    The last code statement in that line, is line 154. It's odd though because right above this query I used the same syntax to query a seperate table and opened a while PDO fetch_assoc loop and that seems to work.

    Can anybody tell me what is going on?
    -- Success achieved from tribulation --
  2. #2
  3. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4124
    without consulting my own code, or the docs, (its late) I would guess that the setFetchMode method is actually part of the $dbh object; not the $sthComments object;

    However, I've never used it.

    I just do

    PHP Code:
    $sthComments $dbh->query($sql);

    $rs $sthComments->fetchAll(PDO::FETCH_ASSOC);  

    print_r($rs); 
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  4. #3
  5. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    The error says that $sthComments is not an object. Either northie's solution is correct or your ->query() function returns FALSE on error.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    the error message says it all: $sthComments isn't an object. It's "false" because there was an error in your query.

    The code generally has some issues:

    Don't insert variables into query strings (it hope it's at least escaped??). PDO has prepared statements, which are the very reason to use PDO. Otherwise you might as well stick to the old mysql_ functions.

    Don't prepend the table name to the columns when you only have one table. It's just useless and only clutters your query.
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    3
    Originally Posted by Jacques1
    Hi,

    Don't insert variables into query strings (it hope it's at least escaped??).
    I wasn't worried about the variable because it's simply a variable defined by another database query, not user input. Should I still escape the var? I thought that was the point of PDO, it had it's own magic quotes, escape, etc.. prevention built in?

    I don't think my query is wrong because I've tried it with just SELECT * FROM tablename and it throws the same error.
    -- Success achieved from tribulation --
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    That's the point of PDO, yes...assuming you actually bind the variables and don't just build a big string bare like you're doing. Bind them properly and you get the benefits.

    If you're using the results of one query in another query, you need to use a JOIN.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    3
    Originally Posted by ManiacDan
    That's the point of PDO, yes...assuming you actually bind the variables and don't just build a big string bare like you're doing. Bind them properly and you get the benefits.

    If you're using the results of one query in another query, you need to use a JOIN.
    Alright great, thanks for all of your insight. I realized I had to start cleaning things up sooner or later, I got it working by combining the query and finally gave up EquiJoin for Inner Join, and got rid of any variables in the query.

    This seemed to work:

    PHP Code:
        /////////////// Query database for news posts ///////////////
        
    $sthNews $dbh->query("SELECT n.news_key, 
            n.member_id, 
            DATE_FORMAT(news_date,'%D %M %Y') AS 
            newsDate, 
            news_title, 
            news_category, 
            news_post, 
            m.member_id,
            firstname, 
            lastname, 
            c.news_key, 
            comment_date, 
            comment_name, 
            comment_comment 
            FROM news n
            INNER JOIN members m 
            ON n.member_id = m.member_id
            INNER JOIN news_comments c
            ON c.news_key=n.news_key 
            ORDER BY n.news_date DESC LIMIT 4"
    );

        
    $sthNews->setFetchMode(PDO::FETCH_ASSOC); 
    Last edited by BitZoid; January 11th, 2013 at 01:46 PM. Reason: added code
    -- Success achieved from tribulation --
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    3
    Quick question. If I have more than one query on a page, should I just use the same ( $sth ) for every query. i.e. $sth = $dbh->query and after that query's results are used in my code, redefine $sth = null; and then I can reuse $sth for the next query?
    -- Success achieved from tribulation --
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Use descriptive variable names, which actually tell you the content (like $news_stmt, $member_stmt etc.). Generic or cryptic names massively reduce readability and can easily lead to mistakes. So choose sensible variable names.
  18. #10
  19. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    I wasn't worried about the variable because it's simply a variable defined by another database query, not user input.
    This wouldn't prevent the variable from having apostrophes in it unless you guarantee when you initially insert the value that it doesn't have them in it.

    Unless you have a particular reason for using different fetch modes for different queries, I recommend just setting the default fetch mode when you initialize your connection and then not messing around with it for every statement.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    What you also might wanna do is set the PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION in order to get rid of this stupid mixture of exceptions, return values and errors.

    You can do this in the fourth argument of the constructor:
    PHP Code:
    array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    This will throw an exception for every problem.
  22. #12
  23. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    3
    Originally Posted by Jacques1
    What you also might wanna do is set the PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION in order to get rid of this stupid mixture of exceptions, return values and errors.

    You can do this in the fourth argument of the constructor:
    PHP Code:
    array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    This will throw an exception for every problem.

    I did some more reading on PDO. I havn't had time to fully study and understand it yet but I made sure the connection PDO construct in the 4th parameter had an array that included a emulate_prepares = false attribute and error mode attribute set to exception mode and made sure to try and catch all errors or exceptions.

    E-oreo - The variable was just another value I inputted into the database that I was referencing from a previous query. So I know it was safe. Even so, I realized I cannot do things sloppily so I went ahead and did things the right way.

    I think its my first inner join I used outside of class, I'm use to equijoins that I learned 10 years ago.

    I still have tons more to read up on, concerning PDO, OOP, and some other things.
    Last edited by BitZoid; January 11th, 2013 at 08:22 PM.
    -- Success achieved from tribulation --
  24. #13
  25. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by BitZoid
    E-oreo - The variable was just another value I inputted into the database that I was referencing from a previous query. So I know it was safe.
    Apart from this concrete case, which you solved:

    Do not distinguish between "safe" and "unsafe" values. Believe me, this doesn't work.

    The value may be safe for this particular moment, but that could change anytime in the future ("Let me add this feature real quick ..."). And chances are you'll forget to add the escaping then. Switching between escaped and raw values generally is very error-prone. You can easily make mistakes and let an unsafe value slip through, which can completely compromise the security.

    You can avoid all this trouble by simply escaping every value, no matter how trivial and safe it is. Or even better: Use intelligent functionalities, which do the escaping for you. For example, many template engines have a default escaping method, so you don't need to call htmlentities() manually for every value.

IMN logo majestic logo threadwatch logo seochat tools logo