So I've been practicing with random queries and I've run into a problem
First off, here is my query:
GROUP_CONCAT(items.id) AS itemslist,
FROM a LEFT JOIN items ON items.owner = a.username
GROUP BY a.username
and that works perfectly for what I need (I need the items in a list, like the group_concat function gives me), except when there are no records for that user in the items table. I'm wondering if you can help me write a query that would work both ways? So it would return the list of users from the a table, no matter if they have items or not.
You need to choose what to do with it. You can either have a list of all a.username, and only matching itemslist within it (including NULL where no matching list exists), or no record of a.username if there is no matching itemslist.
I can't see what you are doing with a comma separating list that cannot be accommodated for using null instead. Personally I wouldn't use a group_concat, but that's just me. With the comma string, and a language like PHP, you'd execute an explode() call and it will return an array of each item. Problem is empty strings and nulls are considered a single character (that is, null), which can be exploded resulting in a single item of empty string.
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
Hey, I give you a lot of credit for asking the right question about the WHERE clause. I can't tell you how many times I have run into this where the coder had no clue at all. You have the right instincts! Congratulations.
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.