2

I can't get my NOT EXISTS mysql statement to work and it's driving me mad now:

$ancestors = mysql_query('
    SELECT * FROM comments e
        WHERE 
            ancestors = "' . $comment["id"] . '" AND 
            user_id != "' . $user->user_object["id"] . '" AND
                NOT EXISTS
                    (
                        SELECT  null 
                        FROM    notifications d
                        WHERE   d.target_id = e.id
                    )
', $database->connection_handle);

Any ideas?

ERROR:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /<>/<>/functions.php on line 785
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /<>/<>/functions.php on line 785
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /<>/<>/functions.php on line 785
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /<>/<>/functions.php on line 785
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /<>/<>/functions.php on line 785
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /<>/<>/functions.php on line 785
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /<>/<>/functions.php on line 785
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /<>/<>/functions.php on line 785
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /<>/<>/functions.php on line 785

Line 785:

    while($reply = mysql_fetch_array($ancestors, MYSQL_ASSOC)){

If I do this:

$ancestors = mysql_query('SELECT * FROM ' . $database->db_prefix . 'comments 
    WHERE    
        ancestors = "' . $comment["id"] . '" AND 
        user_id != "' . $user->user_object["id"] . '"', 
        $database->connection_handle
);

It returns my results i expect.

the notifications table does contain an entry

mysql var dump =

string(46) "Table 'whatever_co.comments' doesn't exist"

//SOLVED::: ' . $database->db_prefix . ' was missing from my table selectors.

Jimmyt1988
  • 20,466
  • 41
  • 133
  • 233
  • what error does it give? – ncremins May 24 '12 at 22:15
  • 3
    Is null a column name in notifications? If so, you've got problems, because it's a [reserved word](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) – amaidment May 24 '12 at 22:15
  • "Any ideas?" --- yes, there is one: fix it – zerkms May 24 '12 at 22:17
  • 1
    @amaidment: no, it is just a value `NULL`. It is common to `SELECT NULL` for queries nested in `(NOT) EXISTS` – zerkms May 24 '12 at 22:18
  • Can you try dumping out the full query before running to make sure the variables contain what you think they do? – mellamokb May 24 '12 at 22:18
  • 1
    Could you please place `var_dump(mysql_error());` after the `mysql_query` and post its output here? – Quassnoi May 24 '12 at 22:25
  • Also, why the downvotes? It's a newbish question, but the @op seems to have done his best to provide the error description (FWIW) – Quassnoi May 24 '12 at 22:31
  • I thnk I found the problem. It's a syntax error. will report back – Jimmyt1988 May 24 '12 at 22:36
  • I was missing my table prefix: ' . $database->db_prefix . ' from comments and notifications. The reason for the question was its the first time i have used not exists.. and was assuming that was the part that was incorrect. Thanks for all your time. This causes for celebration. – Jimmyt1988 May 24 '12 at 22:37

3 Answers3

0

Your NOT EXISTS is fine. I think it may be a problem with your select. Do you have a field called "null" in that table? If not that is your problem.

I think you are looking for something along the lines of

Select * 
FROM notifications d
WHERE d.target_id = e.id
AND e.id IS NULL

Try using something like that.

Tony318
  • 552
  • 2
  • 9
  • 1
    "Do you have a field called "null" in that table? If not that is your problem." --- wrong. Not an answer – zerkms May 24 '12 at 22:19
  • Thanks. Actually that's all I was wondering. Is the way I have laid it out correct. Excellent. Glad I got it right. – Jimmyt1988 May 24 '12 at 22:19
  • @zerkms so you can literally "select null"? I have never seen anyone do this nor have i ever heard of it being done. – Tony318 May 24 '12 at 22:21
  • @Tony318: `SELECT NULL` is common for `EXISTS` expression, because it doesn't care of the data. If `SELECT NULL` confuses you - you can use `SELECT 1` – zerkms May 24 '12 at 22:22
  • @Tony318: Yes, you can `SELECT NULL`. Just try it in a database of yours: `SELECT columnA, columnB, NULL AS columnC FROM TableX ;` – ypercubeᵀᴹ May 24 '12 at 22:22
  • I don't know. I just copied the code from somewhere else, put my stuff in. It didn't work.. So I tried somewhere else to see where I went wrong.. But it didn't seem wrong, after about 2 hours of trying to get the silly thing to work I thought I best atleast check if the statement is correct. Regardless of null, it still returns same rresults. As long as the statement is correct I can get the rest to work. – Jimmyt1988 May 24 '12 at 22:22
  • "I don't know. I just copied the code from somewhere else, put my stuff in" --- **THAT'S THE BIGGEST MISTAKE**. Copy-pasting code without understanding how it works and what it does **NEVER** leads to results you expected – zerkms May 24 '12 at 22:23
  • The 2 hours = understanding it. (1)(I am selecting * comments where ancestors = "id i need" and where it belonds to me.)(2)I am not selecting any rows where the notification table contains the same id. – Jimmyt1988 May 24 '12 at 22:26
0

WHERE EXISTS and WHERE NOT EXISTS don't look at the contents of the rows being returned by the subquery. They simply check if ANY rows are returned: http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

So even though you're selecting a hard-coded null, which would normally "not exist", MySQL isn't looking at that null - it's looking at the row which contains that null, and evaluates it to "hey, that exists".

Marc B
  • 356,200
  • 43
  • 426
  • 500
0

I was missing my table prefix: ' . $database->db_prefix . ' from comments and notifications. The reason for the question was its the first time i have used not exists.. and was assuming that was the part that was incorrect. Thanks for all your time. This causes for celebration.

Jimmyt1988
  • 20,466
  • 41
  • 133
  • 233