CODEWITHSUNDEEP
phpsqlitefilter
BENN1TH
BENN1TH

Reputation: 2023

SQLITE build query string from values passed

Hi have a function that gets passed strings via the below;

getFilterResults($id, $limit, $misc);

and in my function i connect to SQLite DB and build a query based on the values passed through, like so;

function getFilterResults($id, $limit, $misc) {

  $dbConnect = new SQLite3(database);
  if(!empty($limit) && !empty($id) && !empty($misc)){


        $buildString = ('SELECT * FROM fields ORDER BY id DESC');

        }else{

        //no params filters arrived so dont filter - aka get all results
        $buildString = ("SELECT * FROM fields ORDER BY id DESC");

        }

        $query = $dbConnect->query($buildString);

        //...



}

Issue is how do I build the query if some of the values are empty and I have to decide which value uses/starts of the 'WHERE' query..obviously the first value return that is not null starts of the 'WHERE'..

I can see the long way would be to loop through each one and build - once the first value found that is not null and kick off from there but that seems unpractical and not good practice.

    //check if limit set..
    if($limit) {
         $doLimit = 'LIMIT '.$limit.'';
    }

    WHERE 'id = '.id.' AND misc = '.$misc.' '.$doLimit.'

Upvotes: 0

Views: 217

Answers (1)

bradynpoulsen
bradynpoulsen

Reputation: 365

Your function taking in ($id, $limit, $misc) really prevents you from being able to do anything else than what you already are... I would recommend using prepared statements though

// Stores values for a prepared statement
$values = [];

$sqlite = new SQLite3(/* ... */);

$query = 'SELECT * FROM fields WHERE id = ? AND misc = ? ORDER BY id DESC';
$values[] = $id;
$values[] = $misc;

if ($limit) {
    $query .= ' LIMIT ?';
    $values[] = $limit;
}

// Use prepare and let SQLite3 escape the values for you
$stmt = $sqlite->prepare($query);
foreach ($values as $idx => $val) {
    $stmt->bindValue($idx, $val);
}

$result = $stmt->execute();

Upvotes: 1

Related Questions