MySQL update using PDO with variable

Question

I'm having a strange problem with php PDO and mysql. I read other examples here, but while I am learning MySQL with the PDO, I did not understand it and I could not solve it yet.

$name = $_POST[ "name" ];
$email = $_POST[ "email" ];
$telefone = $_POST[ "telefone" ];
$endereco = $_POST[ "endereco" ];
$numero = $_POST[ "numero" ];
$bairro = $_POST[ "bairro" ];
$cidade = $_POST[ "cidade" ];

$telefoneHash = make_hash( $telefone );

$PDO = db_connect();

//$PDO->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//With this code it gives the error: 'Syntax error or access violation: 1064'
//Without it it does not give an error but does not perform the update

$PDO = $PDO->prepare( 'UPDATE users SET :name, :email, :telefone, :endereco, :numero, :bairro, :cidade WHERE email = :email' ); 

$PDO->bindValue( ':name', $_REQUEST[ 'name' ] );
$PDO->bindValue( ':email', $_REQUEST[ 'email' ] );
$PDO->bindValue( ':telefone', $telefoneHash );
$PDO->bindValue( ':endereco', $_REQUEST[ 'endereco' ] );
$PDO->bindValue( ':numero', $_REQUEST[ 'numero' ] );
$PDO->bindValue( ':bairro', $_REQUEST[ 'bairro' ] );
$PDO->bindValue( ':cidade', $_REQUEST[ 'cidade' ] );
$PDO->execute();

echo $PDO->rowCount() . " records UPDATED successfully";

And in other examples that you read UPDATE syntax is the same as yours? — u_mulder
– u_mulder, Commented Feb 6, 2018 at 16:40
A.) Your update query is incorrect. 2.) You should be using an INSERT instead of an UPDATE....maybe iii.) There is no need to change to $_REQUEST if your variables are set at the top of the page. — Jay Blanchard
– Jay Blanchard, Commented Feb 6, 2018 at 16:42
@JayBlanchard INSERT instead of an UPDATE. With a where clause? Lack of coffee again? — u_mulder
– u_mulder, Commented Feb 6, 2018 at 16:46
You're creating a bunch of variables that serve no purpose here as well. PDO also allows you to fire a single execute call with an associative array that has all the values, that helps cut down on how much code you have to write. — tadman
– tadman, Commented Feb 6, 2018 at 16:46

Funk Forty Niner · Accepted Answer · 2018-02-06 16:49:38Z

5

The syntax for UPDATE with PDO is SET col=:col

Using the column name, followed by the equal sign and the named placeholder.

$PDO = $PDO->prepare( 'UPDATE users SET name = :name, email = :email, 
                       telefone = :telefone, endereco = :endereco, 
                       numero = :numero, bairro = :bairro, cidade = :cidade 
                       WHERE email = :email' );

PDO error handling would have clearly shown you the errors:

https://secure.php.net/manual/en/pdo.error-handling.php

Use error reporting also.

https://php.net/manual/en/function.error-reporting.php

This assuming that the column names are of the same names that I used here.

However and as stated, why use the $_REQUEST's? Just use the variables that you assigned in the POST arrays, and assuming that your form is using a post method.

$PDO->bindValue(':name', $name);
$PDO->bindValue(':email', $email);
$PDO->bindValue(':telefone', $telefoneHash);
$PDO->bindValue(':endereco', $telefone);
$PDO->bindValue(':numero', $numero);
$PDO->bindValue(':bairro', $bairro);
$PDO->bindValue(':cidade', $cidade);
$PDO->execute();

edited Feb 6, 2018 at 16:49

answered Feb 6, 2018 at 16:44

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

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

1 Comment

Funk Forty Niner Over a year ago

u_mulder pointed out another in his answer about the email placeholder; I'm not worthy ;-)

u_mulder · Accepted Answer · 2018-02-06 16:44:29Z

3

Base UPDATE query syntax is

UPDATE `table_name` SET field_name = FIELD_VALUE

So, in your query you should do:

$PDO = $PDO->prepare( 'UPDATE users SET name = :name, email = :email /* more fields here */ WHERE email = :email2' ); 

$PDO->bindValue( ':name', $_REQUEST[ 'name' ] );
$PDO->bindValue( ':email', $_REQUEST[ 'email' ] );
$PDO->bindValue( ':email2', $_REQUEST[ 'email' ] );
// more binds here
$PDO->execute();

echo $PDO->rowCount() . " records UPDATED successfully";

First note: you can't use same placeholder name twice, that's why I replaced :email with :email2.

Second note: I suppose, it's useless to update email by which you find the record to the same value.

answered Feb 6, 2018 at 16:44

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

Collectives™ on Stack Overflow

MySQL update using PDO with variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related