pdo codeigniter not executing update query

Question

I'm trying to update a table using this query but does not seem to work.

  $query="UPDATE product SET qty = (qty - '$qty') WHERE barcode = '$barcode'";  
  $result = $this->db->conn_id->prepare($query);
  $result->execute();

I've tried placing the query inside a try catch block but it does not throw any error. The issue is with the implementation in CodeIgniter as this query is working when executed outside codeigniter.

What is `conn_id` referencing to? I do not see this in the examples in the [documentation](http://ellislab.com/codeigniter/user-guide/database/queries.html). — afuzzyllama, Dec 27 '12 at 15:21

afuzzyllama · Accepted Answer · 2012-12-27T15:43:42.110

1

Looking at your code there are a few things.

You aren't using the prepared statement right. The benefit to using a prepared statement is passing in the variables you need with a different function so you can escape them correctly. Consider the following:

$query="UPDATE product SET qty = (qty - ':qty') WHERE barcode = ':barcode'";  

$stmt = $this->db->conn_id->prepare($query);

$stmt->bindParam(':qty', $qty, PDO::PARAM_STR);
$stmt->bindParam(':barcode', $barcode, PDO::PARAM_STR);

$stmt->execute();  

echo "Rows affected: " . $stmt->rowCount();

Here we take the query and setup the parameters within. Then we bind the variables to the statement so they are escaped properly. After that we can execute the statement and then use the fetch() function to get our response. The enumeration passed in will return the results as an associative array.

edited Dec 27 '12 at 15:43

answered Dec 27 '12 at 15:32

afuzzyllama

6,538
5
47
64

Parts of this answer reference this [SO question](http://stackoverflow.com/questions/5884761/how-can-i-use-pdo-in-codeigniter-2) – afuzzyllama Dec 27 '12 at 15:36
thanks for the correction, but still it doesnt update my table. – swordfish Dec 27 '12 at 15:38
Updated my answer. What is the row count for your query? – afuzzyllama Dec 27 '12 at 15:45
Rows affected: 0, ive tried placing static value for quantity but still doesnt work. – swordfish Dec 27 '12 at 15:49
What happens when you do `var_dump($this->db->conn_id)`. Are you sure the record exists in the DB that you are trying to update? – afuzzyllama Dec 27 '12 at 15:56
yes, no problem with the db because i have the same application but not using codeigniter and it works fine. – swordfish Dec 27 '12 at 16:00
Try turning on error throwing? http://stackoverflow.com/questions/12190452/php-pdo-not-updating-table-and-producing-no-errors – afuzzyllama Dec 27 '12 at 16:03
its all working now, i changed the barcode datatype to integer.. but how come my other application using this same query works? – swordfish Dec 27 '12 at 16:19

score 0 · Answer 2 · answered Dec 27 '12 at 15:55

0

If row affected is zero ,i guess the query is correct,see if the value of the barcode variable exists in the database

answered Dec 27 '12 at 15:55

Vaibhav

289
1
3
11

pdo codeigniter not executing update query

2 Answers2