adodb, AutoExecute update field with field

Question

i have table "users" like this

id  | firstname | lastname
==========================
1   | Ujang     | ahmad
2   | Jajat     | sudrajat

and have data :

$record = array('firstname'=>'some value', 'lastname'=>'some value');
$table  = "users";

and process update like this :

$exc= $conn->AutoExecute($table, $record, 'UPDATE', 'id = 1');

how do I update field firstname with the value of lastname use AutoExecute

so I get a result like this :

id  | firstname | lastname
==========================
1   | ahmad     | Ujang
2   | sudrajat  | Jajat

Answer 1

Unless I misunderstand you, AutoExecute doesn't seem right for the job. If you need to do a one-time conversion of all records in your table I would just rename the columns.

ALTER TABLE `users`
CHANGE COLUMN `lastname` `firstname`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `id`,
CHANGE COLUMN `firstname` `lastname`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `firstname`;

Or in PHP/ADODB:

$sql = "ALTER TABLE `users`
  CHANGE COLUMN `lastname` `firstname`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `id`,
  CHANGE COLUMN `firstname` `lastname`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `firstname`;";
if (($result = $conn->Execute($sql)) === false)
  exit($sql.'<br />'.$conn->errorMsg());

If you need to target specific records you could use a temporary variable.

$sql = "UPDATE users
  SET firstname=(@temp:=firstname), firstname = lastname, lastname = @temp
  WHERE id=2";
if (($result = $conn->Execute($sql)) === false)
  exit($sql.'<br />'.$conn->errorMsg());

Cheers