MySQL Data issue on live site

Databases
1

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!

2

your query looks okay except i can’t figure out why you’re joining to the j_p1 table

seems like that might potentially inflate your counts

it’s a LEFT OUTER JOIN which means you want results whether or not there is a matching j_p1 row – but you aren’t using any of the j_p1 columns!

run your query directly in mysql (outside of asp) and you will see whether the problem is with the query (my bet is, it isn’t)

3

May just be talking stupid here but…Have you verified that the database connection is being established, prior to worrying about the query?

4

Thanks for the reply. Yes, I have verified the db connection is in place.

I’ve narrowed it down - if I don’t include these bits of SQL, it works:


      , 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.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

However, they’re the bits I really need!

Seems like something at the host’s end. I’ll get in touch with them.

Thanks