PDO update

[rahjiggah]

Hello, can someone please let me know if my code here is wrong?

 

Im trying to upgrade old mysql to PDO.

 

Just a simple update command and on a few values if the $variable = "" then make the value null in DB.

 

thank you

try{
$sql1="UPDATE newone
SET corating=:corating,
cotarg=:cotarg,
coclose=:coclose,
ROI=:ROI,
est1=:est1,
est2=:est2,
basicos=:basicos,
mktcap=:mktcap,
coNav=:nav,
WHERE coID=:coID";

$q1 = $dbh->prepare($sql1);
$q1->execute(array(

':corating'=>$corating,
':cotarg'=>$cotarg,
':coclose'=>$coclose,
':ROI'=>$ROI,
':est1'=>isset($est1) ? $est1 : null,
':est2'=> isset($est2) ? $est2 : null,
':basicos'=>$basicos,
':mktcap'=>$mktcap,
':coNav'=>isset($nav) ? $nav : null,
':coID'=>$coID,
));

} catch(PDOException $e) {
  echo 'ERROR: ' . $e->getMessage();
}

[vinny42]

 

can someone please let me know if my code here is wrong?

 

Well, if it was correct then you wouldn't be asking the question would you. :-)

 

So tell us what is going wrong, do  you get any errors, strange behaviour, what?

[rahjiggah]

ya sorry that makes sense!

 

yup so code executes but nothing is updated in the table. 

 

There are so many different ways to do this, but this seemed like the easiest way to go.

 

the error isnt showing up so I assume Ive got the syntax wrong... 

[mac_gyver]

for the pdo statements to throw errors, you must configure your instance of the pdo class to do so, either when you create the connection/instance of the class or via a separate call to the PDO::setAttribute method.

 

the only thing apparent in the posted code is that the php null value the code could be assigning to the array entries isn't the same as a database null keyword and are likely producing query errors.

 

if you are trying to use a database null keyword, it needs to literally be the letters n,u,l, and l (with no quotes of any kind around it). however, when you supply the data via the ->execute() method (rather than binding the parameters), all the values are treated as string data and are enclosed by single-quotes when inserted into the query statement.

Edited September 26, 2013 by mac_gyver

[rahjiggah]

yup must be that, I used the setAttribute and saw this:

 

 ERROR: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Edited September 26, 2013 by rahjiggah

[mac_gyver]

for the three values you are doing that for, it's common practice to simply leave the column/value out of the query statement and let the database assign the default value defined for the column(s) not listed in the query.

[rahjiggah]

hmmmm so you mean the actual sql table would determine if the value is NULL?

 

basically this was to prevent that col from assigning 0.00 value, 0.00 can be a legitmate number for those columns but nothing (ie no value) also shows up as 0.00.

 

how would I set that up in mysql?

[rahjiggah]

got it 

 

just did a simple if for the variables in question... figured Id post it here in case anyone else has this prob...

 

Thx for the help Guru!

try{
$sql1="UPDATE newone
SET 
est1=:est1,
est2=:est2,
WHERE coID=:coID";

$q1 = $dbh->prepare($sql1);
if ($est1 == ""){
$est1 = null;
}
$q1->bindValue(':est1', $est1);

if ($est2 == ""){
$est2 = null;
}
$q1->bindValue(':est2', $est2);

$q1->execute();

Edited September 27, 2013 by rahjiggah