PHP ADODB Tutorial
From w3cyberlearnings
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();
?>
Related Links
- PHP ADODB Tutorial
- PHP ADODB Connect
- PHP ADODB Create Table
- PHP ADODB Insert Records with Place Holder
