NOT IN() not working for me...

[Busch]

I've got the following query:

    $i=0;
    $track_articles = array();
    foreach ($article_queries AS $article_query) { 
        
        echo "<pre>";
        echo var_dump($track_articles);
        echo "</pre>";

        $i++;
        $cat[$i] = array();
        $all_article_ids = array();
        $get_article_ids = "
            SELECT article_id 
            FROM unique_articles 
            WHERE article_id > 0  
            AND article_id NOT IN(" . $track_articles . ") 
            " . $article_query . " 
            ORDER BY article_id ASC
            LIMIT 1
        ";
        $get_article_ids_result = mysql_query($get_article_ids) or die(mysql_error());
        while ($article_ids = mysql_fetch_array($get_article_ids_result)) {    
            $track_articles[] = intval($article_ids['article_id']);
            $cat[$i][] = $article_ids['article_id'];
        }
    } 

This results in the following error:
Unknown column 'Array' in 'where clause'

If I remove:

AND article_id NOT IN(" . $track_articles . ") 

I get results but those results include the articles I don't want to include (hence the NOT IN()) part.

I am dumping my $track_articles array and it is an array and it looks like this when I leave out the NOT IN() part:

array(0) {
}

array(1) {
  [0]=>
  int(4286)
}

array(2) {
  [0]=>
  int(4286)
  [1]=>
  int(4286)
}

I have no idea why the query isn't excluding the article_id's from the query. I've been working on this problem for about 3 hours and no luck.

Anyone see what I am doing wrong?

Thanks,
Eric

[r937]

please echo the query so that we can see what mysql sees

[coda]

Wich Database number do you use?

MySQL 4 ?

coda

[Busch]

Here it is with the NOT IN():
SELECT article_id FROM unique_articles WHERE article_id > 0 AND article_id NOT IN(Array) AND article_title LIKE 'rustic' OR article_text LIKE '%rustic%' ORDER BY article_id ASC LIMIT 1
Unknown column 'Array' in 'where clause'

Here it i without the NOT IN():
SELECT article_id FROM unique_articles WHERE article_id > 0 AND article_title LIKE 'rustic' OR article_text LIKE '%rustic%' ORDER BY article_id ASC LIMIT 1

[coda]

http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

This command (not in) works with MySQL 5!

coda

[Busch]

Wich Database number do you use?

MySQL 4 ?

coda

MySQL version 4.0.27-standard

[coda]

That must be the problem. The command "NOT IN" works just with MySql 5 and higer

coda

sry for mine bad englisch

[r937]

NOT IN has worked for lots of versions prior to 5

the problem is here -- NOT IN(Array)

there is probably no column called Array

and that's exactly what the error message says!!

[r937]

by the way, be careful mixing ANDs and ORs

[coda]

NOT IN has worked for lots of versions prior to 5

I'm sorry, i will take it back

[Busch]

the problem is here -- NOT IN(Array)

there is probably no column called Array

but when i echo the variable $track_articles, it is an array...
I get the following output:

array(2) {
  [0]=>
  int(4286)
  [1]=>
  int(4286)
}

I realize there is no column called Array and I am really confused as to why it thinks i am declaring Array as a column...

any other ideas? i'm feeling worried that the master is stumped. if you're stumped, im screwed...

eric

[stereofrog]

lets try this

....
        $cat[$i] = array();
        $all_article_ids = array();
        $track_articles = implode(",", $track_articles); #<--- add this line
        $get_article_ids = "
            SELECT article_id
            FROM unique_articles
.... 

[Busch]

lets try this

$i++;
        $cat[$i] = array();
        $all_article_ids = array();
        $track_articles = implode(",", $track_articles);
        $get_article_ids = "
            SELECT article_id 
            FROM unique_articles 
            WHERE article_id > 0  
            AND article_id NOT IN(" . $track_articles . ") 
            AND " . $article_query . " 
            ORDER BY article_id ASC
            LIMIT 1
        "; 

Produced this:
SELECT article_id FROM unique_articles WHERE article_id > 0 AND article_id NOT IN() AND (article_title LIKE 'rustic' OR article_text LIKE '%rustic%') ORDER BY article_id ASC LIMIT 1

Using explode() produced this:
SELECT article_id FROM unique_articles WHERE article_id > 0 AND article_id NOT IN(Array) AND (article_title LIKE 'rustic' OR article_text LIKE '%rustic%') ORDER BY article_id ASC LIMIT 1

I also tried to separate the AND & OR parts of the query into blocks of parenthesis...

eric

[r937]

the parentheses is good

i'm going to move this thread to the php forum, because that's where your problem really is

[Busch]

OK, I finally figured it out. I think it was still an issue with MySQL. In my first run through the SQL query, the array was empty therefore IN() was failing. I guess IN() expects there to be at least one value in the array.

No Value = No Work.

Anyway, thanks for everyone's help.

Eric

[stereofrog]

http://www.php.net/manual/en/language.types.array.php

[r937]

I guess IN() expects there to be at least one value in the array.

well, yes, sort of

as far as mysql is concerned, the stuff inside the parentheses is not an "array", it is a comma-separated list

and yes, it is invalid when empty

if you use a scripting language like php to generate invalid mysql, that doesn't make it a mysql problem