Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -2 down vote favorite

I've made a code on php to modificate some mysql cells content, but my code are returning the error: 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 '(datanasc,rg,rgemissor,cpf,sexo,cnh,tituloeleitor,zonaeleitoral,' at line 1

    <?php
    include('mysql.php');
    mysql_select_db("teste", $conexao);

    $schema = array('idfunc', 'nomefunc', 'rg', 'rgemissor', 'cpf', 'cnh', 'reservista', 'estrangeiro', 'tituloeleitor', 'zonaeleitoral', 'sindicato', 'matsindical', 'estadocivil', 'datanasc', 'nacionalidade', 'naturalidade', 'endereco', 'salario', 'expediente', 'cargahoraria', 'beneficiarios', 'admissao', 'numeropis', 'banco', 'agencia', 'dataretroativo', 'datadispensa', 'sexo', 'nomepai', 'nomemae', 'funcao', 'linkfoto');
    $fields = array();
    $values = array();
    foreach($_POST as $key => $val){
        if (in_array($key, $schema)){
            $fields[] = "`".mysql_real_escape_string($key)."`";
            $values[] = "`".mysql_real_escape_string($val)."`";
        }
    }

    $ins = mysql_query("UPDATE funcionarios SET(".implode(",",$fields).") VALUES(".implode(",",$values).")") or die(mysql_error());
    echo "Funcionário modificado com sucesso.";


    mysql_close($conexao)
    ?>
share|improve this question
1  
that's not the right UPDATE syntax. check here dev.mysql.com/doc/refman/5.0/en/update.html – Lorenzo Marcon 6 hours ago

4 Answers

up vote 2 down vote

Your query is incorrectly formed because you're basically using an INSERT statement with the word "UPDATE" used in lieu of "INSERT".

An update statement is structured like so:

UPDATE table_name SET column = value WHERE column = conditional_value;

Whereas you're attempting to perform an UPDATE like this:

UPDATE table_name SET(column) VALUES(value);
share|improve this answer
up vote 1 down vote

The correct syntax of update is

UPDATE table_name
SET column_name = value, column_2 = value....
WHERE column1 = value

But your query will produce it as 

UPDATE funcionarios SET column1,column2,... VALUES val1,val2,...
share|improve this answer
how do i get the number of each column on array? – caio 6 hours ago
up vote 0 down vote

change this line: 

"`".mysql_real_escape_string($val)."`";

to 

"'".mysql_real_escape_string($val)."'";

and no need to escape column

"`".($key)."`"

Then change the code to:

foreach($_POST as $key => $val){
        if (in_array($key, $schema)){
            $updates[] = " `$key` =  '".mysql_real_escape_string($val)."'";
        }
}

and this line:

$ins = mysql_query("UPDATE funcionarios SET ".implode(", ", $updates)) or die(mysql_error());
share|improve this answer
Script is running good but it's not modifying mysql record. – caio 6 hours ago
up vote 0 down vote

This means your query isn't correct due to errors. Print out your query ("Update...) to see the the structure and verify where the error is in your query. Also, your update statement is incorrect.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.