2

I was googling on how to store a 1 or 0 as a value. I chose the BIT(1) datatype: enter image description here

Then, for user authentication I store that value in a temporary TEMPUSER object like so inside nodejs: (gender a column in my users table)

db.query('SELECT * from users WHERE id = ' + db.escape(userID) + ' AND password = ' + db.escape(userPassword) + '', function(err, results) {
    TEMPUSER = {
        gender: results[0].gender
    }
});

Now, when I console.log TEMPUSER using:

console.log('Gender Data: ' +TEMPUSER.gender);

It displays:

enter image description here

How Bizarre? Shouldn't it be displaying 1 or 0?

Edit:

And the gender row value is set to 1 as well in mysql for this specific user:

enter image description here

Community
  • 1
  • 1
NiCk Newman
  • 1,716
  • 7
  • 23
  • 48

2 Answers2

4

I suspect if you did

console.log(typeof TEMPUSER.gender);

...you'd find it said object, because the plugin you're using maps BIT columns to NodeJS Buffer objects. So you're getting a Buffer with one entry. The + in your console.log statement converts it to a string with one character, character code U+0001. On my Windows system, if I do:

console.log("\u0001");

...I get the smiley face, too.

Instead, use the buffer:

TEMPUSER = {
    gender: results[0].gender[0]
}

I'd check what you get when the query returns NULL; could be an empty buffer (the above would give you undefined), could be null (the above would give you an error).

T.J. Crowder
  • 1,031,962
  • 187
  • 1,923
  • 1,875
  • Wow, totally forgot about the MYSQL node plugin. I keep putting nodejs under the bus for no reason. Btw, I'm using the [felixge's](https://github.com/felixge/node-mysql) plugin if anyone else see's this thread. Would this be a bug in your opinion @T.J, or just normal behavior? Because I'll just use the `.charCodeAt(0)` method and be done with it. I just found it kind-of funny/odd a smiley face would pop up out of no where so randomly.. lol – NiCk Newman May 30 '15 at 12:56
  • 1
    @NiCkNewman: Following that link, it says it maps `BIT` to `Buffer`. I've updated the answer. – T.J. Crowder May 30 '15 at 13:12
  • 2
    I was about to reply and tell you I had to use `toString()` haha. Quick edit TJ, did not know you could use the buffers like that. That's awesome. Just learned something about node that I never have before. Love the `BIT` data type now. I was getting worried and thought I had to move to `tinyint` :(. Yep, this is now [solved](http://i.gyazo.com/2795aaa6784afa4fb3bc83a237fcbb79.png) – NiCk Newman May 30 '15 at 13:17
0

This is really funny, should save that awkward behavior.

On a series note:

first, you should save boolean values with tinyint data type. (optionally, limit to one character.)

second, as deciding on data-type for gender, quick search result in this:

Storing sex (gender) in database

Best of luck!

Community
  • 1
  • 1
JohnnyJS
  • 1,320
  • 10
  • 21
  • Yeah it's either going to be `bit(1)` or `char(1)` for me. Using @T.J. Crowder's fix, I'd rather use bit as it's less bits than char. Thanks for your insight ! ~ (And this matters to me when I have thousands if not hundred of thousands of characters) – NiCk Newman May 30 '15 at 12:58