SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PDO Prepared Update Statement Not Updating DB

    I have jquery posting some data to this php file, the dev tools show the post data correctly. My insert delete statements execute correctly but this prepared statement does not. What have I done incorrectly.

    Thanks in advance, James

    PHP Code:
    <?php

        $dbtype     
    "mysql";
        
    $dbhost     "localhost";
        
    $dbname     "dbname";
        
    $dbuser     "dbuser";
        
    $dbpass     "dbpass";

    try {
        
        
    // database connection
         
    $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
        
    $conn -> exec('SET CHARACTER SET utf8');
            
    } catch(
    PDOException $e) {
        echo 
    'There was a problem. Caught exception: ',  $e->getMessage(), "\n";
    }

    $date date('Y-m-d H:i:s'); 

    if (isset(
    $_POST['id'])) {

         
    $sql "UPDATE catalogues "
        
    .$sql "SET title = :title,
                manufacturer = :manufacturer,            
                modified = :modified,
                category_id = :category_id,
                subcategory_id = :subcategory_id "            
        
    .$sql "WHERE id = :id";

        
    $query $conn->prepare($sql);    
        
    $query->bindParam(":id",$_POST['id']);    
        
    $query->bindParam(":title",$_POST['title']);    
        
    $query->bindParam(":manufacturer",$_POST['manufacturer']);        
        
    $query->bindParam(":category_id",$_POST['category_id']);    
        
    $query->bindParam(":modified",$date);
        
    $query->bindParam(":subcategory_id",$_POST['subcategory_id']);

        if (
    $query->execute()){             
          echo 
    $_POST['title']." updated successfully.";
        } else {      
          echo 
    "Crap, something went wrong.";
        }
    }
    ?>

  2. #2
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    772
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    And how about like this?
    PHP Code:
    $sql "UPDATE catalogues  
    SET title = :title, 
    manufacturer = :manufacturer,             
    modified = :modified, 
    category_id = :category_id, 
    subcategory_id = :subcategory_id  
    WHERE id = :id"


  3. #3
    SitePoint Enthusiast Belsnickle's Avatar
    Join Date
    Feb 2012
    Location
    Chico, CA
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, for starters I'd move your execute call outside of the if conditions, you should be checking $query->rowCount() = 1 . rowCount lets you see the number of rows affected by the query and since you seem to only ever need one it's an easy place to check for an issue with the response, you could even throw in an else to respond to it being greater than 1.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    This part is wrong:

    PHP Code:
    $sql "UPDATE catalogues " 
        
    .$sql "SET title = :title, 
                manufacturer = :manufacturer,             
                modified = :modified, 
                category_id = :category_id, 
                subcategory_id = :subcategory_id "             
        
    .$sql "WHERE id = :id"
    I'm not sure what it'll do, but I'm sure it doesn't do what you expect it does.
    I think you meant

    PHP Code:
    $sql "UPDATE catalogues "
        
    "SET title = :title, 
                manufacturer = :manufacturer,             
                modified = :modified, 
                category_id = :category_id, 
                subcategory_id = :subcategory_id "             
        
    "WHERE id = :id"
    Even better would be (my opinion)

    PHP Code:
    $sql "
    UPDATE
       catalogues
    SET
       title = :title
     , manufacturer = :manufacturer
     , modified = :modified
     , category_id = :category_id
     , subcategory_id = :subcategory_id
    WHERE
       id = :id
    "

    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Enthusiast Belsnickle's Avatar
    Join Date
    Feb 2012
    Location
    Chico, CA
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually that looks like he's trying to append the SQL to the query and mistook what the manner that's done in was, instead replicating something that would be done inside a function, like echo, but didn't realise echo is a function because it's generally appended to non-parenthetically.

    PHP Code:
    $sql "UPDATE catalogues " 
        
    .$sql "SET title = :title, 
                manufacturer = :manufacturer,             
                modified = :modified, 
                category_id = :category_id, 
                subcategory_id = :subcategory_id "             
        
    .$sql "WHERE id = :id"
    The way the code would read as written while executing would probably be something like this:
    $sql = "UPDATE catalogues " "" =[error] "SET title = :title, manufacturer = :manufacturer, modified = :modified, category_id = :category_id, subcategory_id = :subcategory_id " . "" =[error] "WHERE id = :id";

    What it should be is this:

    PHP Code:
    $sql "UPDATE catalogues ";
    $sql .= "SET title = :title, 
                manufacturer = :manufacturer,             
                modified = :modified, 
                category_id = :category_id, 
                subcategory_id = :subcategory_id "
    ;           
    $sql .= "WHERE id = :id"
    .= is the concatenation assignment operator in PHP, it essentially means append to end and it shorthand for $variable = $variable . "random string"; as . is the concatenation operator for strings. If you know javascript think of . as + and .= as Add to end of variable (string).


    That all being said, you probably shouldn't be attempting to concatenate the string unless it's via conditionals or assigned variables. Since in this case the string in the variable $sql isn't being modified by anything other than binds it should simply just be being assigned in one call to avoid issues like this (so like post #4 above).

  6. #6
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,446
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    This part is wrong:

    PHP Code:
    $sql "UPDATE catalogues " 
        
    .$sql "SET title = :title, 
                manufacturer = :manufacturer,             
                modified = :modified, 
                category_id = :category_id, 
                subcategory_id = :subcategory_id "             
        
    .$sql "WHERE id = :id"
    I'm not sure what it'll do, but I'm sure it doesn't do what you expect it does.
    I think you meant
    I thought that too when I saw it, but I tested it and it actually still does concatenate the strings correctly (even though it's not the usual way to do it).

  7. #7
    SitePoint Enthusiast Belsnickle's Avatar
    Join Date
    Feb 2012
    Location
    Chico, CA
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Probably because it's all one long string and the $sql variables are as of yet undefined while it's executing so it's being treated as . =

    At least that'd be my guess since it's completely lacking of line ends.


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
  •