www.webdeveloper.com
Results 1 to 8 of 8

Thread: PDO prepared statement

  1. #1
    Join Date
    Nov 2009
    Posts
    97

    PDO prepared statement

    Hello everyone,

    I am trying to use PDO for my new projects and I can't seem to understand or at least find a good example on prepared statements, I have an update query that works fine but I know I'm not using it right for the data sanitation feature that PDO offers, this is my code:

    PHP Code:
    $data $conn->query("SELECT * FROM adminUsers INNER JOIN permissions ON adminUsers.adminUserID=permissions.adminUserID");
    while (
    $row $data->fetch(PDO::FETCH_ASSOC)){
        
    $sql "UPDATE adminUsers, permissions SET 
            adminUsers.name = '"
    .$_POST['name']."', 
            adminUsers.lastName = '"
    .$_POST['lastName']."', 
            adminUsers.email = '"
    .$_POST['email']."', 
            permissions.superAdmin = '"
    .$_POST['superAdmin']."' , 
            permissions.editAdminUsers = '"
    .$_POST['editAdminUsers']."' , 
            permissions.deleteAdminUsers = '"
    .$_POST['deleteAdminUsers']."' , 
            permissions.editUsers = '"
    .$_POST['editUsers']."' , 
            permissions.deleteUsers = '"
    .$_POST['deleteUsers']."' 
        WHERE adminUsers.adminUserID = '"
    .$_POST['id']."' && permissions.adminUserID = '".$_POST['id']."'";
        
    $sql $conn->prepare($sql);
        
    $sql->execute(array(':name'=>$row['name'],
                            
    ':lastName'=>$row['lastName'],
                            
    ':email'=>$row['email'],
                            
    ':superAdmin'=>$row['superAdmin'],
                            
    ':editAdminUsers'=>$row['editAdminUsers'],
                            
    ':deleteAdminUsers'=>$row['deleteAdminUsers'],
                            
    ':editUsers'=>$row['editUsers'],
                            
    ':deleteUsers'=>$row['deleteUsers']));


    Could someone please tell me what is the right way to use PDO in this case?

    Thanks a lot for any help provided.
    Last edited by NogDog; 02-01-2013 at 09:37 PM. Reason: changed [code] tags to [php] tags

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,247
    You want to put your ":text" place-holders in the query string itself where you currently have $_POST values. Then when you "bind" values to those parameters, PDO will take care of the sanitation for you. Much simplified:
    PHP Code:
    $sql "
        UPDATE table_name
        SET some_field = :input_value
        WHERE another_field = :id_value
    "
    ;
    $stmt $pdo->prepare($sql);
    $stmt->execute(
        array(
            
    ':input_value' => $_POST['input_value'],
            
    ':id_value'    => $_POST['id_value']
        )
    ); 
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Nov 2009
    Posts
    97
    Thanks for the reply Nogdog,

    I made the changes and I passed a value like "O'reilly" to the DB and it didn't really convert it to anything, it went into the table as "O'reilly", am I missing something here?

    Thanks.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,247
    Quote Originally Posted by Sub_Seven View Post
    Thanks for the reply Nogdog,

    I made the changes and I passed a value like "O'reilly" to the DB and it didn't really convert it to anything, it went into the table as "O'reilly", am I missing something here?

    Thanks.
    That means it worked.

    You don't want to convert it to anything in the actual data. Whatever escaping happens is only for the actual query string so that the MySQL parser knows to treat it as a literal character, much like you might us a "\" to escape a literal quote in a PHP echo:
    PHP Code:
    echo "\"This is a test,\" he said"
    Which will output:
    Code:
    "This is a test," he said
    ...without those backslashes.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Nov 2009
    Posts
    97
    Oh cool!

    May I ask one more question now that we're at it?

    As you can see in my original code I am updating two tables from within the same query, no matter what I do, this $count = $sql->rowCount(); will always return 0 (zero), is this the best method to get the number of affected rows here?

    Thanks again for the help

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,247
    As far as I know, that should work okay for an insert or update query in MySQL (but it may not work for select queries). If it's not working for you, we may need to see exactly where and how are are calling it.

    PS: You may want to check if it's actually returning 0 or false:
    PHP Code:
    $count $sql->rowCount();
    if(
    $count === false) {   // note use of "===" operator, not "==", to differentiate 0 and FALSE
       //some debug code here

    Last edited by NogDog; 02-04-2013 at 03:48 PM.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Nov 2009
    Posts
    97
    It returns 0 all the time, it's not false (I just checked to be sure).

    Let me give you all the code to see if you can spot where the problem is:

    PHP Code:
    if($_POST['submit'] == 'Edit User'){
        
    $data $conn->query("SELECT * FROM adminUsers INNER JOIN permissions ON adminUsers.adminUserID=permissions.adminUserID");
        while (
    $row $data->fetch(PDO::FETCH_ASSOC)){
            
    $sql "UPDATE adminUsers, permissions SET 
                adminUsers.name = :name, 
                adminUsers.lastName = :lastName, 
                adminUsers.email = :email, 
                permissions.superAdmin = :superAdmin, 
                permissions.editAdminUsers = :editAdminUsers, 
                permissions.deleteAdminUsers = :deleteAdminUsers, 
                permissions.editUsers = :editUsers, 
                permissions.deleteUsers = :deleteUsers 
            WHERE adminUsers.adminUserID = :id && permissions.adminUserID = :id"
    ;
            
    $sql $conn->prepare($sql);
            
    $sql->execute(array(':name'=>$_POST['name'],
                                
    ':lastName'=>$_POST['lastName'],
                                
    ':email'=>$_POST['email'],
                                
    ':superAdmin'=>$_POST['superAdmin'],
                                
    ':editAdminUsers'=>$_POST['editAdminUsers'],
                                
    ':deleteAdminUsers'=>$_POST['deleteAdminUsers'],
                                
    ':editUsers'=>$_POST['editUsers'],
                                
    ':deleteUsers'=>$_POST['deleteUsers'],
                                
    ':id'=>$_POST['id']));

            
    $count $sql->rowCount();

            if(
    $count == 1){
                
    $userEdit 'yes';
            }elseif(
    $count == 0){
                
    $noEdit 'yes';
            }
        }

    I display different HTML blocks based on whether those variables are set with isset().

    Thanks

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,247
    Check out this comment on the PHP site and see if adding that last parameter to your PDO object creation resolves this: http://www.php.net/manual/en/pdostat...unt.php#104930
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles