www.webdeveloper.com
Results 1 to 5 of 5

Thread: Problem with using variable values in PDO update statement

  1. #1
    Join Date
    Nov 2013
    Posts
    17

    Problem with using variable values in PDO update statement

    Hi guys!
    I have a slight problem. When I pass values as variables to a sql statement it doesnt work. This is the example:

    THIS WORKS:

    <?php
    require 'DB/dbinc.php';


    try {
    // Connect and create the PDO object
    $conn = new PDO("mysql:host=$dbhost; dbname=$dbname", $usernm, $dbpass);
    $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8

    // changes data in "name" si "link" colummns, where id=3
    $sql = "UPDATE bloging SET title='Novi Title', tekst='Novi tekst' WHERE title='Update post'";
    $count = $conn->exec($sql);

    $conn = null; // Disconnect
    }
    catch(PDOException $e) {
    echo $e->getMessage();
    }

    // If data added ($count not false) displays the number of rows added
    if($count !== false) echo 'Number of rows added: '. $count;
    ?>
    THIS DOES NOT WORK
    <?php
    require 'DB/dbinc.php';
    $oldTitle = 'Stari naslov';
    $nTitle = 'novinaslov';
    $nText = 'novitekst';

    try {
    // Connect and create the PDO object
    $conn = new PDO("mysql:host=$dbhost; dbname=$dbname", $usernm, $dbpass);
    $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8

    // changes data in "name" si "link" colummns, where id=3
    $sql = "UPDATE bloging SET title=$nTitle, tekst=$nText WHERE title=$oldTitle";
    $count = $conn->exec($sql);

    $conn = null; // Disconnect
    }
    catch(PDOException $e) {
    echo $e->getMessage();
    }

    // If data added ($count not false) displays the number of rows added
    if($count !== false) echo 'Number of rows added: '. $count;
    ?>

    THANK YOU IN ADVANCE!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,330
    Make use of bound parameters, so that you don't have to worry about quoting and escaping data:
    PHP Code:
    // changes data in "name" si "link" colummns, where id=3
    $sql "UPDATE bloging SET title=:nTitle, tekst=:nText WHERE title=:oldTitle";
    $stmt $conn->prepare($sql);
    if(
    $stmt == false) {
        throw new 
    Exception(print_r($conn->errorInfo(),1).PHP_EOL.$sql);
    }
    $result $stmt->execute(
        array(
            
    ':nTitle'   => $nTitle,
            
    ':nText'    => $nText,
            
    ':oldTitle' => $oldTitle
        
    )
    );
    if(
    $result == false) {
        throw new 
    Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
    }
    $count $stmt->rowCount(); 
    PS: You can help us help you by making use of this forum's [php], [code], and [html] tags around applicable code snippets.
    "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 2013
    Posts
    17

    Now it reports "Internal Server Error"

    The code that doesn't work did not report any error. It just didn't do anything. Now I copyied your code and reports error? Any idea where is the problem? Also I don't get it why it wont accept variable instead of string text as a value? It works just fine with db connection! But not for update!?
    Sorry for not putting code in quotes b4.

    Thanx in advance.

    PHP Code:
    <?php
    require 'DB/dbinc.php';

    $oldTitle 'Stari naslov';
    $nTitle 'novinaslov';
    $nText 'novitekst';

    try {
    // Connect and create the PDO object
    $conn = new PDO("mysql:host=$dbhost; dbname=$dbname"$usernm$dbpass);
    $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8

    // changes data in "bloging" for "title" and "post"  where title = some title
    $sql "UPDATE bloging SET title=:nTitle, tekst=:nText WHERE title=:oldTitle"
    $stmt $conn->prepare($sql); 
    if(
    $stmt == false) { 
        throw new 
    Exception(print_r($conn->errorInfo(),1).PHP_EOL.$sql); 

    $result $stmt->execute
        array( 
            
    ':nTitle'   => $nTitle
            
    ':nText'    => $nText
            
    ':oldTitle' => $oldTitle 
        

    ); 
    if(
    $result == false) { 
        throw new 
    Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql); 

    $count $stmt->rowCount();  
    ?>

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,330
    Probably because you have a "try {" that is not closed with a corresponding "catch" block.
    "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 2013
    Posts
    17

    Problem soved!

    PHP Code:
    <?php
      
    require 'DB/dbinc.php';
      
      
    $oT $_POST['starinaslov'];
      
    $nT $_POST['novinaslov'];
      
    $nTx $_POST['novitekst'];
      
      try {
      
    // Connect and create the PDO object
     
    $conn = new PDO("mysql:host=$dbhost; dbname=$dbname"$usernm$dbpass);
     
    $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8
     
     //Update
     
    $sql "UPDATE bloging SET title=:ntitle, tekst=:ntext WHERE title=:oldtitle";
     
    $stmt $conn->prepare($sql);
     
    $stmt->execute(array(
       
    ':ntitle' => $nT
       
    ':ntext' => $nTx
       
    ':oldtitle' => $oT
     
    ));
     
     
    $conn null// Disconnect
     
    }
     catch(
    PDOException $e) {
     echo 
    $e->getMessage();
     }
     
     
    ?>

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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