PDO fetch data where id's not equal to array

Question

So I'm in the process of updating my project from an XML based schema to a mysql database one. and I'm trying to figure out how to do this function--if its at all possible. I want to fetch some rows from a Database where a value is not equal to that of a value in an array.

function catchUpgrades($id)
{
    $sql = "SELECT id, name, cost FROM upgrades WHERE prereq <> :id OR null <> :id";
    $que = $this->db->prepare($sql);
    $que->bindParam('id', $id[0]);
    try { 
        $que->execute();
        while($row = $que->fetch(PDO::FETCH_BOTH))
        {
            $array['name'] = $row[1];
            $array['id'] = $row[0];
            $array['cost'] = $row[2];
        }
    }catch(PDOException $e) {}
}
function showUpgrades()
{
    $html = "<div id='upgrades'>";
    $array = $this->getUserUpgrades();
    $upgrades = $this->catchUpgrades($array);
    print_r($upgrades);


}

Answer 1

This predicate will never return TRUE:

OR null <> :id

NULL is a keyword representing the special "null" value. The inequality comparison to NULL will never return TRUE.

To test whether :id contains a non-NULL value, use the ANSI standard "IS NOT NULL" operator, like this:

OR :id IS NOT NULL

As a non-standard alternative, you could make use the SQL specific "null safe comparator" operator, like this:

OR NOT (:id <=> NULL)

But, it doesn't make sense that you would want to do that test, because it's redundant with the previous predicate.

What behavior do you want when the value supplied for :id is NULL? Do you want to match every row? (Your query looks familiar to the pattern we use for search criteria, where supplying a NULL value for the bind parameter disables the search criteria, like this:

WHERE ( prereq <> :id OR :id IS NULL )

If value supplied for :id is a null value, the second part will return TRUE, and it doesn't matter what first condition evaluates to at all. With a non-NULL value for :id, the second condition will be FALSE, so the first condition will need to be TRUE in order for a row to be returned.

(But that's just a guess, it's not clear what you are trying to do there.)

---

As another note, we ran into problems with PDO when we used the same named parameter multiple times in a statement. (The PDO documentation didn't indicate a problem with this, back when we we ran into the issue.) The workaround we used was to reference a named parameter only once in the statement, by making all of the named parameters unique.

The workaround was to do something like this:

$sql = "SELECT id, name, cost FROM upgrades WHERE prereq <> :id1 OR :id2 IS NULL";
$que = $this->db->prepare($sql);
$que->bindParam(':id1', $id[0]);
$que->bindParam(':id2', $id[0]);

(I'm not sure if that's fixed in later releases of PDO or not; it may no longer be a problem that needs a workaround like this.)

(I'm not sure that answers your question, but I gave it a shot.)

Answer 2

You can bind a value from an array. This function will work with slight changes - I added a colon, a second bound parameter with a different placeholder name to avoid a PDO conflict, and a return value. Obviously otherColumnName will have to change. If this is not what you intended, please let me know. I left a comment...

function catchUpgrades($id)
{
    $sql = "SELECT id, name, cost FROM upgrades WHERE prereq <> :id OR otherColumnName <> :id2";
    $que = $this->db->prepare($sql);
    $que->bindParam(':id', $id[0]); //note the : before id
    $que->bindParam(':id2', $id[0]); //note the : before id2
    try { 
        $que->execute();
        while($row = $que->fetch(PDO::FETCH_BOTH))
        {
            $array['id'] = $row[0];                
            $array['name'] = $row[1];
            $array['cost'] = $row[2];
        }
        return $array;
    } catch(PDOException $e) {}
}

Then, to call the function, you would have a line like this:

$results_array = catchUpgrades($id);

Answer 3

it's been 8 years since the question was asked, but if by chance someone else is looking for a similar question this might help

prereq != :id OR :id != null

OU:

prereq != ? OR id != ?
$que->execute([$id1, $id2]);