SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 50 of 50
  1. #26
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It spit out the array just fine.

    So what function is it that you can loop thru to echo row data, like normally I use mysql_fetch_array

  2. #27
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The array has 3 rows in it though

    There is no $row['governor']

    There is a $row[0]['governor'], $row[1]['governor'], $row[2]['governor']

    You need a loop to print multiple rows the same as if you used mysql_fetch_array

  3. #28
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay cool, like this then?


    while($row = $stmt->fetchAll(PDO::FETCH_ASSOC)) {

    echo $row[0]['color'] . " " . $row[0]['class'] . "<br />";

    }

    sorry I switched field names on ya.

  4. #29
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works but how do I loop through all of the rows, so I can display them all w/o having to enter [1] and [2] etc, do I need to use a for loop inside the while? Basically it should mimic this, right?

    while ($row = mysql_fetch_array($result)) {

    echo $row['color'];
    echo $row['size'];

    etc.

    }

  5. #30
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    2 Thread(s)
    Hi CatPDC,
    Okay, so as to #3, how do I use prepared statements in a more general dbClass where I don't know the table or LIMIT I am going to use ahead of time? How does that make this code re-usable if I can't pass these value into it?
    A little after the fact, but I too am building a CRUD group of classes and I have been working on how to make it more reusable. Here is an example how I did it for an Update class: http://www.sitepoint.com/forums/showpost.php?p=4543196&postcount=8
    ictus==""

  6. #31
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    foreach ($result as $row) {
      echo 
    $row['governor'] ...

    or

    PHP Code:
    for ($i 0$i count($result); $i++) {
      
    $row $result[$i];
      echo 
    $row['governor']...

    This is basic language syntax -- you might want to read through some PHP tutorials on how to use arrays

    PHP is really well documented on the php.net website

  7. #32
    SitePoint Evangelist AlienDev's Avatar
    Join Date
    Feb 2007
    Location
    UK
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by catpdc View Post
    PHP Code:
    $dbtype "mysql";
    $dbhost "localhost";
    $dbname "test";
    $dbuser "test";
    $dbpass "password";

    $dbh = new PDO("mysql:host=$dbhost;$dbname=$dbname"$dbuser$dbpass); 
    Off Topic:


    Are those variables really necessary? I bet not. Why waste lines and clutter your code with variable declarations, instead of writing:

    PHP Code:
    $dbh = new PDO('mysql:host=localhost; dbname=test''test, 'password); 
    Me on StackOverflow | Blog & personal website.

    I mostly use: PHP, Java, JavaScript, Android.

  8. #33
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    PHP Code:
    foreach ($result as $row) {
      echo 
    $row['governor'] ...

    or

    PHP Code:
    for ($i 0$i count($result); $i++) {
      
    $row $result[$i];
      echo 
    $row['governor']...

    This is basic language syntax -- you might want to read through some PHP tutorials on how to use arrays

    PHP is really well documented on the php.net website


    I know man, I just wasn't sure if there was a special function like mysql_fetch_array that let's you loop it with a while loop. Since this is in a Class i actually return all of the rows in an array and loop them in the object.

  9. #34
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    Hi CatPDC,


    A little after the fact, but I too am building a CRUD group of classes and I have been working on how to make it more reusable. Here is an example how I did it for an Update class: http://www.sitepoint.com/forums/showpost.php?p=4543196&postcount=8
    So you have a class with methods like "setTable" and "setFieldValue" that do one thing, set that value for the instance of the class?

    What does the if statement do?

    PHP Code:
    if ($stmt->execute()) {
               
    $this->pdo->commit();
               return 
    true;
            } else {
               
    $this->pdo->rollback();
            } 
    are you calling execute() in another method and this just checks whether that happend?

    I'm reading up on it here: http://www.php.net/manual/en/pdo.begintransaction.php

  10. #35
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by catpdc View Post
    So you have a class with methods like "setTable" and "setFieldValue" that do one thing, set that value for the instance of the class?

    What does the if statement do?

    PHP Code:
    if ($stmt->execute()) {
               
    $this->pdo->commit();
               return 
    true;
            } else {
               
    $this->pdo->rollback();
            } 
    are you calling execute() in another method and this just checks whether that happend?

    I'm reading up on it here: http://www.php.net/manual/en/pdo.begintransaction.php
    Hi CatPDC,

    The setter methods do simply set the properties of the class. Some people would do this in one array. Some do not like setters and choose to pass in variables; however to be clearer and to only set in the Crud classes the properties I need then I use the setters.

    The transaction, commit, and rollback do what you have read in the manual and so if anything during the insert or update goes wrong then it will rollback.

    The makeup of my CRUD class consists of interfaces like so:
    PHP Code:
    nterface IGet {
        function 
    getByNameOrId();
        function 
    getLike();
    }

    interface 
    IUniqueOrLast {
        function 
    getUnique();
        function 
    getLastInsertedId();
    }

    interface 
    ISave {
        function 
    save($container); //update or insert
        
    function saveNew(); //insert only

    Then I have a base abstract class that has the methods that I want to share through out the different classes. so for my save class I do
    PHP Code:
    class InsertUpdate extends Base implements ISave 
    One of my methods in this InsertUpdate class is update()
    PHP Code:
    function update() {
        
    //Update
            
    $sql 'UPDATE ' $this->table ' SET ' $this->field_value ' = :update_value  WHERE ' $this->column_name .' = :value';
             
    $stmt $this->pdo->prepare(trim($sql));
             
    $stmt->bindParam(':update_value'$this->update_value);
            
    $stmt->bindParam(':value'$this->value);

            
    //$this->pdo->beginTransaction();
            
    try {
                    if (
    $stmt->execute()) {
                        
    $this->pdo->commit();
                        return 
    true;
                    } else {
                        
    $this->pdo->rollback();
                    }
                } catch (
    Exception $e){
                    return 
    $e->getMessage(); //return exception
                
    }
        } 
    As earlier mentioned once the sql is prepared and the parameters are bound it tries to execute. If it goes well then the update is committed and if not it rolls back. If an exception occurs it will bubble up and get caught by my error handling class.

    A more complete view of how this works is:
    PHP Code:
    function testUpdate() {

            
    // Wiring...
            
    $container = new bucket_Container(new DbFactory);
            
    $insertUpdate $container->get('InsertUpdate');

            
    //Insert record to Update
            
    $insertUpdate->setTable('users');
            
    $expression = array('insert');
            
    $insertUpdate->setSelectExpression($expression);
            
    $insertUpdate->setColName('uid_number,  uid , display_name , firstName, lastName, password, user_info, signature, has_email');
            
    $insertUpdate->setValue(
            
    "(10004, btest, Bee Test, Bee, Test, c154da9694c9f27ccd17f112f4472cd32271845a3a8d279ff400a6e4466fcc8f, 0, 0, 0)"
            
    );

            
    $output =$insertUpdate->save($insertUpdate); // Insert

            
    if(!empty($output)) { // if it does not exist.
                
    $this->assertEqual(True$output); //Insert
            
    }

            
    //Update
            
    $insertUpdate->setTable("users");
            
    $insertUpdate->setFieldValue("firstName");
            
    $insertUpdate->setUpdateValue("Be");
            
    $insertUpdate->setColName('uid_number');
            
    $insertUpdate->setValue("10004");

            
    $insertUpdate->update($insertUpdate);
            if(!empty(
    $output)) {
                
    $this->assertEqual(True$output); 
            }

            
    $insertUpdate->setTable("users");
            
    $insertUpdate->setFieldValue("display_name");
            
    $insertUpdate->setUpdateValue("Be Test");
            
    $insertUpdate->setColName('uid_number');
            
    $insertUpdate->setValue("10004");

            
    $insertUpdate->update($insertUpdate); //Update
            
    if(!empty($output)) { 
                
    $this->assertEqual(True$output); 
            }
        } 
    Hope this helps.
    Steve
    ictus==""

  11. #36
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome, when I get done with my ditch digging day job this weekend I have some work to do!

  12. #37
    SitePoint Guru
    Join Date
    Oct 2006
    Location
    Queensland, Australia
    Posts
    852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi catpdc,

    First of all, it's important that you know that "PDO" and "prepared statements" are two seperate things. PDO is a class/interface for performing operations against a database, where as prepared statements are a feature of MySQL which is supported by both the PDO and the mysqli PHP extensions.

    There are two reasons why you may use prepared statements. The first is that by design, prepared statements prevent SQL injection. They do this simply by only allowing "values" to be passed when executing a prepared statement. Because of this, any data sent to a prepared statement will be treated as a value, hence any special MySQL characters or keywords are not interpreted as MySQL code, but are merely treated as textual data.

    The second reason you may use prepared statement is for performance reasons. Depending on the type of application and how's it's been designed, prepared statements can either increase or decrease performance. Unlike a normal SQL query, prepared statements require two separate requests to the server. The first request sends the prepared statement (e.g. "SELECT title FROM table WHERE author = ?"), while all subsequent requests send a reference to that prepared statement along with the accompanying data. If you're application rarely re-uses prepared statements, then it's likely they will decrease performance. On the other hand, if your application executes a set queries many times, but with different "values", then prepared statements can increase performance.

    I hope that's slightly improved your understanding of PDO and prepared statements. Prepared statements are a new concept, so like any new concept, it'll take a little while to properly grasp and understand; like when you go from procedural code to OOP, although it shouldn't take near as long to fully grasp.

  13. #38
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Wardrop View Post
    Hi catpdc,

    First of all, it's important that you know that "PDO" and "prepared statements" are two seperate things. PDO is a class/interface for performing operations against a database, where as prepared statements are a feature of MySQL which is supported by both the PDO and the mysqli PHP extensions.

    There are two reasons why you may use prepared statements. The first is that by design, prepared statements prevent SQL injection. They do this simply by only allowing "values" to be passed when executing a prepared statement. Because of this, any data sent to a prepared statement will be treated as a value, hence any special MySQL characters or keywords are not interpreted as MySQL code, but are merely treated as textual data.

    The second reason you may use prepared statement is for performance reasons. Depending on the type of application and how's it's been designed, prepared statements can either increase or decrease performance. Unlike a normal SQL query, prepared statements require two separate requests to the server. The first request sends the prepared statement (e.g. "SELECT title FROM table WHERE author = ?"), while all subsequent requests send a reference to that prepared statement along with the accompanying data. If you're application rarely re-uses prepared statements, then it's likely they will decrease performance. On the other hand, if your application executes a set queries many times, but with different "values", then prepared statements can increase performance.

    I hope that's slightly improved your understanding of PDO and prepared statements. Prepared statements are a new concept, so like any new concept, it'll take a little while to properly grasp and understand; like when you go from procedural code to OOP, although it shouldn't take near as long to fully grasp.
    Yes, that helps me understand more for sure. The site I am building is a classifieds site, so it repeats the same query over and over every time someone clicks on an item for sale, exactly like craigslist. The only things that will change are category and post ID... so prepared statements should make that faster because it is calling the same query over and over simply by reference, and sending an array of ? dynamic values with it, to avoid resending the static SELECT code, correct?

  14. #39
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I have prepared statements figured out in the basic sense.

    In my normal SQL Queries for pagination I use a LIMIT with an offset and a # of rows I want per page, like so:

    $limit = $offset . ', ' . $rowsperpage;

    The query might end up like LIMIT 30, 15 on page 3.

    When I use this with prepared statements though, I get zero results. If I set LIMIT to 15 it works fine but "0, 15" doesn't work. Here's the code I am using:

    $this->_queryString .= " LIMIT ?";
    $stmt = $dbh->prepare($this->_queryString);
    $stmt->bindParam(3, $this->_limit, PDO::PARAM_STR);

    If I set it to PARAM_INT and pass it one value, say 15, I get 15 results. If I set it to string and create $limit like "0, 15" I get nothing... ideas?

    I tried this too with no luck:

    $this->_queryString .= " LIMIT ?, ?";
    $stmt->bindParam(3, $this->_limit1, PDO::PARAM_INT); // $_limit1 = 0;
    $stmt->bindParam(4, $this->_limit2, PDO::PARAM_INT); // $_limit2 = 15;


    *edit*

    It looks i can use OFFSET, forgot about that one.

  15. #40
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    LIMIT ?, ?

    Remember, bind values, not expressions. You have to pass in the complete query you want to execute, not a partial query you're going to append more syntax to.

    I've verified this works, same example I gave on the last page but with an offset too, so you did something wrong with your code.

    PHP Code:
    $stmt $dbh->prepare("SELECT * FROM users LIMIT ?, ?");
    $stmt->bindParam(1$offsetPDO::PARAM_INT);
    $stmt->bindParam(2$limitPDO::PARAM_INT);
    $stmt->execute(); 

  16. #41
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I think I had my bindings for offset and limit in the wrong order, so limit was set to 0. Got it working, thanks.

    The partial query was building up to the full string, i.e.

    PHP Code:
    $this->_queryString "SELECT $this->_fields FROM $this->_table";
            
        if (
    $this->_where_field != "") {
            
    $this->_queryString .= " WHERE $this->_where_field = ?";
        }
            
        if (
    $this->_orderby != "") {
            
    $this->_queryString .= " ORDER BY ?";
        }
            
        if (
    $this->_limit != "") {
            
    $this->_queryString .= " LIMIT ?, ?";
        }

    $stmt $dbh->prepare($this->_queryString);
            
        
    $stmt->bindParam(1$this->_where_valuePDO::PARAM_INT);
        
    $stmt->bindParam(2$this->_orderbyPDO::PARAM_STR);
        
    $stmt->bindParam(3$this->_offsetPDO::PARAM_INT);
        
    $stmt->bindParam(4$this->_limitPDO::PARAM_INT); 
    That way the class is more flexible and can work with queries that don't have a LIMIT etc. I am going to pass it all in as an array later, but wanted to get it working in general, and it seems to. Is that okay to do, so use conditionals to build the query string like that?

  17. #42
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, that's fine. I just meant that when you call prepare(), its argument needs to be the full query. You can't use parameter binding to add pieces of SQL syntax ("#, #") after the fact.

  18. #43
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help so far. I'm working a full time construction job as well as another 7-8 hours a day on this project, plus raising a family, so my head is kind of mush sometimes. I really want it done right though, no more security holes and hacked together code, so I am stepping through it and i refuse to just copy/paste code, i want to understand it and write it myself.

    When I create a new PDO does it need to be inside the method I called prepare() in or can I make a dbConnect method and use $this->dbh?

  19. #44
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by kyberfabrikken View Post
    I would say that PDO is more of a standard than mysqli is, so I would suggest that you use this.
    Funnily I had to give up with PDO when using stored procs. Error messages bore no relation to the actual errors. Buggy as hell in this department. Switched to MySQLi and all was fine.

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  20. #45
    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 lastcraft View Post
    Funnily I had to give up with PDO when using stored procs. Error messages bore no relation to the actual errors. Buggy as hell in this department. Switched to MySQLi and all was fine.
    Sounds scary. I've never used stored procs much, but I did run into some memory leaks at some point, which were related to PDO.

    Buggy or not, PDO seems to be more widely used in the community.

  21. #46
    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 catpdc View Post
    When I create a new PDO does it need to be inside the method I called prepare() in or can I make a dbConnect method and use $this->dbh?
    You can - and indeed you should - reuse the same connection object (Eg. the PDO instance) throughout the application. Either pass it in to the object/methods that need it or make it global. I recommend the former.

  22. #47
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    Buggy or not, PDO seems to be more widely used in the community.
    I'd agree.

    MySQLI has a shockingly horrid API in some places, like mysqli_stmt_bind_param(), which forces you to use call_user_func_array() in certain situations.

  23. #48
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren View Post
    I'd agree.

    MySQLI has a shockingly horrid API in some places, like mysqli_stmt_bind_param(), which forces you to use call_user_func_array() in certain situations.
    That's not fair. MySQLi has a full OOP API which is perfectly functional in all cases. What you're talking about are incredibly old interfaces, which I don't think most sane people have used in a long time.

  24. #49
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SituationSoap View Post
    That's not fair. MySQLi has a full OOP API which is perfectly functional in all cases. What you're talking about are incredibly old interfaces, which I don't think most sane people have used in a long time.
    The OO interface shares the same lunacy as the functional interface.

    Having a function take variable number of arguments in this instance is poor.

    The MySQLI_Stmt::bind_param() & MySQLI_Stmt::bind_result() methods are really ugly to use and have to resort to call_user_func_array(), as you would with the functional equivalents.

    Whereas PDO, can bind parameters and columns individually.

  25. #50
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren View Post
    The OO interface shares the same lunacy as the functional interface.

    Having a function take variable number of arguments in this instance is poor.

    The MySQLI_Stmt::bind_param() & MySQLI_Stmt::bind_result() methods are really ugly to use and have to resort to call_user_func_array(), as you would with the functional equivalents.

    Whereas PDO, can bind parameters and columns individually.
    Ah, I understand what you're getting at.

    That's a legitimate complaint, and one I don't have an answer for. I would guess that the decision was made in the hope that you could bring the API closer to a "Do what I mean" state. Personally, if I'm writing a public API, I prefer to make things a bit funkier on the back end if it makes it easier on the developer using the API (assuming it doesn't introduce security or performance issues).


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
  •