Database Access

It is not necessary in Andromeda to make a connection to the database, Andromeda makes a connection on every trip to the server. When the user supplies their use_id and password, these are used to make the connection for you. The simplest way to access the database is with direct SQL commands:

<?php
# This is file public_items
class public_items extends x_table2 {
    function main() {
        $items=SQL("Select sku,description from items order by sku");
        foreach($items as $item) {
            echo "The item is: ".$item['sku'].', '.$item['description'].'br/>';
        }
    }
}
?>

Special Insert and Update Commands

Andromeda provides a handful of very useful commands that are detailed in our reference section. The command SQLX_Insert accepts a table name and an associative array. When combined with commands to process GET-POST Variables you can have some very simple code:

<?php
# This is file public_items
class public_items extends x_table2 {
    function main() {
        if(gp('postflag')==1) {
            if ($this->ProcesPost()) return;
        }
        hidden('gp_page','public_items');
        hidden('postflag','1');
        ?>
        Column 1: <input name="inp_column1">
        <br/>
        Column 2: <input name="inp_column2">
        <br/>
        Column 3: <input name="inp_column3">
        <br/>
        <?php
    }
    
    function processPost() {
        // The database enforces all rules, so we just
        // grab the vars and do the insert.
        $row=aFromgp('inp_');
        SQLX_Insert('sometable',$row);
        
        // False tells the main() routine not to stop, but
        // to display the form again.  Errors will be displayed
        // automatically.
        if(Errors()) return false;
        ?>
        Values have been saved!
        <?php
    }
}
?>

There are also the commands SQLX_Update, and the very handy SQLX_UpdateorInsert, which "knows what you mean" when handling data that may need to be inserted or updated. These and other commands are detailed in Specialized SQL Commands.

When Coding Straight SQL

When coding straight SQL, which is often the simplest way to code queries, there is a special step you must take if you are using either row-level or column-level security. When row or column security is used, the base tables are off limits and users must access views instead. The particular view a person uses depends upon who they are, so you cannot hardcode the view name. The way to deal with this is to use the framework function ddTable_idResolve to find out which view you need. So a straight pull would use:

<?php
function main() {
    $viewid = DDTable_idResolve('employees');
    $employees=SQL_AllRows("SELECT * FROM $viewid");
}
?>