PHP ADODB Update
From w3cyberlearnings
Contents |
Table
create table( id INT NOT NULL AUTO_INCREMENT, name varchar(200), age INT, PRIMARY KEY(id) );
Step 1:Connect to the database
require_once 'adodb.inc.php';
// Connection string
$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'school';
// set connection
$conn1 = &ADONewConnection('mysql');
$conn1->PConnect($host, $user, $pass, $dbname);
Step 2: Execute query with the id for update
- You need to know the id of the table you want to update.
$conn1->debug = true; //set debug to true to see the debug $sql = "SELECT * FROM profile WHERE id= 1"; $rs = $conn1->Execute($sql);
Step 3:Generate the record array for update
$record = array(); $record['name'] = 'bob lee'; $record['age'] = 31;
Step 4:Call GetUpdateSQL to Update
$updateSQL = $conn1->GetUpdateSQL($rs, $record); $conn1->Execute($updateSQL); $conn1->close();
Complete Code
<?php
require_once 'adodb.inc.php';
// Connection string
$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'school';
// set connection
$conn1 = &ADONewConnection('mysql');
$conn1->PConnect($host, $user, $pass, $dbname);
$conn1->debug = true;
// has to be id value to be update
$sql = "SELECT * FROM profile WHERE id = 1";
$rs = $conn1->Execute($sql);
$record = array();
$record['name'] = 'bob lee';
$record['age'] = 31;
$updateSQL = $conn1->GetUpdateSQL($rs, $record);
$conn1->Execute($updateSQL);
$conn1->close();
?>
Check Result
mysql> select * from profile where id=1; +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | bob lee | 31 | +----+---------+-----+ 1 row in set (0.00 sec) mysql>
PHP function for update record in ADODB
- This is a simple and reusable PHP function for ADODB for update record.
function updateRecord($db, $table, $array, $primary='id') {
if (!$array['id']) {
return false;
}
$sql = "SELECT * FROM {$table} WHERE {$primary} = {$array['id']}";
$rs = $db->Execute($sql);
$updateSQL = $db->GetUpdateSQL($rs, $array);
$db->Execute($updateSQL);
}
Usage
<?php
require_once 'updateFunction.php';
require_once 'adodb.inc.php';
// Connection string
$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'school';
// set connection
$conn1 = &ADONewConnection('mysql');
$conn1->PConnect($host, $user, $pass, $dbname);
$conn1->debug = true;
$data = array('name' => 'janny', 'age' => 80, 'id' => 3);
if (updateRecord($conn1, 'profile', $data)) {
echo 'yes';
}
$conn1->close();
?>
Check Result
mysql> select * from profile where id=3; +----+-------+-----+ | id | name | age | +----+-------+-----+ | 3 | janny | 80 | +----+-------+-----+ 1 row in set (0.00 sec)
Related Links
- PHP ADODB FETCH BOTH
- PHP ADODB Update with place holder
- PHP ADODB Update and Custom Function
- PHP ADODB RecordSet Filters
