MySQL AND keyword issue

Question

I have a query which is behaving strange...

Firstly, here is a query to get all PMs whether or not they've been read or deleted for the user ID 1:

SELECT * FROM `pms` WHERE `toid` = '1'

This returns 3 rows as expected. Next, let's see if I can get only unread messages for this user:

SELECT * FROM `pms` WHERE `toid` = '1' AND `read` = '0'

This returns 2 rows as expected. Let's see if I can get any read and unread messages which have been binned:

SELECT * FROM `pms` WHERE `toid` = '1' AND `binned` = '0'

This returns 2 rows as expected.

The query which I need to run is getting all unread and not binned messages for a specified user id. To do this, I am doing this:

SELECT * FROM `pms` WHERE `toid` = '1' AND `read` = '0' AND `binned` = '0'

However, it should be returning 1 row as I know in the database there is a message with toid as 1, read as 0 and binned as 0 but for some reason this query above is returning 0 rows...

Why is this?

UPDATE

Here is a screenshot of my table structure as seen in Sequel Pro:

screenshot

Here is a screenshot of the data inside the table as seen in Sequel Pro:

screenshot2

As you can see there is definitely 1 record with toid as 1, read as 0 and binned as 0.

UPDATE 2

The reason these are ENUM is because I'm wishing to store a boolean value in MySQL. I do this by enforcing the column to be either a '1' or a '0' and making it default to '0' as well. If anyone has a better way of storing boolean values in MySQL then I'd love to learn.

Secondly, here is my PHP function inside of my User.class.php file which is getting the unread count using this SQL. This function is returning 0 when it should be returning 1. The $this->getUserId() is returning 1 as that is the current user I am using:

public function getUnreadCount()
{
    global $database;
    $sql = "SELECT * FROM `pms` WHERE `toid` = '".$this->getUserID()."' AND `read` = '0' AND 'binned' = '0'";
    $query = $database->query($sql);
    $count = $database->count($query);
    return $count;
}

Thanks for the help so far but I still cannot work out why this isn't working. I'm using the read in the query adding backticks to prevent MySQL from using it as a keyword.

I bet its something really obvious I'm missing...

Well if it returns 1 row it means one field must be 1/0 so recheck your database ? — Jonathan Römer, Jan 27 '13 at 16:53
@JonathanRomer It is SUPPOSED to return 1 row but returns 0 rows. — Jamesking56, Jan 27 '13 at 17:06
You should try renaming the 'read' to something else, since it is a keyword in SQL — Aakash Anuj, Jan 27 '13 at 17:08
@Jamesking56 and that is why the 'read' appears in blue while the other column attributes in gray — Aakash Anuj, Jan 27 '13 at 17:09
I thought that if you put it in backticks it wouldn't treat it as a keyword? I've been putting it in backticks? — Jamesking56, Jan 27 '13 at 17:12
Maybe its a stupid question from my side, but why do you use enum for the read and binned? — Jonathan Römer, Jan 27 '13 at 17:19
I agree with @JonathanRomer why do you use ENUM and not just INT. — Mr. Radical, Jan 27 '13 at 17:22
I use ENUM because I want to enforce that it can only be either 1 or 0 so that I can translate that into a boolean value in PHP. If you know of a better way of storing boolean in MySQL then I'd love to learn — Jamesking56, Jan 27 '13 at 21:33
@Jamesking56 you could use foreign keys: http://stackoverflow.com/questions/9249695/limit-the-value-of-a-mysql-datatype-to-a-specific-range-preferably-not-enum. If you want to restrict the ENUM is a good solution. I'm not sure if there is a performance differs. — Mr. Radical, Jan 28 '13 at 00:11

score 1 · Answer 1 · answered Jan 27 '13 at 17:32

James, I think the problem might have to do with how the table was populated.

Since the "read" and "binned" columns' datatypes are ENUMs, you probably have to either set the correct default value ('0' or '1') or always provide a valid value when inserting a row into this table. In other words, you can't omit a value for either the "read" or "binned" columns when inserting a "pms"-row.

In other words, if your "pms" table is set up as follows, without defaults:

create table pms (
  toid int,
  `read` ENUM('0','1') ,
  binned ENUM('0','1') 
  );

then you have to insert fully specified row-values like so:

insert into pms (toid, `read`, binned) values
  (1, '0', '0'),
  (1, '0', '1'),
  (1, '1', '0'),
  (1, '1', '1')
;

and avoid inserting sparse data like this:

insert into pms (toid) values (1);
insert into pms (toid, binned) values (1, '1');
insert into pms (toid, `read`) values (1, '1');
insert into pms (toid, `read`, binned) values (1, '1', '1');

Providing the correct default enum-value for those columns would also solve this issue:

create table pms (
  toid int,
  `read` ENUM('0','1') default '0',
  binned ENUM('0','1') default '0' 
  );

I've set up a sqlfiddle to illustrate.

They already are setup to default to 0 of nothing is entered. Check my MySQL setup in my screenshot above. — Jamesking56, Jan 27 '13 at 21:35
Make sure that default is a character '0', not an integer zero. The [sqlfiddle](http://sqlfiddle.com/#!2/969e9/1) above reproduces your behavior exactly (and shows alternatives behaving correctly), hence my suspicion that it has to do with no or incorrect default values when inserting into that table — fspinnenhirn, Jan 27 '13 at 23:13

echo_Me · Answer 2 · 2013-01-27T18:20:52.333

0

if your columns are integers try doing this

   SELECT * FROM `pms` WHERE `toid` = 1 AND `read` = 0 AND `binned` = 0

EDIT: it should be your columns to be integers like that in this demo.

SQLFIDDLE DEMO

or to be enum with values as strings like here

 SELECT * FROM `pms` 
WHERE `toid` = 1 AND `read` = '0' AND `binned` = '0'

sqllfiddle demo

edited Jan 27 '13 at 18:20

answered Jan 27 '13 at 17:21

echo_Me

37,078
5
58
78

score 0 · Answer 3 · answered Jan 27 '13 at 18:20

0

Try to test if you have set your variables correctly. I suggest by testing if you get the right results when querying for just one variable.:

SELECT * FROM `pms` WHERE `toid` = '1'; -- 3;

SELECT * FROM `pms` WHERE `read` = '0'; -- 4;

SELECT * FROM `pms` WHERE `binned` = '0'; -- 4;

Classic mistakes would be that you have used integer values instead of string (ENUM) values or have substituted the zero for an null.

SQL FIDDLE DEMO

answered Jan 27 '13 at 18:20

Mr. Radical

1,847
1
19
29

Here is my PHP to get the unread messages count from the database: http://pastebin.com/c1RygHiS – Jamesking56 Jan 27 '13 at 21:36
@Jamesking56 check you PHP code near 'binned' you have used quotes instead of backticks. Maybe this might explain you troubles. – Mr. Radical Jan 27 '13 at 22:20
Oh, I only responded on you comment. Stupid me :$ Oke, in that case you can accept your own answer. – Mr. Radical Jan 27 '13 at 22:25
@Jamesking56 For your comment about boolean see this post stackoverflow.com/questions/289727/…. The best method is to use BIT according to this answer. – Mr. Radical Jan 27 '13 at 22:38

score 0 · Accepted Answer · answered Jan 27 '13 at 21:53

Wow haha I've just found why its not been returning the rows.

I'd mistakenly used single quotes instead of backticks in my PHP implementation of the SQL query...

So my query was actually:

$sql = "SELECT * FROM `pms` WHERE `toid` = '".$this->getUserID()."' AND `read` = '0' AND 'binned' = '0'";

When it should've been:

$sql = "SELECT * FROM `pms` WHERE `toid` = '".$this->getUserID()."' AND `read` = '0' AND `binned` = '0'";`

As you can see, near the end of the query for binned I had mistakenly used single quotes.

Can you believe it was that simple?

Just out of interest, how do you think I should be storing boolean values in MySQL?

MySQL AND keyword issue

UPDATE

UPDATE 2

4 Answers4