CDE Examples

The examples below cover most practical uses of the CDE with PHP. In most cases all one needs to know is how to change to a different database.

In most cases the convention of the database path is hostname:path. SQLite and DBase differ from this as they access a file as apposed to a service on a computer.

//SQLite Database Connection  
global $CDE;    
$CDE = new CDESimple ($_CDE_DATABASE="database/mydatabase.db", 
                      $_CDE_USERNAME="", 
                      $_CDE_PASSWORD="", 
                      "sqlite");
$CDE->debug = true;
$CDE->tmppath="/tmp/"; //sqlite needs the temporary workspace

//Dbase Database Connection  
global $CDE;    
$CDE = new CDESimple ($_CDE_DATABASE="database/mydbf.dbf", 
                      $_CDE_USERNAME="", 
                      $_CDE_PASSWORD="", 
                      "dbase");
$CDE->debug = true;

//Firebird Database Connection  
global $CDE;    
$CDE = new CDESimple ($_CDE_DATABASE="127.0.0.1:/home/firebird/MYFIREBIRD.FDB", 
                      $_CDE_USERNAME="SYSDBA", 
                      $_CDE_PASSWORD="masterkey", 
                      "firebird");
$CDE->debug = true;  

//MySQL Database Connection  
global $CDE;    
$CDE = new CDESimple ($_CDE_DATABASE="127.0.0.1:mysql", 
                      $_CDE_USERNAME="root", 
                      $_CDE_PASSWORD="", 
                      "mysql");
$CDE->debug = true;

//MySQL Database Connection  
global $CDE;    
$CDE = new CDESimple ($_CDE_DATABASE="127.0.0.1:ORACLECONTEXT", 
                      $_CDE_USERNAME="oracle", 
                      $_CDE_PASSWORD="", 
                      "oracle");
$CDE->debug = true;

Probably the most important and most used feature will be to retrieve records from a database. The act of retrieving records in CDE also populates the field information of the record being retrieved so that one can dynamically iterate records on screen in a table if needed. Make sure you have you database connection $CDE variable declared globally for easy use in your whole application. This example assumes that you have created a connection to the database already.

//Query to get all people
$sql = "select * 
          from tblperson";
  
$persons = $CDE->get_row ($sql); // Default return an object set with field values in uppercase

//Display any errors
if ($CDE->error != "")
{
  echo "<pre>";
  print_r ($CDE->error);
  echo "</pre>";
}

//Display the result set
echo "<pre>";
print_r ($persons);
echo "</pre>";
  
//Display in a table
echo "<table>";
echo "<tr><th> First Name </th><th> Last Name </th></tr>";
foreach ($persons as $id => $record)
{
   echo "<tr><td> $record->FIRSTNAME </td><td> $record->LASTNAME </td></tr>";
}
echo "</table>";
  
//Some other useful things based on the above get_row

$thirdperson = $CDE->get_value (2); // Get the third person, remember the array is 0 indexed

echo "<pre>";
echo "No of rows retrieved ".$CDE->affectedrows;
echo "</pre>";  

echo "<pre>";
echo "No of fields retrieved ".$CDE->nooffields;
echo "</pre>";  

//Show the field information for use with dynamic output
echo "<pre>";
print_r ($CDE->fieldinfo);
echo "</pre>";  

And that's it in a nutshell. You'll be connecting to many databases from the same application using the same syntax. I just suffix the database type when I connect to different databases, for instance my Firebird database handle will be $CDEFB and the MySQL database handle will be $CDEMY.

One of the things I liked about the Firebird PHP library was the ability to pass parameters to the SQL statement, now you don't have to use this but every now and then it makes for clean SQL coding and you may like it.

//Example of a normal update
$CDE->exec ("update tblperson set firstname = 'Andre', lastname = 'van Zuydam' where id = 0");
$CDE->commit(); //Commit the transaction on a transactional database like Postgres, Firebird, Oracle, MSSQL

//Example of a parameter update
$CDE->exec ("update tblperson set firstname = ?, lastname = ? where id = ?", "Andre", "van Zuydam", 0);
$CDE->commit();

Now the above examples may not look so different but if it is a complicated SQL statement the second way with parameters comes out cleaner. Another thing you may not want to worry about is passing binary data into a blob and for this reason I use a parameter.

//Example of uploading a file
$CDE->exec ("update tblfile set fileimage = ? where id = 0", file_get_contents("/tmp/somefile.bin"));
$CDE->commit();  

And that should be sufficient to get one started on executing queries to the database.