On my local IIS server (Win XP, running MySQL 5.1.41-community), this SQL returns output, and I can get that output into an ASP page, using a connection via a “System DSN”, using the MySQL ODBC 5.1 Driver.
When I transferred the ASP page to my live site, on different hosting, running IIS and MySQL 5.0.19-log, the page returns no data.
However, I can run the SQL that is being used in the ASP page in SQLyog, on the live site, and it does return data.
There are no errors on the ASP page when I view it on the live site.
I can’t work out what is going on. I can run other SQLs on the same ASP page (e.g. select count(*) ct from j_ecard_photos), and it +does+ run okay on the ASP page.
The SQL that isn’t returning data on the live site is this - could it be that it’s too complicated, or something like that:
SELECT DATE(card_date)
, DATE_FORMAT(card_date, '%W %d %M %Y') as b_date
, DATE_FORMAT(card_date, '%Y-%m-%d') as f_date
, COUNT(*) ct_all
, SUM(CASE WHEN p.themeType = 'v' THEN 1 ELSE 0 END) AS ct_vid
, SUM(CASE WHEN p.themeType = 'f' THEN 1 ELSE 0 END) AS ct_fla
, SUM(CASE WHEN p.themeType = 'i' THEN 1 ELSE 0 END) AS ct_img
, SUM(CASE WHEN c.photodirect = 1 THEN 1 ELSE 0 END) AS ct_pht
, SUM(CASE WHEN c.readflag = '1' THEN 1 ELSE 0 END) AS ct_r_y
, SUM(CASE WHEN c.readflag = '0' THEN 1 ELSE 0 END) AS ct_r_n
FROM card c
LEFT OUTER
JOIN photos p
ON p.photoID = c.photoid
LEFT OUTER
JOIN j_p1 ph
ON ph.photoID = c.photoID
GROUP BY DATE(card_date)
ORDER BY 1 DESC
LIMIT 10
I got the LEFT OUTER concept from another SitePoint query a few years ago.
Any advice much appreciated.
Thanks!