PHP ADODB Tutorial

From CoderLearner
Jump to: navigation, search

Create Database And Table For the Tutorial

mysql> create database mytestdatabase;
Query OK, 1 row affected (0.00 sec)
mysql> use mytestdatabase;
Database changed
mysql> CREATE TABLE profile
    -> (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(200) NOT NULL,
    -> age INT NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

Connect to MySQL

<?php 
	include 'adodb.inc.php';
	$host= 'localhost';
	$user = 'sophal';
	$pass = '@password';
	$dbname = 'w3cyberlearnings';

	$conn1 = &ADONewConnection('mysql');
	$conn1->PConnect($host,$user,$pass,$dbname);

?>

ADODB Insert Record Statement

<?php
	include 'adodb.inc.php';

	$host = 'localhost';
	$user = 'root';
	$pass = 'caojiang';
	$dbname = 'w3cyberlearnings';

	$conn1 = &ADONewConnection('mysql');
	$conn1->PConnect($host, $user, $pass, $dbname);

	$sql = "INSERT INTO profile(id,name,age) values(null,'Janny',24) ";

	if ($conn1->Execute($sql) == false) {
    		print 'error' . $conn1->ErrorMsg() . '<br>';
	} else {
    		echo 'Success!';
	}
?>

Adodb insert statement with placeholder

<?php
	include 'adodb.inc.php';
        $host = 'localhost';
	$user = 'root';
	$pass = 'caojiang';
	$dbname = 'w3cyberlearnings';

	$conn1 = &ADONewConnection('mysql');
	$conn1->PConnect($host, $user, $pass, $dbname);

	$sql = "INSERT INTO profile(id,name,age) values(null,?,?) ";

	$my_data = array(
 	   array('Horny', 24),
  	  array('Iinny', 28)
	);

	for ($i = 0; $i < count($my_data); $i++) {
 	   if ($conn1->Execute($sql,$my_data[$i]) == false) {
 	       print 'error' . $conn1->ErrorMsg() . '<br>';
	    } else {
   	     echo 'Success!';
  	  }
	}
?>

GetRows()

<?php

       include 'adodb.inc.php';

       $host = 'localhost';
       $user = 'root';
       $pass = 'caojiang';
       $dbname = 'w3cyberlearnings';

       $conn1 = &ADONewConnection('mysql');
       $conn1->PConnect($host, $user, $pass, $dbname);

       $sql = "SELECT * FROM profile";

       $result =  $conn1->Execute($sql);
       if ($result==false) {
          print 'error' . $conn1->ErrorMsg() . '<br>';
       } else {
          
          print_r($result->GetRows());
          
       }

?>

Return

Array
(
    [0] => Array
        (
            [0] => 1
            [id] => 1
            [1] => Bob
            [name] => Bob
            [2] => 24
            [age] => 24
        )

    [1] => Array
        (
            [0] => 2
            [id] => 2
            [1] => Janny
            [name] => Janny
            [2] => 24
            [age] => 24
        )

    [2] => Array
        (
            [0] => 3
            [id] => 3
            [1] => Sopka
            [name] => Sopka
            [2] => 24
            [age] => 24
        )

    [3] => Array
        (
            [0] => 4
            [id] => 4
            [1] => Jinny
            [name] => Jinny
            [2] => 28
            [age] => 28
        )

    [4] => Array
        (
            [0] => 5
            [id] => 5
            [1] => Horny
            [name] => Horny
            [2] => 24
            [age] => 24
        )

    [5] => Array
        (
            [0] => 6
            [id] => 6
            [1] => Iinny
            [name] => Iinny
            [2] => 28
            [age] => 28
        )

)

Get Table Structure

<?php
	include 'adodb.inc.php';
        $host = 'localhost';
        $user = 'root';
        $pass = 'caojiang';
        $dbname = 'w3cyberlearnings';

        $conn1 = &ADONewConnection('mysql');
        $conn1->PConnect($host, $user, $pass, $dbname);

        $table_meta = $conn1->MetaColumns('profile');
     
        print_r($table_meta);
  
?>

Result

Array
(
    [ID] => ADOFieldObject Object
        (
            [name] => id
            [max_length] => 11
            [type] => int
            [scale] => 
            [not_null] => 1
            [primary_key] => 1
            [auto_increment] => 1
            [binary] => 
            [unsigned] => 
            [zerofill] => 
            [has_default] => 
        )

    [NAME] => ADOFieldObject Object
        (
            [name] => name
            [max_length] => 200
            [type] => varchar
            [scale] => 
            [not_null] => 1
            [primary_key] => 
            [auto_increment] => 
            [binary] => 
            [unsigned] => 
            [zerofill] => 
            [has_default] => 
        )

    [AGE] => ADOFieldObject Object
        (
            [name] => age
            [max_length] => 11
            [type] => int
            [scale] => 
            [not_null] => 1
            [primary_key] => 
            [auto_increment] => 
            [binary] => 
            [unsigned] => 
            [zerofill] => 
            [has_default] => 
        )

)

Get Record

<?php

        include 'adodb.inc.php';
        $host = 'localhost';
        $user = 'root';
        $pass = 'caojiang';
        $dbname = 'w3cyberlearnings';

        $conn1 = &ADONewConnection('mysql');
        $conn1->PConnect($host, $user, $pass, $dbname);

        $sql = "SELECT * FROM profile";

        $rs = $conn1->Execute($sql);
        foreach($rs as $row) {
            print_r($row);
        }
?>

Return Record

<?php

        include 'adodb.inc.php';
        $host = 'localhost';
        $user = 'root';
        $pass = 'caojiang';
        $dbname = 'w3cyberlearnings';

        $conn1 = &ADONewConnection('mysql');
        $conn1->PConnect($host, $user, $pass, $dbname);

        $sql = "SELECT * FROM profile";

        $rs = $conn1->Execute($sql);
        if(!$rs) {
             print $conn1->ErrorMsg();
        }
        else {
           while(!$rs->EOF) {
                echo $rs->fields[0]. ' '. $rs->fields[1].' '. $rs->fields[2] . '<BR>';
                $rs->MoveNext();
        }
}

$rs->close();
$conn1->close();
?>

Result

1 Bob 24
2 Janny 24
3 Sopka 24
4 Jinny 28
5 Horny 24
6 Iinny 28

Return Record With Associate Array

<?php
include 'adodb.inc.php';

$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'mytestdatabase';

$conn1 = &ADONewConnection('mysql');
$conn1->PConnect($host, $user, $pass, $dbname);

$sql = "SELECT * FROM profile";
$conn1->SetFetchMode(ADODB_FETCH_ASSOC);
$rs = $conn1->Execute($sql);

if (!$rs) {
    print $conn1->ErrorMsg();
} else {
    foreach ($rs as $r) {
        echo $r['id'] . ' ' . $r['name'] . ' ' . $r['age'] . '<BR>';
    }
}

$rs->close();
$conn1->close();
?>

Use placeholder for SELECT statement

<?php
include 'adodb.inc.php';

$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'mytestdatabase';

$conn1 = &ADONewConnection('mysql');
$conn1->PConnect($host, $user, $pass, $dbname);

$sql = "SELECT * FROM profile age=? OR id=?";
$conn1->SetFetchMode(ADODB_FETCH_ASSOC);
$rs = $conn1->Execute($sql,array(24,6));

if (!$rs) {
    print $conn1->ErrorMsg();
} else {
    foreach ($rs as $r) {
        echo $r['id'] . ' ' . $r['name'] . ' ' . $r['age'] . '<BR>';
    }
}

$rs->close();
$conn1->close();
?>