public/lib/library.php
/**
     * More general search of the library that matches on any
     * of the following columns: artist, album, note, Locale, or Genre
     * @abstract Get all key library information based on \
     * given input and return in json format \
     * all values that match the paramaters. \
     * Similar to SeathcLibraryWithAlbum but broader search
     * @todo convert to wrapper for searchLibraryWithAlbum
     * @global \TPS\mysqli $mysqli
     * @param string $term
     * @param boolean $exact
     * @param string $sort columns name to sort result by
     * @return boolean|array
     */
More general search of the library that matches on any
of the following columns: artist, album, note, Locale, or Genre
@abstract Get all key library information based on \
given input and return in json format \
all values that match the paramaters.
More general search of the library that matches on any of the following columns : artist album note Locale or Genre @abstract Get all key library information based on \ given input and return in json format \ all values that match the paramaters .
public function searchLibrary($term,$exact=False,$page=1,$limit=25, $sort='RefCode', $reverse=false){
        $tps = new \TPS\TPS();
        $tps->sanitizePagination($page, $limit);
        $result = array();
        if(!$exact){
            $term="%{$term}%";
        }
        $cols = $tps->listDatabaseColumns('library');
        if(!in_array($sort, $cols, true)){
            $sortIndex = array_find($sort, $cols);
            if(!$sortIndex){
                throw new \Exception("requested column does not exist in specified table");
            }
            else{
                $oldSort = (string)$sort;
                $sort = $cols[$sortIndex];
                $this->log->warn("table column `$oldSort` requested but wrong case found, will use `$sort`",
                    200, 'library search');
            }
        }
        $direction = $reverse?"DESC":"";
        $saniCol = "";
        try{
            $saniCol = $this->mysqli->real_escape_string($sort);
        }
        catch (\Exception $e){
            $saniCol = $this->db->quote($sort);
        }
        if($stmt = $this->mysqli->prepare("SELECT datein,dateout,RefCode,artist,album,"
                . "`format`,variousartists,`condition`,genre,`status`,"
                . "Locale,CanCon,updated,release_date,note,playlist_flag,year "
                . "FROM library where "
                . "artist like ? or album like ? or note like ? or"
                . " Locale like ? or genre like ? ORDER BY `library`.`$saniCol`, `library`.`RefCode` "
                . "$direction limit ?,?")){
            $stmt->bind_param('sssssii', $term, $term, $term, $term, $term, $page, $limit);
            $stmt->execute();
            $stmt->bind_result($datein,$dateout,$RefCode_q,
                    $artist_q,$album_q,$format,$variousartists,
                    $condition,$genre,$status,
                    $Locale,$CanCon,$updated,$release_date,
                    $note,$playlist_flag,$year);
            while($stmt->fetch()){
                array_push($result, array(
                    'datein'=>$datein,'dateout'=>$dateout,'RefCode'=>$RefCode_q,
                    'artist'=>$artist_q,'album'=>$album_q,'format'=>$format,
                    'variousartists'=>$variousartists,
                    'condition'=>$condition,'genre'=>$genre,'status'=>$status,
                    'Locale'=>$Locale,'CanCon'=>$CanCon,'updated'=>$updated,
                    'release_date'=>$release_date,
                    'note'=>$note,'playlist_flag'=>$playlist_flag,'year'=>$year,
                ));
            }
            $stmt->close();
        }
        else{
            error_log($this->mysqli->error);
            return FALSE;
        }
        return $result;
    }
public function searchLibrary ( $ term , $ exact = False , $ page = 1 , $ limit = 25 , $ sort = 'RefCode' , $ reverse = false ) { $ tps = new \ TPS \ TPS ( ) ; $ tps -> sanitizePagination ( $ page , $ limit ) ; $ result = array ( ) ; if ( ! $ exact ) { $ term = " % { $ term } % " ; } $ cols = $ tps -> listDatabaseColumns ( 'library' ) ; if ( ! in_array ( $ sort , $ cols , true ) ) { $ sortIndex = array_find ( $ sort , $ cols ) ; if ( ! $ sortIndex ) { throw new \ Exception ( " requested column does not exist in specified table " ) ; } else { $ oldSort = ( string ) $ sort ; $ sort = $ cols [ $ sortIndex ] ; $ this -> log -> warn ( " table column ` $ oldSort ` requested but wrong case found, will use ` $ sort ` " , 200 , 'library search' ) ; } } $ direction = $ reverse ? " DESC " : " " ; $ saniCol = " " ; try { $ saniCol = $ this -> mysqli -> real_escape_string ( $ sort ) ; } catch ( \ Exception $ e ) { $ saniCol = $ this -> db -> quote ( $ sort ) ; } if ( $ stmt = $ this -> mysqli -> prepare ( " SELECT datein,dateout,RefCode,artist,album, " . " `format`,variousartists,`condition`,genre,`status`, " . " Locale,CanCon,updated,release_date,note,playlist_flag,year  " . " FROM library where  " . " artist like ? or album like ? or note like ? or " . "  Locale like ? or genre like ? ORDER BY `library`.` $ saniCol `, `library`.`RefCode`  " . " $ direction  limit ?,? " ) ) { $ stmt -> bind_param ( 'sssssii' , $ term , $ term , $ term , $ term , $ term , $ page , $ limit ) ; $ stmt -> execute ( ) ; $ stmt -> bind_result ( $ datein , $ dateout , $ RefCode_q , $ artist_q , $ album_q , $ format , $ variousartists , $ condition , $ genre , $ status , $ Locale , $ CanCon , $ updated , $ release_date , $ note , $ playlist_flag , $ year ) ; while ( $ stmt -> fetch ( ) ) { array_push ( $ result , array ( 'datein' => $ datein , 'dateout' => $ dateout , 'RefCode' => $ RefCode_q , 'artist' => $ artist_q , 'album' => $ album_q , 'format' => $ format , 'variousartists' => $ variousartists , 'condition' => $ condition , 'genre' => $ genre , 'status' => $ status , 'Locale' => $ Locale , 'CanCon' => $ CanCon , 'updated' => $ updated , 'release_date' => $ release_date , 'note' => $ note , 'playlist_flag' => $ playlist_flag , 'year' => $ year , ) ) ; } $ stmt -> close ( ) ; } else { error_log ( $ this -> mysqli -> error ) ; return FALSE ; } return $ result ; }
More general search of the library that matches on any
 of the following columns: artist, album, note, Locale, or Genre
 @abstract Get all key library information based on \
 given input and return in json format \
 all values that match the paramaters.
More general search of the library that matches on any of the following columns : artist album note Locale or Genre @abstract Get all key library information based on \ given input and return in json format \ all values that match the paramaters .
//$this->mysqli; # PDO //$result=["error"=>$mysqli->error];