SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    NOT IN() not working for me...

    I've got the following query:
    PHP Code:
        $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:
    PHP Code:
    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:
    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please echo the query so that we can see what mysql sees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2006
    Location
    Germany, but living in Denmark
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wich Database number do you use?

    MySQL 4 ?

    coda

  4. #4
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 '&#37;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

  5. #5
    SitePoint Member
    Join Date
    Dec 2006
    Location
    Germany, but living in Denmark
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

    This command (not in) works with MySQL 5!

    coda

  6. #6
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by coda View Post
    Wich Database number do you use?

    MySQL 4 ?

    coda
    MySQL version 4.0.27-standard

  7. #7
    SitePoint Member
    Join Date
    Dec 2006
    Location
    Germany, but living in Denmark
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That must be the problem. The command "NOT IN" works just with MySql 5 and higer

    coda

    sry for mine bad englisch

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    by the way, be careful mixing ANDs and ORs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Dec 2006
    Location
    Germany, but living in Denmark
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    NOT IN has worked for lots of versions prior to 5
    I'm sorry, i will take it back

  11. #11
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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:
    Code:
    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

  12. #12
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lets try this

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

  13. #13
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by stereofrog View Post
    lets try this
    PHP Code:
    $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 '&#37;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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the parentheses is good

    i'm going to move this thread to the php forum, because that's where your problem really is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  16. #16
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Busch View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •