Revolution Noob - Databases

[Trevor DeVore]

On Dec 6, 2005, at 7:18 PM, Adam wrote:

> Hello everyone:

Hi Adam.

> I have converted most of my functions to Revolution but I have one  
> function that is also the most important to the scripts that I have  
> yet to figure out how to convert.

Before I get into how to accomplish what you want to do I am going to  
discuss data manipulation for a second since you mention array  
intersection in your pseudo code.  I imagine you are used to dealing  
with arrays and objects in PHP.

Before stumbling upon Revolution I programmed web apps with PHP.   
When I started using Revolution I had a tough time working with  
arrays.  In PHP I used arrays for all sorts of data storage and  
manipulation because there was such a rich set of array functions.   
Transcript does not offer the same sort array manipulation that PHP  
does so I was often frustrated when I tried to accomplish something  
with arrays in Transcript the same way I would have done it in PHP.

What Transcript does excel in is chunk expressions (chars, items,  
words, lines, etc.).  Once I got this concept through my thick skull,  
data manipulation became much easier in Revolution.

Alright, onto the code...

> For those who may not have used PHP, I'll use pseudo-code.

In the examples below I'm going to provide you the raw Transcript  
commands for dealing with databases.  If you are going to work with  
databases quite a bit in Revolution you may want to take a look at my  
libDatabase library.  Revolution has some very low level database  
abstraction handlers that don't go far enough in making databases  
integrate seamlessly with Transcript IMO.

libDatabase handles the dirty work of connection, fetching, adding  
and updating records among other things.  I based the library off of  
my PHP app framework db code which was a abstraction layer sitting on  
top of the ADOdb library.  I spend the majority of my days working in  
Rev and databases and I've found that the library saves me lots of  
time.  You can get the library here:

<http://www.mangomultimedia.com/developer/revolution/>

Use the 2.x beta version.  I'm using it in commercial applications.

> Connect to database

Check out revOpenDatabase in the docs.  If you were connecting to a  
MySQL database on the localhost you would do something like this:

put revOpenDatabase("mysql", "localhost", "MyDatabase", "root", "")  
into myConnectionID

myConnectionID is the database connection id (integer) that you will  
use when querying the database.

> Start with the first record in the the record set

To run the query you can do this -

put revQueryDatabase(myConnectionID, "SELECT ID, Value1, Value2 FROM  
MyTable") into myCursor

There is no array_intersect handler in Transcript so you will have to  
approach this part a little differently.  Depending on the type of  
data you are storing you could do something like this:

--> SET EVERYTHING UP
set the wholeMatches to true
put 0 into tMatches
put 1 into tMin
put 2 into tMax

put "FindValue1,FindValue2,FindValue3" into tListOfItemsToFind

--> LOOP THROUGH RETURNED RECORDS
repeat while revCurrentRecordIsLast(myCursor) is false
   --> Copy the record's field values into an array
   --> Intersect the created array with an array of values to find
   --> SEE IF EACH FIELD VALUE IS IN THE tListOfItemsToFind LIST
   repeat for each item tField in "Value1,Value2"
     if revDatabaseColumnNamed(myCursor, tField) is among the items  
of tListOfItemsToFind then
       add 1 to tMatches
     end if
   end repeat

   --> Count the number of values in the resultant array
   --> If the count value is < x or > y add the record id to an array
   --> STORE RECORD IDS TO DELETE IN A COMMA DELIMITED LIST
   if tMatches < tMin OR tMatches > tMax then
     put revDatabaseColumnNamed(myCursor, "ID") &comma after  
tIDsToDelete
   end if

   --> RESET COUNTER
   put 0 into tMatches

   --> MOVE TO NEXT RECORD
   revMoveToNextRecord myCursor
end repeat

> After the last record has been processed delete all the records  
> contained in the array of record ids

--> tIDsToDelete NOW CONTAINS LIST OF RECORD IDS TO DELETE
if tIDsToDelete is not empty
   delete the last char of tIDsToDelete --> GET RID OF TRAILING COMMA

   --> EXECUTE DELETE STATEMENT
   revExecuteSQL myConnectionID, format("DELETE FROM MyTable WHERE ID  
IN(%s)", tIDsToDelete)
end if

> I'm not sure if that'll make sense to anyone, but any help and  
> guidance you may be able to provide is greatly appreciated.

Hopefully I understood what you were looking for and that this makes  
sense.


-- 
Trevor DeVore
Blue Mango Multimedia
trevor at mangomultimedia.com