PHP SQL PDO Update Query

Question

At the suggestion of many, I am learning PDO to perform a large PHP MySQL query. What is wrong with my query? The code is just one large form submission, and everyone recommends PDO over handcoding a large mysql query.

The query itself gives a complain from Dreamweaver but not from Zend Studio. Could anything be wrong?

<?php
$host="localhost"; // Host name
$username="********"; // Mysql username
$password="********"; // Mysql password
$db_name="practice"; // Database name
$tbl_name="administration"; // Table name

// Connect to server and select databse.
//$dbc = mysql_connect("$host", "$username", "$password")or die("cannot connect");
    try {  

      # MySQL with PDO_MYSQL  
      $DBH = new PDO("mysql:host=$host;dbname=$db_name", $username, $password); 

    }  
    catch(PDOException $e) {  
        echo $e->getMessage("Error Connecting to Database");  
        $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );  
    }  


mysql_select_db("$db_name")or die("cannot select DB");

//These variables stay the same and can be used as is in A PDO submission
$ac1=$_POST['ac1'];
$ac2=$_POST['ac2'];
$fan=$_POST['fan'];
$na=$_POST['na'];
$dh=$_POST['dh'];
//Initialization of variables is typical
$tolerance1=$_POST['tolerance1'];
$temptime1=$_POST['temptime1'];
$tolerance2=$_POST['tolernce2'];
$temptime2=$_POST['temptime2'];
$tolerance3=$_POST['tolerance3'];
$temptime3=$_POST['temptime3'];
$tolerance4=$_POST['tolerance4'];
$temptime4=$_POST['temptime4'];
$tolerance5=$_POST['tolerance5'];
$temptime5=$_POST['temptime5'];

$humidtolerance1=$_POST['humidtolerance1'];
$humidtime1=$_POST['humidtime1'];
$humidtolerance2=$_POST['humidtolerance2'];
$humidtime2=$_POST['humidtime2'];
$humidtolerance3=$_POST['humidtolerance3'];
$humidtime3=$_POST['humidtime3'];
$humidtolerance4=$_POST['humidtolerance4'];
$humidtime4=$_POST['humidtime4'];
$humidtolerance5=$_POST['humidtolerance5'];
$humidtime5=$_POST['humidtime5'];

$custnum = 0;
//Each parameter is bound to a number.
$STH->bindParam(1, $ac1);  
$STH->bindParam(2, $ac2);  
$STH->bindParam(3, $fan); 
$STH->bindParam(4, $na);  
$STH->bindParam(5, $dh);  
$STH->bindParam(6, $tolerance1);
$STH->bindParam(7, $temptime1);
$STH->bindParam(8, $tolerance2);
$STH->bindParam(9, $temptime2);
$STH->bindParam(10, $tolerance3);
$STH->bindParam(11, $temptime4);
$STH->bindParam(12, $tolerance4);
$STH->bindParam(13, $temptime4);
$STH->bindParam(14, $tolerance5);
$STH->bindParam(15, $temptime5);
$STH->bindParam(16, $humidtolerance1);
$STH->bindParam(17, $humidtime1);
$STH->bindParam(18, $humidtolerance2);
$STH->bindParam(19, $humidtime2);
$STH->bindParam(20, $humidtolerance3);
$STH->bindParam(21, $humidtime3);
$STH->bindParam(22, $humidtolerance4);
$STH->bindParam(23, $humidtime4);
$STH->bindParam(24, $humidtolerance5);
$STH->bindParam(25, $humidtime5);
$STH->bindParam(26, $custnum);

//Dreamweaver says there is an error here but Zend Studio does not.
# unnamed placeholders  
$STH = $DBH->("UPDATE $tbl_name WHERE custnum = $custnum (ac1, ac2, fan, na, dh, tolerance1, temptime1, tolerance2, temptime2, tolerance3, temptime3, tolerance4, temptime4, tolerance5, temptime5, humidtolerance1, humidtime1, humidtolerance2,  humidtime2, humidtolerance3,  humidtime3, humidtolerance4,  humidtime4, humidtolerance5,  humidtime5,) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 
$STH->execute();  

//Send them back to the page they were at/
header("location:index.php");
?>

Do you actually get any error outside of your IDE when testing? — maxedison
– maxedison, Commented Mar 9, 2012 at 18:38
I'd assume so, considering $DBH->('...') is bad grammar... — cHao
– cHao, Commented Mar 9, 2012 at 18:39
Yes, the error is Parse error: syntax error, unexpected '(', expecting T_STRING or T_VARIABLE or '{' or '$' in /hvac/admin/admin.php on line 84 — Daniel USAF
– Daniel USAF, Commented Mar 9, 2012 at 18:40

netcoder · Accepted Answer · 2012-03-09 18:48:08Z

10

Well you're doing:

$DBH->("...");

instead of:

$STH = $DBH->prepare("...");

You're also using $STH uninitialized in your code (since it's prepare that initializes it and it's missing). You'd want to prepare the statement first, then bind parameters to it (not the other way around, like it is actually the case):

$STH = $DBH->prepare("...");
$STH->bindParam(1, $ac1);  
$STH->bindParam(2, $ac2);
// ...

You can also just prepare it and pass an array to PDOStatement::execute:

$STH = $DBH->prepare("...");
$STH->execute(array($ac1, $ac2, ...));

Your MySQL query is also wrong, you're doing:

UPDATE table WHERE something = something (column1, column2) values (?, ?)

The WHERE is misplaced, and the (column) VALUES (?) syntax is the INSERT syntax, not UPDATE. You'd want to do this instead:

UPDATE table SET column1=?, column2=? WHERE something = something

Lastly, you should remove this:

mysql_select_db("$db_name")or die("cannot select DB");

edited Mar 9, 2012 at 18:48

answered Mar 9, 2012 at 18:42

netcoder

68k19 gold badges129 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RumpRanger Over a year ago

Collectives™ on Stack Overflow

PHP SQL PDO Update Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related