I wanted to learn pear, but the few posts I saw online have a very high evaluation of adodb, so I learned this instead.
There are several advantages of ADODB (I said online, not what I said):
1. The speed is twice as fast as the pearl;
2. There are many more database types supported than pear, and they can even support ACCESS;
3. No installation is required, no server support is required (this is very important for beginners)
If you don’t know what adodb is or if you want to download adodb, you can go to this link to see: /class/
In addition, if any brother translated the full text of README or knew where there was a translation, please reply to me, thank you.
Tutorial
Example 1: Select Statement
Task: Connect to an Access database named Northwind, displaying the first two fields of each record.
In this example, we created a new ADOC connection (ADOConnection) object and used it to connect to a database. This connection uses the PConnect method, which is a persistent connection. When we want to query the database, we can call the Execute() function of this connection at any time. It will return an ADORcordSet object which is actually a cursor that holds the current row in the array fields[]. We use MoveNext() to move from one record to the next.
NB: There is a very practical function SelectLimit not used in this example. It can control the number of records displayed (such as only the first ten records are displayed, which can be used as pagination display).
PHP:--------------------------------------------------------------------------------
<?
include(''); #Load ADOdb
$conn= &ADONewConnection('access'); # Create a new connection
$conn->PConnect('northwind'); #Connect to an MS-Access database named northwind
$recordSet = &$conn->Execute('select * from products'); #Search all data from the products data table
if (!$recordSet)
print $conn->ErrorMsg(); //If an error occurs in data search, it will be displayed.
else
while (!$recordSet->EOF) {
print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
$recordSet->MoveNext(); //Point to the next record
} //The list displays data
$recordSet->Close(); //Optional
$conn->Close(); //Optional
?>
--------------------------------------------------------------------------------
$recordSet returns the current array in $recordSet->fields, and numeric indexes the fields (starting from 0). We use the MoveNext() function to move to the next record. When the database searches to the end, EOF property is set to true. If an error occurs in Execute(), recordset returns flase.
The $recordSet->fields[] array is generated from the PHP database extension. Some database extensions can only be indexed by numbers but not by field names. If you insist on using field names indexing, you should use the SetFetchMode function. Regardset can be created by Execute() or SelectLimit() regardless of the format indexing.
PHP:--------------------------------------------------------------------------------
$db->SetFetchMode(ADODB_FETCH_NUM);
$rs1 = $db->Execute('select * from table'); //Use numerical index
$db->SetFetchMode(ADODB_FETCH_ASSOC);
$rs2 = $db->Execute('select * from table'); //Use field name index
print_r($rs1->fields); # shows array([0]=>'v0',[1] =>'v1')
print_r($rs2->fields); # shows array(['col1']=>'v0',['col2'] =>'v1')--------------------------------------------------------------------------------
If you want to get the record number, you can use $recordSet->RecordCount(). Returns -1 if there is no current record.
Example 2: Advanced Select with Field Objects
Search the table to display the first two fields. If the second field is in time or date format, change it to US Standard Time format to display.
PHP:--------------------------------------------------------------------------------
<?
include(''); ///Load adodb
$conn= &ADONewConnection('access'); //Create a new connection
$conn->PConnect('northwind'); //Connect the MS-Access database named northwind
$recordSet = &$conn->Execute('select CustomerID,OrderDate from Orders'); //Search for CustomerID and OrderDate fields from the Orders table
if (!$recordSet)
print $conn->ErrorMsg(); //If the database search is wrong, the error message will be displayed
else
while (!$recordSet->EOF) {
$fld = $recordSet->FetchField(1); //Assign the second field to $fld
$type = $recordSet->MetaType($fld->type); //Get the format of field value
if ( $type == 'D' || $type == 'T')
print $recordSet->fields[0].' '.
$recordSet->UserDate($recordSet->fields[1],'m/d/Y').'<BR>'; //If the field format is date or time type, make it output in US standard format
else
print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>'; //Otherwise, output as is
$recordSet->MoveNext(); //Point to the next record
}
$recordSet->Close(); //Optional
$conn->Close(); //Optional
?>
--------------------------------------------------------------------------------
In this example, we check the format of the second field with the FetchField() function. It returns an object containing three variables
name: field name
type: The real format of the field in its database
max_length: The maximum length of the field, some databases will not return this value, such as MYSQL, in which case the max_length value is equal to -1.
We use MetaType() to convert the database format of the field into a standard field format
C: Character-type field, it should be displayed under the <input type="text"> tag.
X: Text-type fields, which store relatively large text, generally act on the <textarea> tag
B: Block, large objects in binary format, such as picture
D: Date field
T: Time-type field
L: Logical field (Bolean logic or bit-field)
I: Integer field
N: numeric fields. Including automatic numbering, numeric, floating point number, real number and integer.
R: Continuous fields. Including serial, autoincrement integers. It can only work in the specified database.
If metatype is of date or time stamp type, we use the user-defined date format UserDate() function to output it. UserDate() is used to convert the PHP SQL date string format to the user-defined format. Another usage of MetaType() is to confirm the validity of the data before insertion and replacement.
Example 3: Inserting
Insert a record containing date and character data into the order data table. The conversion must be performed before inserting. eg: the single-quote in the word John's.
PHP:--------------------------------------------------------------------------------
<?
include(''); //Load adodb
$conn= &ADONewConnection('access'); //Create a new connection
$conn->PConnect('northwind'); //Connect to ACCESS database northwind
$shipto = $conn->qstr("John's Old Shoppe");
$sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
$sql .= "values ('ANATR',2,".$conn->DBDate(time()).",$shipto)";
if ($conn->Execute($sql) === false) {
print 'error inserting: '.$conn->ErrorMsg().'<BR>';
} //If the insertion fails to succeed, output error message
?>
--------------------------------------------------------------------------------
In this example, we see that ADOdb can easily handle some advanced database operations. unix timestamp (a long integer) is converted by DBDate() into the correct Access format, and the right escape character is used for quoting the John's Old Shoppe, which is John's Old Shoppe and not PHP's default John's Old Shoppe with qstr().
Observe the error handling of the execution statement. If an error occurs in Execute(), the ErrorMsg() function will return the last error prompt. Note: php_track_errors might have to be enabled for error messages to be saved.
Example 4: Debugging
<?
include(''); //Load adodb
$conn= &ADONewConnection('access'); //Create a new connection
$conn->PConnect('northwind'); //Connect to ACCESS database northwind
$shipto = $conn->qstr("John's Old Shoppe");
$sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
$sql .= "values ('ANATR',2,".$conn->FormatDate(time()).",$shipto)";
$conn->debug = true;
if ($conn->Execute($sql) === false) print 'error inserting';
?>
In the above example, we set debug = true. It will display all SQL information before execution, and at the same time, it will also display all error messages. In this example, we no longer need to call ErrorMsg(). To display recordset, you can refer to the rs2html() instance.
You can also refer to the content of Custom Error Handlers.
Example 5: MySQL and Menus
Connect to the MySQL database agora, and generate a <select> drop-down menu from the SQL declaration. The menu's <option> option is displayed as the first field and the return value is the second field.
PHP:--------------------------------------------------------------------------------
<?
include(''); # load code common to ADOdb
$conn = &ADONewConnection('mysql'); //eate a connection
$conn->PConnect('localhost','userid','','agora'); //SQL database, database name is agora
$sql = 'select CustomerName, CustomerID from customers'; //Search field name is used for display, and id is used for return value
$rs = $conn->Execute($sql);
print $rs->GetMenu('GetCust','Mary Rosli'); //Show menu
?>
--------------------------------------------------------------------------------
Here we define a menu called GetCust, where 'Mary Rosli' is selected. See GetMenu(). We also have a function that returns the record value to the array: GetArray(), and an associative array with the key being the first column: GetAssoc().
Example 6: Connecting to 2 Databases At Once
PHP:--------------------------------------------------------------------------------
<?
include(''); # load code common to ADOdb
$conn1 = &ADONewConnection('mysql'); # create a mysql connection
$conn2 = &ADONewConnection('oracle'); # create a oracle connection
$conn1->PConnect($server, $userid, $password, $database);
$conn2->PConnect(false, $ora_userid, $ora_pwd, $oraname);
$conn1->Execute('insert ...');
$conn2->Execute('update ...');
?> //Connect two databases at the same time
--------------------------------------------------------------------------------
7: Generating Update and Insert SQL
ADOdb 1.31 or above supports two new functions: GetUpdateSQL( ) and GetInsertSQL( ). This allows you to perform a "SELECT * FROM table query WHERE...", make a copy of the $rs->fields, modify the fields, and then generate the SQL to update or insert into the table automatically.
Let's take a look at how these two functions are executed in this worksheet: (ID, FirstName, LastName, Created).
Before these functions can be called, you need to initialize the recordset by performing a select on the table. Idea and code by Jonathan Younger jyounger#.
PHP:--------------------------------------------------------------------------------
<?
#==============================================
# SAMPLE GetUpdateSQL() and GetInsertSQL() code
#==============================================
include('');
include(''); // Strange, does this sentence seem to be the same? If anyone knows the reason, please give me an explanation.
#==========================
# This code tests an insert
$sql = "SELECT * FROM ADOXYZ WHERE id = -1"; #Find an empty record$conn = &ADONewConnection("mysql"); # create a connection
$conn->debug=1;
$conn->PConnect("localhost", "admin", "", "test"); # connect to MySQL, testdb
$rs= $conn->Execute($sql); # Get an empty record
$record= array(); # Create an array to prepare for insertion
# Set the insert value $record["firstname"] = "Bob";
$record["lastname"] = "Smith";
$record["created"] = time();
# Pass the empty recordset and the array containing the data to insert
# into the GetInsertSQL function. The function will process the data and return
# a fully formatted insert sql statement.# Variables will be formatted before insertion
$insertSQL = $conn->GetInsertSQL($rs, $record);
$conn->Execute($insertSQL); # Insert data in the database
#==========================
#The following program demonstrates the modification of data, which is roughly the same as the previous program.
$sql = "SELECT * FROM ADOXYZ WHERE id = 1";
# Select a record to update
$rs = $conn->Execute($sql); # Execute the query and get the existing record to update
$record = array(); # Initialize an array to hold the record data to update
# Set the values for the fields in the record
$record["firstname"] = "Caroline";
$record["lastname"] = "Smith"; # Update Caroline's lastname from Miranda to Smith
# Pass the single record recordset and the array containing the data to update
# into the GetUpdateSQL function. The function will process the data and return
# a fully formatted update sql statement with the correct WHERE clause.
# If the data has not changed, no recordset is returned
$updateSQL = $conn->GetUpdateSQL($rs, $record);
$conn->Execute($updateSQL); # Update the record in the database
$conn->Close();
?>
--------------------------------------------------------------------------------
Example 8 Implementing Scrolling with Next and Previous
The following demonstration is a very small page browsing program.
PHP:--------------------------------------------------------------------------------
include_once('../');
include_once('../');
session_start();
$db = NewADOConnection('mysql');
$db->Connect('localhost','root','','xphplens');
$sql = "select * from adoxyz ";
$pager = new ADODB_Pager($db,$sql);
$pager->Render($rows_per_page=5);--------------------------------------------------------------------------------
The result of running the above program is as follows:
|< << >> >|
ID First Name Last Name Date Created
36 Alan Turing Sat 06, Oct 2001
37 Serena Williams Sat 06, Oct 2001
38 Yat Sun Sun Sat 06, Oct 2001
39 Wai Hun See Sat 06, Oct 2001
40 Steven Oey Sat 06, Oct 2001
Page 8/10
Calling the Render($rows) method can display data pages. If you do not enter a value for Render(), the default value of ADODB_Pager is 10 records per page.
You can select to display any field in SQL and define a name for it:
$sql = 'select id as "ID", firstname as "First Name",
lastname as "Last Name", created as "Date Created" from adoxyz';
You can find the above code in adodb/tests/, the ADODB_Pager object is in adodb/. You can add an image and change the color to the ADODB_Pager code. You can display the HTML code by setting $pager->htmlSpecialChars = false.
Some of the code used here was contributed by Iván Oliva and Cornel G.
Example 9: Exporting in CSV or Tab-Delimited Format
We provide some helper functions to export in comma-separated-value (CSV) and tab-delimited formats:
PHP:--------------------------------------------------------------------------------
include_once('/path/to/adodb/');include_once('/path/to/adodb/');
$db = &NewADOConnection('mysql');$db->Connect($server, $userid, $password, $database);$rs = $db->Execute('select fname as "First Name", surname as "Surname" from table');
print "<pre>";print rs2csv($rs); # return a string, CSV formatprint '<hr>'; $rs->MoveFirst(); # note, some databases do not support MoveFirstprint rs2tab($rs,false); # return a string, tab-delimited
# false == suppress field names in first lineprint '<hr>';$rs->MoveFirst();rs2tabout($rs); # send to stdout directly (there is also an rs2csvout function)
print "</pre>";
$rs->MoveFirst();$fp = fopen($path, "w");
if ($fp) { rs2csvfile($rs, $fp); # write to file (there is also an rs2tabfile function)
fclose($fp);}--------------------------------------------------------------------------------
Carriage-returns or newlines are converted to spaces. Field names are returned in the first line of text. Strings containing the delimiter character are quoted with double-quotes. Double-quotes are double-quoted again. This conforms to Excel import and export guide-lines.
All the above functions take as an optional last parameter, $addtitles which defaults to true. When set to false field names in the first line are suppressed.
Example 10: Recordset Filters
Sometimes we want to pre-process all rows in a recordset before we use it. For example, we want to ucwords all text in recordset.
PHP:--------------------------------------------------------------------------------
include_once('adodb/');
include_once('adodb/');
// ucwords() every element in the recordset
function do_ucwords(&$arr,$rs)
{
foreach($arr as $k => $v) {
$arr[$k] = ucwords($v);
}
}
$db = NewADOConnection('mysql');
$db->PConnect('server','user','pwd','db');
$rs = $db->Execute('select ... from table');
$rs = RSFilter($rs,'do_ucwords');--------------------------------------------------------------------------------
The RSFilter function takes 2 parameters, the recordset, and the name of the filter function. It returns the processed recordset scrolled to the first record. The filter function takes two parameters, the current row as an array, and the recordset object. For future compatibility, you should not use the original recordset object.
There are several advantages of ADODB (I said online, not what I said):
1. The speed is twice as fast as the pearl;
2. There are many more database types supported than pear, and they can even support ACCESS;
3. No installation is required, no server support is required (this is very important for beginners)
If you don’t know what adodb is or if you want to download adodb, you can go to this link to see: /class/
In addition, if any brother translated the full text of README or knew where there was a translation, please reply to me, thank you.
Tutorial
Example 1: Select Statement
Task: Connect to an Access database named Northwind, displaying the first two fields of each record.
In this example, we created a new ADOC connection (ADOConnection) object and used it to connect to a database. This connection uses the PConnect method, which is a persistent connection. When we want to query the database, we can call the Execute() function of this connection at any time. It will return an ADORcordSet object which is actually a cursor that holds the current row in the array fields[]. We use MoveNext() to move from one record to the next.
NB: There is a very practical function SelectLimit not used in this example. It can control the number of records displayed (such as only the first ten records are displayed, which can be used as pagination display).
PHP:--------------------------------------------------------------------------------
<?
include(''); #Load ADOdb
$conn= &ADONewConnection('access'); # Create a new connection
$conn->PConnect('northwind'); #Connect to an MS-Access database named northwind
$recordSet = &$conn->Execute('select * from products'); #Search all data from the products data table
if (!$recordSet)
print $conn->ErrorMsg(); //If an error occurs in data search, it will be displayed.
else
while (!$recordSet->EOF) {
print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
$recordSet->MoveNext(); //Point to the next record
} //The list displays data
$recordSet->Close(); //Optional
$conn->Close(); //Optional
?>
--------------------------------------------------------------------------------
$recordSet returns the current array in $recordSet->fields, and numeric indexes the fields (starting from 0). We use the MoveNext() function to move to the next record. When the database searches to the end, EOF property is set to true. If an error occurs in Execute(), recordset returns flase.
The $recordSet->fields[] array is generated from the PHP database extension. Some database extensions can only be indexed by numbers but not by field names. If you insist on using field names indexing, you should use the SetFetchMode function. Regardset can be created by Execute() or SelectLimit() regardless of the format indexing.
PHP:--------------------------------------------------------------------------------
$db->SetFetchMode(ADODB_FETCH_NUM);
$rs1 = $db->Execute('select * from table'); //Use numerical index
$db->SetFetchMode(ADODB_FETCH_ASSOC);
$rs2 = $db->Execute('select * from table'); //Use field name index
print_r($rs1->fields); # shows array([0]=>'v0',[1] =>'v1')
print_r($rs2->fields); # shows array(['col1']=>'v0',['col2'] =>'v1')--------------------------------------------------------------------------------
If you want to get the record number, you can use $recordSet->RecordCount(). Returns -1 if there is no current record.
Example 2: Advanced Select with Field Objects
Search the table to display the first two fields. If the second field is in time or date format, change it to US Standard Time format to display.
PHP:--------------------------------------------------------------------------------
<?
include(''); ///Load adodb
$conn= &ADONewConnection('access'); //Create a new connection
$conn->PConnect('northwind'); //Connect the MS-Access database named northwind
$recordSet = &$conn->Execute('select CustomerID,OrderDate from Orders'); //Search for CustomerID and OrderDate fields from the Orders table
if (!$recordSet)
print $conn->ErrorMsg(); //If the database search is wrong, the error message will be displayed
else
while (!$recordSet->EOF) {
$fld = $recordSet->FetchField(1); //Assign the second field to $fld
$type = $recordSet->MetaType($fld->type); //Get the format of field value
if ( $type == 'D' || $type == 'T')
print $recordSet->fields[0].' '.
$recordSet->UserDate($recordSet->fields[1],'m/d/Y').'<BR>'; //If the field format is date or time type, make it output in US standard format
else
print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>'; //Otherwise, output as is
$recordSet->MoveNext(); //Point to the next record
}
$recordSet->Close(); //Optional
$conn->Close(); //Optional
?>
--------------------------------------------------------------------------------
In this example, we check the format of the second field with the FetchField() function. It returns an object containing three variables
name: field name
type: The real format of the field in its database
max_length: The maximum length of the field, some databases will not return this value, such as MYSQL, in which case the max_length value is equal to -1.
We use MetaType() to convert the database format of the field into a standard field format
C: Character-type field, it should be displayed under the <input type="text"> tag.
X: Text-type fields, which store relatively large text, generally act on the <textarea> tag
B: Block, large objects in binary format, such as picture
D: Date field
T: Time-type field
L: Logical field (Bolean logic or bit-field)
I: Integer field
N: numeric fields. Including automatic numbering, numeric, floating point number, real number and integer.
R: Continuous fields. Including serial, autoincrement integers. It can only work in the specified database.
If metatype is of date or time stamp type, we use the user-defined date format UserDate() function to output it. UserDate() is used to convert the PHP SQL date string format to the user-defined format. Another usage of MetaType() is to confirm the validity of the data before insertion and replacement.
Example 3: Inserting
Insert a record containing date and character data into the order data table. The conversion must be performed before inserting. eg: the single-quote in the word John's.
PHP:--------------------------------------------------------------------------------
<?
include(''); //Load adodb
$conn= &ADONewConnection('access'); //Create a new connection
$conn->PConnect('northwind'); //Connect to ACCESS database northwind
$shipto = $conn->qstr("John's Old Shoppe");
$sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
$sql .= "values ('ANATR',2,".$conn->DBDate(time()).",$shipto)";
if ($conn->Execute($sql) === false) {
print 'error inserting: '.$conn->ErrorMsg().'<BR>';
} //If the insertion fails to succeed, output error message
?>
--------------------------------------------------------------------------------
In this example, we see that ADOdb can easily handle some advanced database operations. unix timestamp (a long integer) is converted by DBDate() into the correct Access format, and the right escape character is used for quoting the John's Old Shoppe, which is John's Old Shoppe and not PHP's default John's Old Shoppe with qstr().
Observe the error handling of the execution statement. If an error occurs in Execute(), the ErrorMsg() function will return the last error prompt. Note: php_track_errors might have to be enabled for error messages to be saved.
Example 4: Debugging
<?
include(''); //Load adodb
$conn= &ADONewConnection('access'); //Create a new connection
$conn->PConnect('northwind'); //Connect to ACCESS database northwind
$shipto = $conn->qstr("John's Old Shoppe");
$sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
$sql .= "values ('ANATR',2,".$conn->FormatDate(time()).",$shipto)";
$conn->debug = true;
if ($conn->Execute($sql) === false) print 'error inserting';
?>
In the above example, we set debug = true. It will display all SQL information before execution, and at the same time, it will also display all error messages. In this example, we no longer need to call ErrorMsg(). To display recordset, you can refer to the rs2html() instance.
You can also refer to the content of Custom Error Handlers.
Example 5: MySQL and Menus
Connect to the MySQL database agora, and generate a <select> drop-down menu from the SQL declaration. The menu's <option> option is displayed as the first field and the return value is the second field.
PHP:--------------------------------------------------------------------------------
<?
include(''); # load code common to ADOdb
$conn = &ADONewConnection('mysql'); //eate a connection
$conn->PConnect('localhost','userid','','agora'); //SQL database, database name is agora
$sql = 'select CustomerName, CustomerID from customers'; //Search field name is used for display, and id is used for return value
$rs = $conn->Execute($sql);
print $rs->GetMenu('GetCust','Mary Rosli'); //Show menu
?>
--------------------------------------------------------------------------------
Here we define a menu called GetCust, where 'Mary Rosli' is selected. See GetMenu(). We also have a function that returns the record value to the array: GetArray(), and an associative array with the key being the first column: GetAssoc().
Example 6: Connecting to 2 Databases At Once
PHP:--------------------------------------------------------------------------------
<?
include(''); # load code common to ADOdb
$conn1 = &ADONewConnection('mysql'); # create a mysql connection
$conn2 = &ADONewConnection('oracle'); # create a oracle connection
$conn1->PConnect($server, $userid, $password, $database);
$conn2->PConnect(false, $ora_userid, $ora_pwd, $oraname);
$conn1->Execute('insert ...');
$conn2->Execute('update ...');
?> //Connect two databases at the same time
--------------------------------------------------------------------------------
7: Generating Update and Insert SQL
ADOdb 1.31 or above supports two new functions: GetUpdateSQL( ) and GetInsertSQL( ). This allows you to perform a "SELECT * FROM table query WHERE...", make a copy of the $rs->fields, modify the fields, and then generate the SQL to update or insert into the table automatically.
Let's take a look at how these two functions are executed in this worksheet: (ID, FirstName, LastName, Created).
Before these functions can be called, you need to initialize the recordset by performing a select on the table. Idea and code by Jonathan Younger jyounger#.
PHP:--------------------------------------------------------------------------------
<?
#==============================================
# SAMPLE GetUpdateSQL() and GetInsertSQL() code
#==============================================
include('');
include(''); // Strange, does this sentence seem to be the same? If anyone knows the reason, please give me an explanation.
#==========================
# This code tests an insert
$sql = "SELECT * FROM ADOXYZ WHERE id = -1"; #Find an empty record$conn = &ADONewConnection("mysql"); # create a connection
$conn->debug=1;
$conn->PConnect("localhost", "admin", "", "test"); # connect to MySQL, testdb
$rs= $conn->Execute($sql); # Get an empty record
$record= array(); # Create an array to prepare for insertion
# Set the insert value $record["firstname"] = "Bob";
$record["lastname"] = "Smith";
$record["created"] = time();
# Pass the empty recordset and the array containing the data to insert
# into the GetInsertSQL function. The function will process the data and return
# a fully formatted insert sql statement.# Variables will be formatted before insertion
$insertSQL = $conn->GetInsertSQL($rs, $record);
$conn->Execute($insertSQL); # Insert data in the database
#==========================
#The following program demonstrates the modification of data, which is roughly the same as the previous program.
$sql = "SELECT * FROM ADOXYZ WHERE id = 1";
# Select a record to update
$rs = $conn->Execute($sql); # Execute the query and get the existing record to update
$record = array(); # Initialize an array to hold the record data to update
# Set the values for the fields in the record
$record["firstname"] = "Caroline";
$record["lastname"] = "Smith"; # Update Caroline's lastname from Miranda to Smith
# Pass the single record recordset and the array containing the data to update
# into the GetUpdateSQL function. The function will process the data and return
# a fully formatted update sql statement with the correct WHERE clause.
# If the data has not changed, no recordset is returned
$updateSQL = $conn->GetUpdateSQL($rs, $record);
$conn->Execute($updateSQL); # Update the record in the database
$conn->Close();
?>
--------------------------------------------------------------------------------
Example 8 Implementing Scrolling with Next and Previous
The following demonstration is a very small page browsing program.
PHP:--------------------------------------------------------------------------------
include_once('../');
include_once('../');
session_start();
$db = NewADOConnection('mysql');
$db->Connect('localhost','root','','xphplens');
$sql = "select * from adoxyz ";
$pager = new ADODB_Pager($db,$sql);
$pager->Render($rows_per_page=5);--------------------------------------------------------------------------------
The result of running the above program is as follows:
|< << >> >|
ID First Name Last Name Date Created
36 Alan Turing Sat 06, Oct 2001
37 Serena Williams Sat 06, Oct 2001
38 Yat Sun Sun Sat 06, Oct 2001
39 Wai Hun See Sat 06, Oct 2001
40 Steven Oey Sat 06, Oct 2001
Page 8/10
Calling the Render($rows) method can display data pages. If you do not enter a value for Render(), the default value of ADODB_Pager is 10 records per page.
You can select to display any field in SQL and define a name for it:
$sql = 'select id as "ID", firstname as "First Name",
lastname as "Last Name", created as "Date Created" from adoxyz';
You can find the above code in adodb/tests/, the ADODB_Pager object is in adodb/. You can add an image and change the color to the ADODB_Pager code. You can display the HTML code by setting $pager->htmlSpecialChars = false.
Some of the code used here was contributed by Iván Oliva and Cornel G.
Example 9: Exporting in CSV or Tab-Delimited Format
We provide some helper functions to export in comma-separated-value (CSV) and tab-delimited formats:
PHP:--------------------------------------------------------------------------------
include_once('/path/to/adodb/');include_once('/path/to/adodb/');
$db = &NewADOConnection('mysql');$db->Connect($server, $userid, $password, $database);$rs = $db->Execute('select fname as "First Name", surname as "Surname" from table');
print "<pre>";print rs2csv($rs); # return a string, CSV formatprint '<hr>'; $rs->MoveFirst(); # note, some databases do not support MoveFirstprint rs2tab($rs,false); # return a string, tab-delimited
# false == suppress field names in first lineprint '<hr>';$rs->MoveFirst();rs2tabout($rs); # send to stdout directly (there is also an rs2csvout function)
print "</pre>";
$rs->MoveFirst();$fp = fopen($path, "w");
if ($fp) { rs2csvfile($rs, $fp); # write to file (there is also an rs2tabfile function)
fclose($fp);}--------------------------------------------------------------------------------
Carriage-returns or newlines are converted to spaces. Field names are returned in the first line of text. Strings containing the delimiter character are quoted with double-quotes. Double-quotes are double-quoted again. This conforms to Excel import and export guide-lines.
All the above functions take as an optional last parameter, $addtitles which defaults to true. When set to false field names in the first line are suppressed.
Example 10: Recordset Filters
Sometimes we want to pre-process all rows in a recordset before we use it. For example, we want to ucwords all text in recordset.
PHP:--------------------------------------------------------------------------------
include_once('adodb/');
include_once('adodb/');
// ucwords() every element in the recordset
function do_ucwords(&$arr,$rs)
{
foreach($arr as $k => $v) {
$arr[$k] = ucwords($v);
}
}
$db = NewADOConnection('mysql');
$db->PConnect('server','user','pwd','db');
$rs = $db->Execute('select ... from table');
$rs = RSFilter($rs,'do_ucwords');--------------------------------------------------------------------------------
The RSFilter function takes 2 parameters, the recordset, and the name of the filter function. It returns the processed recordset scrolled to the first record. The filter function takes two parameters, the current row as an array, and the recordset object. For future compatibility, you should not use the original recordset object.