[Chapter 7 Page 221 Step 4]

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.

[Chapter 7 Page 221 Step 4]

Thanks for your reply. I really puzzled by this.

This is what I have:

Server connection collation: utf8mb4_general_ci
Server charset: UTF-8 Unicode (utf8)
Database client version: libmysql - 5.0.96
Server 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 Posts
FROM users AS u
LEFT JOIN messages AS m
USING (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.

[Chapter 7 Page 221 Step 4]

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 Posts
FROM users AS u
LEFT JOIN messages AS m
USING (user_id)
GROUP BY (u.user_id);

This is the result:
username        Posts
troutster         38
funny man      34        
Gareth           37
tim                 32
finchy           4e6f6e65

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