SitePoint Sponsor |
|
User Tag List
Results 1 to 17 of 17
Thread: NOT IN() not working for me...
-
Dec 19, 2006, 05:08 #1
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'];
}
}
Unknown column 'Array' in 'where clause'
If I remove:
PHP Code:AND article_id NOT IN(" . $track_articles . ")
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) }
Anyone see what I am doing wrong?
Thanks,
Eric
-
Dec 19, 2006, 05:21 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
please echo the query so that we can see what mysql sees
-
Dec 19, 2006, 05:28 #3
- 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
-
Dec 19, 2006, 05:29 #4
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
-
Dec 19, 2006, 05:31 #5
- 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
-
Dec 19, 2006, 05:32 #6
-
Dec 19, 2006, 05:34 #7
- 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
-
Dec 19, 2006, 05:41 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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!!
-
Dec 19, 2006, 05:42 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
by the way, be careful mixing ANDs and ORs
-
Dec 19, 2006, 05:47 #10
- Join Date
- Dec 2006
- Location
- Germany, but living in Denmark
- Posts
- 18
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Dec 19, 2006, 06:47 #11
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) }
any other ideas? i'm feeling worried that the master is stumped. if you're stumped, im screwed...
eric
-
Dec 19, 2006, 06:52 #12
- 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
....
-
Dec 19, 2006, 06:56 #13PHP 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
";
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
-
Dec 19, 2006, 07:26 #14
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Dec 19, 2006, 08:13 #15
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
-
Dec 19, 2006, 08:15 #16
-
Dec 19, 2006, 08:19 #17
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Bookmarks