PDO DB UPDATE PROBLEMS

[alenphp]

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 "text" and "text"  where title = some title
$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 "text" and "text"  where title = some title
$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;
?>

I don't get it why it wont accept variable instead of string text as a value?

 

Thanx in advance!

[paddy_fields]

$sql = "UPDATE bloging SET title=".$nTitle.", tekst=".$nText." WHERE title=".$oldTitle;

[kicken]

You'd need to make sure your variables are escaped and that you generate valid SQL statements. As your code stands right now, you are not doing either.

UPDATE bloging SET title=$nTitle, tekst=$nText WHERE title=$oldTitle

after substituting the variables you'd yield:

UPDATE bloging SET title=novinaslov, tekst=novitekst WHERE title=Stari naslov

Notice how there are no quotes around your values?

 

However, forget your variables in the SQL method. You should be using prepared statements with bound parameters:

$sql = "UPDATE bloging SET title=:ntitle, tekst=:ntext WHERE title=:oldtitle";
$stmt = $conn->prepare($sql);
$stmt->execute(array(
  ':ntitle' => $nTitle
  , ':ntext' => $nText
  , ':oldtitle' => $oldTitle
));

[mac_gyver]

and, if you turn on exceptions after, or inside, your connection statement, the rest of the statements that fail will throw an exception that your try/catch block will use. in the current code, the ->exec() statement that is failing isn't throwing an exception so that you know it is failing -

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

[jazzman1]

and, if your database container is set the same as your intended character set in this case UTF-8, you don't have to force every time
SET NAMES making the connection charset to whatever you specify. Instead using CHARACTER SET instead of SET NAMES.

$conn = new PDO("mysql:host=$dbhost; dbname=$dbname;charset=utf8", $usernm, $dbpass);

[alenphp]

Thank you guys! I've got working code now, thanks to you. And it looks like this:

 

<?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();
}


?>