Weird error with JOIN

fkm

Hi everyone 🙂

I have a query that selects fields from two joined tables.
Everything works fine unless I got a field of the joined table in the WHERE (or/and ORDER BY) clause :\

Then I receive the following error-message: "Unknown table 'curl_isles' in where clause"

First it's weird that the MySQL server doesn't complain about the fields of the joined table in the SELECT statement. Furthermore SHOW TABLES returned all tables. So the script can find curl_isles. And finally the queries work fine if pasted into phpMyAdmin which is the most confusing part of all :queasy:

Here's a query that works in the script:

SELECT
  `isles`.`pos`,`collected`,
  `curl_isles`.`owner`, `curl_isles`.`team`,
  `u1`, `u2`, `u3`, `u4`,
  `s1`, `s2`, `s3`, `s4`, `s5`,
  `t1`, `t2`, `t3`, `t4`, `t5`,
  `b1`, `b2`, `b3`, `b4`, `b5`, `b6`, `b7`, `b8`, `b9`, `b10`, `b11`,
  `r1`, `r2`, `r3`
FROM `isles` JOIN `curl_isles` USING (`pos`) 
WHERE `isles`.`pos` LIKE '%:%:%' AND `isles`.`collected` >= '1117207965'
ORDER BY `collected` DESC LIMIT 0,20

And this is a query that works in phpMyAdmin but not in the script:

SELECT
  `isles`.`pos`,`collected`,
  `curl_isles`.`owner`, `curl_isles`.`team`,
  `u1`, `u2`, `u3`, `u4`,
  `s1`, `s2`, `s3`, `s4`, `s5`,
  `t1`, `t2`, `t3`, `t4`, `t5`,
  `b1`, `b2`, `b3`, `b4`, `b5`, `b6`, `b7`, `b8`, `b9`, `b10`, `b11`,
  `r1`, `r2`, `r3`
FROM `isles` JOIN `curl_isles` USING (`pos`) 
WHERE `isles`.`pos` LIKE '%:%:%' AND `curl_isles`.`owner` LIKE 'Devil' AND `isles`.`collected` >= '1117208102'
ORDER BY `collected` DESC LIMIT 0,20

As the error comes from the Database and I print the queries I use in phpMyAdmin just before I send them to the MySQL server I assume that it must be a MySQL related problem. But anyway, here's the related part of my PHP script:

 include("mysql.php");
 $SQL_query  = " SELECT
                   `isles`.`pos`,`collected`,
                   `curl_isles`.`owner`, `curl_isles`.`team`,
                   `u1`, `u2`, `u3`, `u4`,
                   `s1`, `s2`, `s3`, `s4`, `s5`,
                   `t1`, `t2`, `t3`, `t4`, `t5`,
                   `b1`, `b2`, `b3`, `b4`, `b5`, `b6`, `b7`, `b8`, `b9`, `b10`, `b11`,
                   `r1`, `r2`, `r3`
                 FROM `isles` JOIN `curl_isles` USING (`pos`) ".$SQL['where']."
                 ORDER BY `".$SEARCH['sort_by']."` ".$SEARCH['sort_style'].$SQL['limit'];

 print "<pre>".$SQL_query."</pre>";
 $QRY = mysql_query($SQL_query) or die(mysql_error());
 mysql_close();

Does anyone has an idea what could be wrong or what I could try to fix this problem?
I'm at the end of my wisdom :xbones:

nemonoman

the problem is probably embedded in the $SQL_query string that you are constructing from (apparently) a number of strings held in the array $SQL.

Likely this is a typo or spacing problem. Check out the query you are failing with:

$QRY = mysql_query($SQL_query) or die(mysql_error().". Failed query was: $SQL_query");

fkm

And it's getting even stranger :\
With that line of code I just receive the mysql-error ("Unknown table 'curl_isles' in where clause"). "Failed [...]" is missing.

I don't get it :bemused:
Even if I leave out the $QRY = ... I get a MySQL error. Perhaps I got to restart the whole script from scratch :\
Everything I try confuses me even more 🙁

If you are motivated you are welcome to have a look at the script (http://mkf.ath.cx/ik/tool/list_isles.phps). I need a break :queasy:

fkm

So. Break is finished.
I think we can rule out a spacing problem after my last test:

SELECT `isles`.`pos`,`collected`, `curl_isles`.`owner`, `curl_isles`.`team`, `u1`, `u2`, `u3`, `u4`, `s1`, `s2`, `s3`, `s4`, `s5`, `t1`, `t2`, `t3`, `t4`, `t5`, `b1`, `b2`, `b3`, `b4`, `b5`, `b6`, `b7`, `b8`, `b9`, `b10`, `b11`, `r1`, `r2`, `r3` FROM `isles` JOIN `curl_isles` USING (`pos`) WHERE `isles`.`pos` LIKE '%:%:%' AND `isles`.`collected` >= '1117215365' ORDER BY `owner` ASC LIMIT 0,20

Furthermore I found out that the script never reaches the die() and instead dies and returns the MySQL error inside mysql_query() - I'm no specialist, so I could be wrong.
If I leave out the die() the script dies anyway.

$QRY = mysql_query($SQL_query);

I will now investigate in direction of the mysql_query() and no longer concentrate on the query itself, as I tested maaaany different methods. All (/most) of them working in phpMyAdmin and not in the Script.

If you nevertheless have an other idea please tell me :queasy:

nemonoman

curl_isles.owner
looks very suspicious:

try curl_isles.owner,

or even curl_isles.owner ##no backquotes at all.

fkm

Thank you two for the feedback!
It gave me new motivation and finally I found the problem with some help.

The problem was that the script ran much further than I expected and the real problem came from another MySQL query from another file that is included almost at the end of the script (list_pages.php).

I've made a function from the content of list_pages.php and the whole script works again as it's supposed to 😃