ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools

Trace:
developing

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
Last revisionBoth sides next revision
developing [2015/07/06 05:53] – created mnewnhamdeveloping [2015/07/12 17:13] – mnewnham
Line 1: Line 1:
 ====== Creating Applications With ADOdb ======  ====== Creating Applications With ADOdb ====== 
 +[[reference:Reference Guide]]
 ===== Example 1: Select Statement ===== ===== Example 1: Select Statement =====
  
Line 8: Line 9:
 NB: A useful function that is not used in this example is SelectLimit, which allows us to limit the number of rows shown. NB: A useful function that is not used in this example is SelectLimit, which allows us to limit the number of rows shown.
  
-<code> +<code php>
 include('adodb.inc.php');    # load code common to ADOdb include('adodb.inc.php');    # load code common to ADOdb
- 
 $conn = &ADONewConnection('access');        # create a connection $conn = &ADONewConnection('access');        # create a connection
- 
 $conn->PConnect('northwind');   # connect to MS-Access, northwind DSN $conn->PConnect('northwind');   # connect to MS-Access, northwind DSN
- 
 $recordSet = &$conn->Execute('select * from products'); $recordSet = &$conn->Execute('select * from products');
- 
 if (!$recordSet)  if (!$recordSet) 
 +    print $conn->ErrorMsg();
 +else while (!$recordSet->EOF) {
 +    print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
 +    $recordSet->MoveNext();
 +}
  
-         print $conn->ErrorMsg(); +/* 
- + * Recordsets and connections are automatically closed at the end of the script 
-else + */
- +
-while (!$recordSet->EOF) { +
- +
-         print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>'; +
- +
-         $recordSet->MoveNext(); +
- +
-} +
- +
 $recordSet->Close(); # optional $recordSet->Close(); # optional
- 
 $conn->Close(); # optional $conn->Close(); # optional
  
  
  
-<code>+</code> 
 The $recordSet returned stores the current row in the $recordSet->fields array, indexed by column number (starting from zero). We use the MoveNext() function to move to the next row. The EOF property is set to true when end-of-file is reached. If an error occurs in Execute(), we return false instead of a recordset. The $recordSet returned stores the current row in the $recordSet->fields array, indexed by column number (starting from zero). We use the MoveNext() function to move to the next row. The EOF property is set to true when end-of-file is reached. If an error occurs in Execute(), we return false instead of a recordset.
  
 The $recordSet->fields[] array is generated by the PHP database extension. Some database extensions only index by number and do not index the array by field name. To force indexing by name - that is associative arrays - use the SetFetchMode function. Each recordset saves and uses whatever fetch mode was set when the recordset was created in Execute() or SelectLimit(). The $recordSet->fields[] array is generated by the PHP database extension. Some database extensions only index by number and do not index the array by field name. To force indexing by name - that is associative arrays - use the SetFetchMode function. Each recordset saves and uses whatever fetch mode was set when the recordset was created in Execute() or SelectLimit().
  
-         $db->SetFetchMode(ADODB_FETCH_NUM); +<code php> 
- +$db->SetFetchMode(ADODB_FETCH_NUM); 
-         $rs1 = $db->Execute('select * from table'); +$rs1 = $db->Execute('select * from table'); 
- +$db->SetFetchMode(ADODB_FETCH_ASSOC); 
-         $db->SetFetchMode(ADODB_FETCH_ASSOC); +$rs2 = $db->Execute('select * from table'); 
- +print_r($rs1->fields); # shows array([0]=>'v0',[1] =>'v1') 
-         $rs2 = $db->Execute('select * from table');+print_r($rs2->fields); # shows array(['col1']=>'v0',['col2'] =>'v1') 
 +</code>
  
-         print_r($rs1->fields); # shows array([0]=>'v0',[1] =>'v1') 
-         print_r($rs2->fields); # shows array(['col1']=>'v0',['col2'] =>'v1') 
 To get the number of rows in the select statement, you can use $recordSet->RecordCount(). Note that it can return -1 if the number of rows returned cannot be determined. To get the number of rows in the select statement, you can use $recordSet->RecordCount(). Note that it can return -1 if the number of rows returned cannot be determined.
  
-Example 2: Advanced Select with Field Objects+===== Example 2: Advanced Select with Field Objects =====
  
 Select a table, display the first two columns. If the second column is a date or timestamp, reformat the date to US format. Select a table, display the first two columns. If the second column is a date or timestamp, reformat the date to US format.
  
-<?+<code php>
  
 include('adodb.inc.php');    # load code common to ADOdb include('adodb.inc.php');    # load code common to ADOdb
Line 102: Line 93:
  
  
-?>+</code> 
 In this example, we check the field type of the second column using FetchField(). This returns an object with at least 3 fields. In this example, we check the field type of the second column using FetchField(). This returns an object with at least 3 fields.
  
Line 121: Line 113:
 If the metatype is of type date or timestamp, then we print it using the user defined date format with UserDate(), which converts the PHP SQL date string format to a user defined one. Another use for MetaType() is data validation before doing an SQL insert or update. If the metatype is of type date or timestamp, then we print it using the user defined date format with UserDate(), which converts the PHP SQL date string format to a user defined one. Another use for MetaType() is data validation before doing an SQL insert or update.
  
-Example 3: Inserting+===== Example 3: Inserting =====
  
 Insert a row to the Orders table containing dates and strings that need to be quoted before they can be accepted by the database, eg: the single-quote in the word John's. Insert a row to the Orders table containing dates and strings that need to be quoted before they can be accepted by the database, eg: the single-quote in the word John's.
  
-<? +<code php>
 include('adodb.inc.php');    # load code common to ADOdb include('adodb.inc.php');    # load code common to ADOdb
- 
 $conn = &ADONewConnection('access');        # create a connection $conn = &ADONewConnection('access');        # create a connection
- 
- 
- 
 $conn->PConnect('northwind');   # connect to MS-Access, northwind dsn $conn->PConnect('northwind');   # connect to MS-Access, northwind dsn
- 
 $shipto = $conn->qstr("John's Old Shoppe"); $shipto = $conn->qstr("John's Old Shoppe");
- 
- 
  
 $sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) "; $sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
- 
 $sql .= "values ('ANATR',2,".$conn->DBDate(time()).",$shipto)"; $sql .= "values ('ANATR',2,".$conn->DBDate(time()).",$shipto)";
  
  
 +if ($conn->Execute($sql) === false)
 +    print 'error inserting: '.$conn->ErrorMsg().'<BR>';
  
-if ($conn->Execute($sql) === false) {+</code>
  
-         print 'error inserting: '.$conn->ErrorMsg().'<BR>';+In this example, we see the advanced date and quote handling facilities of ADOdb. The unix timestamp (which is a long integer) is appropriately formatted for Access with DBDate(), 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 Execute statement. False is returned by Execute() if an error occurred. The error message for the last error that occurred is displayed in ErrorMsg(). Note: php_track_errors might have to be enabled for error messages to be saved.
  
-?> +===== Example 4: Debugging =====
-In this example, we see the advanced date and quote handling facilities of ADOdb. The unix timestamp (which is a long integer) is appropriately formated for Access with DBDate(), 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 Execute statement. False is returned by Execute() if an error occured. The error message for the last error that occurred is displayed in ErrorMsg(). Note: php_track_errors might have to be enabled for error messages to be saved. +<code php>
- +
-Example 4: Debugging +
- +
-<?+
  
 include('adodb.inc.php');    # load code common to ADOdb include('adodb.inc.php');    # load code common to ADOdb
- 
 $conn = &ADONewConnection('access');        # create a connection $conn = &ADONewConnection('access');        # create a connection
- 
 $conn->PConnect('northwind');   # connect to MS-Access, northwind dsn $conn->PConnect('northwind');   # connect to MS-Access, northwind dsn
- 
 $shipto = $conn->qstr("John's Old Shoppe"); $shipto = $conn->qstr("John's Old Shoppe");
  
 $sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) "; $sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
- 
 $sql .= "values ('ANATR',2,".$conn->FormatDate(time()).",$shipto)"; $sql .= "values ('ANATR',2,".$conn->FormatDate(time()).",$shipto)";
  
 $conn->debug = true; $conn->debug = true;
 if ($conn->Execute($sql) === false) print 'error inserting'; if ($conn->Execute($sql) === false) print 'error inserting';
-?>+</code> 
 In the above example, we have turned on debugging by setting debug = true. This will display the SQL statement before execution, and also show any error messages. There is no need to call ErrorMsg() in this case. For displaying the recordset, see the rs2html() example. In the above example, we have turned on debugging by setting debug = true. This will display the SQL statement before execution, and also show any error messages. There is no need to call ErrorMsg() in this case. For displaying the recordset, see the rs2html() example.
  
 Also see the section on Custom Error Handlers. Also see the section on Custom Error Handlers.
  
-Example 5: MySQL and Menus+===== Example 5: MySQL and Menus =====
  
 Connect to MySQL database agora, and generate a <select> menu from an SQL statement where the <option> captions are in the 1st column, and the value to send back to the server is in the 2nd column. Connect to MySQL database agora, and generate a <select> menu from an SQL statement where the <option> captions are in the 1st column, and the value to send back to the server is in the 2nd column.
  
-<?+<code php>
  
 include('adodb.inc.php'); # load code common to ADOdb include('adodb.inc.php'); # load code common to ADOdb
Line 197: Line 174:
 print $rs->GetMenu('GetCust','Mary Rosli'); print $rs->GetMenu('GetCust','Mary Rosli');
  
-?>+</code> 
 Here we define a menu named GetCust, with the menu option 'Mary Rosli' selected. See GetMenu(). We also have functions that return the recordset as an array: GetArray(), and as an associative array with the key being the first column: GetAssoc(). Here we define a menu named GetCust, with the menu option 'Mary Rosli' selected. See GetMenu(). We also have functions that return the recordset as an array: GetArray(), and as an associative array with the key being the first column: GetAssoc().
  
-Example 6: Connecting to 2 Databases At Once+===== Example 6: Connecting to 2 Databases At Once =====
  
-<?+<code php>
  
 include('adodb.inc.php');  # load code common to ADOdb include('adodb.inc.php');  # load code common to ADOdb
Line 222: Line 200:
 $conn2->Execute('update ...'); $conn2->Execute('update ...');
  
-?> +</code> 
-Example 7: Generating Update and Insert SQL+ 
 +===== Example 7: Generating Update and Insert SQL =====
  
 Since ADOdb 4.56, we support AutoExecute(), which simplifies things by providing an advanced wrapper for GetInsertSQL() and GetUpdateSQL(). For example, an INSERT can be carried out with: Since ADOdb 4.56, we support AutoExecute(), which simplifies things by providing an advanced wrapper for GetInsertSQL() and GetUpdateSQL(). For example, an INSERT can be carried out with:
  
-  +<code php>  
-    $record["firstname"] = "Bob";  +$record["firstname"] = "Bob";  
-    $record["lastname"] = "Smith";  +$record["lastname"] = "Smith";  
-    $record["created"] = time();  +$record["created"] = time();  
-    $insertSQL = $conn->AutoExecute($rs, $record, 'INSERT'); +$insertSQL = $conn->AutoExecute($rs, $record, 'INSERT');  
 +</code> 
 and an UPDATE with: and an UPDATE with:
-  + 
-    $record["firstname"] = "Caroline";  +<code php>  
-    $record["lastname"] = "Smith"; # Update Caroline's lastname from Miranda to Smith  +$record["firstname"] = "Caroline";  
-    $insertSQL = $conn->AutoExecute($rs, $record, 'UPDATE', 'id = 1'); +$record["lastname"] = "Smith"; # Update Caroline's lastname from Miranda to Smith  
 +$insertSQL = $conn->AutoExecute($rs, $record, 'UPDATE', 'id = 1');  
 +</code> 
 The rest of this section is out-of-date: The rest of this section is out-of-date:
  
Line 245: Line 229:
 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#unilab.com. Since ADOdb 2.42, you can pass a table name instead of a recordset into GetInsertSQL (in $rs), and it will generate an insert statement for that table. 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#unilab.com. Since ADOdb 2.42, you can pass a table name instead of a recordset into GetInsertSQL (in $rs), and it will generate an insert statement for that table.
  
-<?+<code php>
  
 #============================================== #==============================================
Line 357: Line 341:
 $conn->Close(); $conn->Close();
  
-?>+</code> 
 $ADODB_FORCE_TYPE $ADODB_FORCE_TYPE
 The behaviour of AutoExecute(), GetUpdateSQL() and GetInsertSQL() when converting empty or null PHP variables to SQL is controlled by the global $ADODB_FORCE_TYPE variable. Set it to one of the values below. Default is ADODB_FORCE_VALUE (3): The behaviour of AutoExecute(), GetUpdateSQL() and GetInsertSQL() when converting empty or null PHP variables to SQL is controlled by the global $ADODB_FORCE_TYPE variable. Set it to one of the values below. Default is ADODB_FORCE_VALUE (3):
Line 372: Line 357:
  
  
 +<code php>
 define('ADODB_FORCE_IGNORE',0); define('ADODB_FORCE_IGNORE',0);
  
Line 380: Line 365:
  
 define('ADODB_FORCE_VALUE',3); define('ADODB_FORCE_VALUE',3);
 +
 +</code>
 +
 Thanks to Niko (nuko#mbnet.fi) for the $ADODB_FORCE_TYPE code. Thanks to Niko (nuko#mbnet.fi) for the $ADODB_FORCE_TYPE code.
  
Line 386: Line 374:
 Since 4.62, the table name to be used can be overridden by setting $rs->tableName before AutoExecute(), GetInsertSQL() or GetUpdateSQL() is called. Since 4.62, the table name to be used can be overridden by setting $rs->tableName before AutoExecute(), GetInsertSQL() or GetUpdateSQL() is called.
  
-Example 8: Implementing Scrolling with Next and Previous+====== Example 8: Implementing Scrolling with Next and Previous =====
  
 The following code creates a very simple recordset pager, where you can scroll from page to page of a recordset. The following code creates a very simple recordset pager, where you can scroll from page to page of a recordset.
  
 +<code php>
 include_once('../adodb.inc.php'); include_once('../adodb.inc.php');
  
Line 413: Line 402:
  
 $pager->Render($rows_per_page=5); $pager->Render($rows_per_page=5);
 +
 +</code>
 This will create a basic record pager that looks like this: This will create a basic record pager that looks like this:
  
-|<   <<   >>   >|   +|<   <<   >>   >| 
-ID +   
-First Name +^ID^First Name^Last Name^Date Created^ 
-Last Name +|36|Alan|Turing|Sat 06, Oct 2001|  
-Date Created +|37|Serena|Williams|Sat 06, Oct 2001|  
-36  +|38|Yat Sun|Sun|Sat 06, Oct 2001|  
-Alan  +|39|Wai Hun|See|Sat 06, Oct 2001|  
-Turing  +|40|Steven|Oey|Sat 06, Oct 2001| 
-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 Page 8/10
 +
 The number of rows to display at one time is controled by the Render($rows) method. If you do not pass any value to Render(), ADODB_Pager will default to 10 records per page. The number of rows to display at one time is controled by the Render($rows) method. If you do not pass any value to Render(), ADODB_Pager will default to 10 records per page.