2

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);


}
Improve this question
4
  • 2
    Not an answer, but you cannot use a named parameter marker of the same name twice in a prepared statement (php.net/manual/en/pdo.prepare.php) -> prereq <> :id OR null <> :id Commented Apr 2, 2014 at 20:42
  • In that statement, are you saying where prereq does not equal ID, OR ID is not null? That statement makes it seems like you have a database column called null, which would be a problem. Commented Apr 2, 2014 at 20:48
  • @Sean: the PDO documentation makes it clear, can't use the same named parameter twice; when we ran into that "bug" a long time ago, I don't recall that restriction being noted in the documentation. Commented Apr 2, 2014 at 21:18
  • @Sean, well that might've been part of the problem; it should've been "nullIf"... fixed that now onto the other answers! Commented Apr 2, 2014 at 21:27

3 Answers 3

Reset to default
4

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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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);
Improve this answer

7 Comments

+1 For pointing out that the ':' character was missing from the first argument to the bindParam method, and noting the PDO limitation of referencing a bind parameter name only one time in a statement.
@spencer7593 Gracias. Chivalry is not dead!
So you do need to have the : before the bind? i've never had an issue with that and have been doing it sans-: with all my params to date.
You do need it according to the documentation for bindParam. Without it, results may be unpredictable.
I'm trying this and for some reason it's returning an empty array. (or the array is not sticking... one of the two...)
|
-2

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]);
Improve this answer

2 Comments

Please read the answer above, stackoverflow.com/a/22823152/285587
It is also unclear how your answer is intended to help

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.