smitty71 Posted June 17, 2015 Share Posted June 17, 2015 I have tried everything I can think of, including dropping my tables and pasting directly from the SQL file in the Downloads section. When I run the query: SELECT u.username,IF(COUNT(message_id) > 0,COUNT(message_id), 'None') AS PostsFROM users AS uLEFT JOIN messages AS mUSING (user_id)GROUP BY (u.user_id);This is the result:username Poststroutster 38funny man 34 Gareth 37tim 32finchy 4e6f6e65It is somehow adding 30 to the results, and a strange hex number instead of the word "None".If I remove the "IF" part I get the expected result and finchy has 0 posts. Link to comment Share on other sites More sharing options...
Larry Posted June 19, 2015 Share Posted June 19, 2015 This is very strange. I've not seen it before. Considering the issue with "None", my hypothesis is it's not adding 30 to the others, but rather adding the character "3". I wonder if there's not an issue with character sets here. What version of MySQL are you using? What is the character set on the table? What is the character set you're using on the connection? Link to comment Share on other sites More sharing options...
smitty71 Posted June 19, 2015 Author Share Posted June 19, 2015 Thanks for your reply. I really puzzled by this. This is what I have: Server connection collation: utf8mb4_general_ciServer charset: UTF-8 Unicode (utf8)Database client version: libmysql - 5.0.96Server version: 5.5.32-31.0-log - Percona Server (GPL), Release rel31.0, Revision 549 The tables are all utf-8 As an added weirdness, if I subtract 30 from the 2nd argument of the IF, I get this: SELECT u.username,IF(COUNT(message_id) > 0,(COUNT(message_id)- 30), 'None') AS PostsFROM users AS uLEFT JOIN messages AS mUSING (user_id)GROUP BY (u.user_id); Result: username Posts troutster -22 funny man -26 Gareth -23 tim -28 finchy None I've also noticed that if I add or subtract anything from that 2nd argument, it has a similar result. Like if I subtract 1, the results are 7, 3, 6, 1, and None. I only get a weird result if I use the original, correct code. In fact I just tried adding 0 and got the proper result. I know I must be missing something simple. Link to comment Share on other sites More sharing options...
Larry Posted June 19, 2015 Share Posted June 19, 2015 I suspect the utf8mb4_general_ci is the problem. I'd change this to utf8_general_ci and see if that fixes it. Link to comment Share on other sites More sharing options...
smitty71 Posted June 20, 2015 Author Share Posted June 20, 2015 Thanks for your help. That must have been the problem. I was running the queries through phpMyAdmin on my SiteGround account and could not change the connection settings, so I downloaded MySQL and decided to try it all locally on my PC. Now I am getting the expected results. Also using the MySQL Command Line Client is forcing me to be more aware of what was happening (and more careful in my typing). I'll revisit the issue with my ISP at some later time. Right now I'm just glad to get past this distraction and back to learning the basics. Thanks again for your help and your book. Link to comment Share on other sites More sharing options...
Larry Posted June 24, 2015 Share Posted June 24, 2015 Good to know! Thanks for sharing that result. This was a stumper! Link to comment Share on other sites More sharing options...
Recommended Posts