PHP ADODB Insert

From CoderLearner
Jump to: navigation, search

Description

Create the custom insert function can be very beneficial, you don't need to write a long SQL insert statement.

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

This is the custom insert function to handle the insert statement. Check the example to see its usage.

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;