src/snac/server/database/SQL.php
/**
     * Select a list by status
     *
     * Select a list of mainID, version for a given status, and most recent. User is ignored therefore we get
     * records from any user. That is: not constrained by user. Optional args for limit and offset allow
     * returning partial lists.
     *
     * We are dynamically building the query string. Use sprintf() in order to get $limiStr and $offsetStr
     * into the query as necessary. Note the care we take in checking that $limit and $offset are
     * integers. Building query strings dynamically is one way that sql injection attacks sneak into code. We
     * are validating for ints and building the string only from trusted data.
     *
     * Use single quotes in $queryStr below. We don't want $1 to be interpolated. Testing php behavior reveals
     * that $1 never interpolates, but heaven only knows if that is in the php language spec. If you want, it
     * also seems fine to escape with \ when using double quotes.
     *
     * @param string $status optional Status defaults to 'published'.
     *
     * @param integer $limit Limit to the number of records. Not optional here. Must be -1 for all, or a
     * number. The higher level calling code has a default from the config.
     *
     * @param integer $offset An offset to jump into the list of records in the database. Not optional
     * here. Must be -1 for all, or a number. The higher level calling code has a default from the config.
     *
     * @return string[] Associative list with keys 'version', 'ic_id'. Values are integers.
     */
Select a list by status
Select a list of mainID, version for a given status, and most recent. User is ignored therefore we get
records from any user. That is: not constrained by user. Optional args for limit and offset allow
returning partial lists.

We are dynamically building the query string. Use sprintf() in order to get $limiStr and $offsetStr
into the query as necessary. Note the care we take in checking that $limit and $offset are
integers. Building query strings dynamically is one way that sql injection attacks sneak into code. We
are validating for ints and building the string only from trusted data.

Use single quotes in $queryStr below. We don't want $1 to be interpolated. Testing php behavior reveals
that $1 never interpolates, but heaven only knows if that is in the php language spec. If you want, it
also seems fine to escape with \ when using double quotes.

@param string $status optional Status defaults to 'published'.

@param integer $limit Limit to the number of records. Not optional here. Must be -1 for all, or a
number. The higher level calling code has a default from the config.

@param integer $offset An offset to jump into the list of records in the database. Not optional
here. Must be -1 for all, or a number. The higher level calling code has a default from the config.

@return string[] Associative list with keys 'version', 'ic_id'. Values are integers.
Select a list by status Select a list of mainID version for a given status and most recent . User is ignored therefore we get records from any user . That is : not constrained by user . Optional args for limit and offset allow returning partial lists . We are dynamically building the query string . Use sprintf () in order to get $limiStr and $offsetStr into the query as necessary . Note the care we take in checking that $limit and $offset are integers . Building query strings dynamically is one way that sql injection attacks sneak into code . We are validating for ints and building the string only from trusted data . Use single quotes in $queryStr below . We don ' t want $1 to be interpolated . Testing php behavior reveals that $1 never interpolates but heaven only knows if that is in the php language spec . If you want it also seems fine to escape with \ when using double quotes . @param string $status optional Status defaults to ' published ' . @param integer $limit Limit to the number of records . Not optional here . Must be - 1 for all or a number . The higher level calling code has a default from the config . @param integer $offset An offset to jump into the list of records in the database . Not optional here . Must be - 1 for all or a number . The higher level calling code has a default from the config . @return string [] Associative list with keys ' version ' ' ic_id ' . Values are integers .
public function selectListByStatus($status = 'published', $limit, $offset)
    {
        $limitStr = '';
        $offsetStr = '';
        $limitStr = $this->doLOValue('limit', $limit);
        $offsetStr = $this->doLOValue('offset', $offset);
        $queryString = sprintf(
            'select aa.version, aa.id as ic_id
                from version_history as aa,
                    (select max(version) as version, id from version_history
                    where id in (select distinct id from version_history where status=$1)
                    group by id) as cc
                where
                    aa.id=cc.id and
                    aa.version=cc.version and
                    aa.status = $1
                order by aa.version desc %s %s', $limitStr, $offsetStr);
        $result = $this->sdb->query($queryString,
                                    array($status));
        $all = array();
        while($row = $this->sdb->fetchrow($result))
        {
            $mainID = $row['ic_id'];
            $version = $row['version'];
            $maxVersion = $this->selectCurrentVersion($mainID);
            if ($maxVersion == $version)
            {
                array_push($all, $row);
            }
        }
        return $all;
    }
public function selectListByStatus ( $ status = 'published' , $ limit , $ offset ) { $ limitStr = '' ; $ offsetStr = '' ; $ limitStr = $ this -> doLOValue ( 'limit' , $ limit ) ; $ offsetStr = $ this -> doLOValue ( 'offset' , $ offset ) ; $ queryString = sprintf ( 'select aa.version, aa.id as ic_id
                from version_history as aa,
                    (select max(version) as version, id from version_history
                    where id in (select distinct id from version_history where status=$1)
                    group by id) as cc
                where
                    aa.id=cc.id and
                    aa.version=cc.version and
                    aa.status = $1
                order by aa.version desc %s %s' , $ limitStr , $ offsetStr ) ; $ result = $ this -> sdb -> query ( $ queryString , array ( $ status ) ) ; $ all = array ( ) ; while ( $ row = $ this -> sdb -> fetchrow ( $ result ) ) { $ mainID = $ row [ 'ic_id' ] ; $ version = $ row [ 'version' ] ; $ maxVersion = $ this -> selectCurrentVersion ( $ mainID ) ; if ( $ maxVersion == $ version ) { array_push ( $ all , $ row ) ; } } return $ all ; }
Select a list by status
 Select a list of mainID, version for a given status, and most recent.
Select a list by status Select a list of mainID version for a given status and most recent .
/*
             * I think the query above works, returning the max() only when that version also has the required
             * status. However, the check below will confirm that the returned version really is the max.
             */