PHP ADODB Insert

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 and get empty record

$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 insert

$record = array();
$record['id'] = 1;
$record['name'] = 'bob';
$record['age'] = 30;

Step 4:Call GetInsertSQL to insert

$inesrtSQL = $conn1->GetInsertSQL($rs, $record);
$conn1->Execute($inesrtSQL);
$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;

$sql = "SELECT * FROM profile WHERE id= -1";


$rs = $conn1->Execute($sql);

$record = array();
$record['id'] = 1;
$record['name'] = 'bob';
$record['age'] = 30;

$inesrtSQL = $conn1->GetInsertSQL($rs, $record);
$conn1->Execute($inesrtSQL);
$conn1->close();

?>

Display

(mysql): SELECT * FROM profile WHERE id= -1  
(mysql): INSERT INTO profile ( ID, NAME, AGE ) VALUES ( 1, 'bob', 30 )  

Query Insert Result Table

mysql> select * from profile;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | bob  |  30 |
+----+------+-----+
1 row in set (0.00 sec)

Create PHP Function to handle the insert

• I create a custom function to handle most of the insert.

function insert_record_adodb($db, $table, $dataobject, $returnid=true, $primarykey='id') {

    if (empty($db)) {
        return false;
    }
/// Check we are handling a proper $dataobject
    if (is_array($dataobject)) {

        $dataobject = (object) $dataobject;
    }
    // $db->debug = true;
    $rs = $db->Execute('SELECT * FROM ' . $table . ' WHERE ' . $primarykey . ' = \'-1\'');
    /// Get the correct SQL from adoDB
    if (!$insertSQL = $db->GetInsertSQL($rs, (array) $dataobject, true)) {
        return false;
    }
/// Run the SQL statement
    if (!$db->Execute($insertSQL)) {

        return false;
    }

    $id = $db->Insert_ID();


    return (integer) $id;
}

Usage 1

require_once 'adodb.inc.php';
require_once 'mycustomfunction.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;

$nameobject1 = new stdClass();
$nameobject1->name = 'Janny';
$nameobject1->age = 32;

$id = insert_record_adodb($conn1, 'profile', $nameobject1, $return = true);
echo 'insert id' . $id;

Related Links

• PHP ADODB Example
• PHP Tutorial
• Tutorial Home

---

• PHP ADODB Tutorial
• PHP ADODB Connect
• PHP ADODB Create Table
• PHP ADODB Insert Records with Place Holder

• PHP ADODB Insert and Custom Function
• PHP ADODB Select Record
• PHP ADODB FETCH ASSOC
• PHP ADODB FETCH NUM

• PHP ADODB FETCH BOTH
• PHP ADODB Update with place holder
• PHP ADODB Update and Custom Function
• PHP ADODB RecordSet Filters

• PHP ADODB Export in CSV
• PHP ADODB Delete Record
• PHP ADODB Tutorial with HTML