PDO statement error

[gmc1103]

Hi

 

I'm having an error in the following code and i don't get why

 public function delItem($id_itens){
        try {
            $sql = 'DELETE FROM `item` WHERE `id_itens`=:me';
            $stmt = $this->db->prepare($sql);
            $stmt->bindValue(':me', $id_itens, PDO::PARAM_STR);
            if (!$stmt->execute()) {
                print_r($stmt->errorInfo());
                return array('status' => 'error', 'message' => 'Problema ao remover este item...');
            } else {
                print_r($this->db->query($sql));
                return array('status' => 'success', 'message' => 'O registo foi removido com sucesso...');
            }
        }
        catch (PDOException $e) {
            echo $e->getMessage();
        }
        
    }

and the error is 

 

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':me' at line 1

[Barand]

Are you sure the spelling is correct?

 

$sql = 'DELETE FROM `item` WHERE `id_itens`=:me';

[gmc1103]

Hi Barand

 

i have removed this line 

print_r($this->db->query($sql));

and i don't have that error again but i have a strange situation

 

 

From my sqlyog i use this

DELETE FROM `esmaior_biblioteca`.`item` WHERE `id_itens` = "6";

and it works

 

in the code i put a line to check if i receive the var and i have result (11)

print_r($id_itens);
4a "11"{"status":"success","message":"O registo foi removido com sucesso..."} 0 

And it say removed but when i check it still in my database..

[Barand]

I see you bind the value with "PDO::PARAM_STR" yet your data (6 and 11) are numeric, How is id_itens defined?

[gmc1103]

id_itens are defined in database int

But i receive like this "7", that's why i put PDO::PARAM_STR" 

[mac_gyver]

i'm going to guess that the value in your $id_itens variable actually contains double-quotes around it.

 

if you are using print_r($id_itens) and it produced "11" in the output, the only way that can happen is if there are double-quotes in $id_itens.

$var = 1;
print_r($var);
echo '<br>';

$var = '1';
print_r($var);
echo '<br>';

$var = "1";
print_r($var);
echo '<br>';

$var = '"1"';
print_r($var);
echo '<br>';

output -

1
1
1
"1"

instead of print_r(), use var_dump() as it also gives the length of whatever is in the variable.

 

so, what is your code that is producing $id_itens?

Edited November 3, 2015 by mac_gyver

[gmc1103]

Hi mac_gyver

 

The double quote comes from this part of code

 'click .remove': function (e, value, row, index) {
                $('#myModal').modal('show').find('.modal-body #nome').val(JSON.stringify(row.nome));
                $('#myModal').modal('show').find('.modal-body #id_itens').val(JSON.stringify(row.id_itens));
            }

And i have checked and i can see double quotes in input text area of modal

 

Using var_dump the $id_itens retrives ""3""

 

What's wrong, it is the JSON??

 

Thanks

[Barand]

Unless it's an object or an array there is no need to stringify.

[gmc1103]

Yes, it is an object, i have resolved this issue using

  var jobstr=JSON.stringify(row);
  var semAspas=JSON.parse(jobstr);
  var itemN = semAspas.id_itens; // id_itens
  var nome = semAspas.nome; // name
 

Now i receive just the number and not with double quotes.

 

Thank all for pointing me to the rigth direction

 

Bes regards

[Barand]

row is an object but row.id_itens isn't.

 

You could have just done this (without the stringify)

$('#myModal').modal('show').find('.modal-body #id_itens').val(row.id_itens);