MySQL errors parsing recordsets in Bindings panel

[EdP21]

I am using PHP 5.2.6 and MySQL 5.0.51 and have just upgraded to CS4 in the hope that DW would 'understand' my more complex SQL statements a bit better. Both the below examples still throw errors if I attempt to expand the recordsets in the Bindings panel (to show and be able to drag fields on to my page).

Both also work perfectly both by clicking the Test button in the recordset Edit dialog and on the site.

1. Gives MySQL Error#: 1054 - Unknown column 'colname' in 'on clause'

Variable:
$colname_rsCategories = $_GET['id'];

SQL:
SELECT DISTINCT art_categories.artcat_id, artcat_name, NOT ISNULL(articlesxcategories.artcat_id) as hascat
FROM art_categories LEFT JOIN articlesxcategories ON art_categories.artcat_id=articlesxcategories.artcat_id AND articlesxcategories.article_id=colname
ORDER BY artcat_name ASC

2. Gives MySQL Error#: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

Variable:
$colname_rsArticles = $_GET['sort'];

SQL:
SELECT article_id, art_title, art_author, DATE_FORMAT(art_dateentered,'%e %b %Y') as dateentered, art_dateuntil<NOW() AS expired, (SELECT COUNT(*) FROM art_details WHERE articles.article_id=art_details.article_id) AS dcount, (SELECT COUNT(*) FROM art_comments WHERE articles.article_id=art_comments.article_id) AS ccount FROM articles
ORDER BY art_active, IF(colname='title', art_title, CONCAT(art_dateentered,'DESC '))

[I know I could do this using LEFT JOIN syntax, which DW does recognise, but it's a lot slower than a sub-query with multiple counts]

Does anyone know of any a way to make DW 'understand' these queries better?

Thanks

Ed

[David_Powers]

If the SQL queries work correctly in MySQL, but you're getting problems in Dreamweaver, I suggest that you submit details to Adobe as a bug report. The bug report form is here.

[EdP21]

Thanks David, I have submitted two bug reports. Not holding my breath..

QiQi86: there is a variable (colname) in the SQL, it's just not in the WHERE clause. It's still perfectly valid SQL though.

Ed

[Guest]

Okay, the first code, u have set one variable with value $_GET['id'] but u didnt use it in ur WHERE clause from mysql code. For the second code, i cant figure it out since the code is too complex, where u put another 2 mysql query in that code.

[richarddwalsh]

in the first code, your $colname_rsCategories is never called, you are calling in the sql statement colname, you need to revise either your variable or the variable call in the SQL statement to cover that error.

As for the second, it is the same issue. are you able to even return results in the test menu?

[EdP21]

No, it is called. I expressed it that way ($colname_rsCategories) since this is a Dreamweaver forum and that is how DW represents the variable (colname) in its implementation. As I said at the beginning of my post, the Test function displays the correct results.

[islandrun]

BUG DREAM