Fetching bit field with mysql_query()

Question

Here's my code:

$query = "SELECT Username, EmailVerified, Blocked FROM user";
$result = mysql_query($query, $link);
$row = mysql_fetch_assoc($result);
print_r($row);

Field Username is string and EmailVerified and Blocked are of type bit. The line print_r($row) displays the value of Username field but not the other two. I tried mysql_fetch_object(), mysql_fetch_row(), mysql_fetch_array() also, but same result.

Can't we fetch bit fields with mysql_query()?

What does it display for those two? They should be 0 or 1

Michael Berkowski
– Michael Berkowski

2012-01-08 15:55:44 +00:00
Commented Jan 8, 2012 at 15:55 — Michael Berkowski
– Michael Berkowski, Commented Jan 8, 2012 at 15:55

bsod101 · Accepted Answer · 2012-01-08 15:56:03Z

8

I think you need to cast the BIT field to an integer ->

SELECT Username, CAST(EmailVerified AS unsigned integer) AS EmailV, CAST(Blocked AS unsigned integer) AS Block FROM User

answered Jan 8, 2012 at 15:56

bsod101

3741 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Esailija · Accepted Answer · 2012-01-08 16:05:49Z

4

Yes you can but they are retrieved as strings, and most likely end up being unprintable characters. You can get the values as numbers like so:

$query = "SELECT Username, EmailVerified, Blocked FROM user";
$result = mysql_query($query, $link);
$row = mysql_fetch_assoc($result);

$row['EmailVerified'] = ord( $row['EmailVerified'] );
$row['Blocked'] = ord( $row['Blocked'] );
print_r($row);

edited Jan 8, 2012 at 16:05

answered Jan 8, 2012 at 15:56

Esailija

140k24 gold badges280 silver badges328 bronze badges

3 Comments

Kumar Kush Over a year ago

Thanks. That worked. Yet, I would prefer nicky77's approach.

Esailija Over a year ago

@kush.impetus cool, they are still strings though so you need to convert to number anyway in PHP after that query if you were to use them :P

Kumar Kush Over a year ago

@Esalija: You are right. But you know, they will costly operations in terms of CPU time, in case it counts.

Marco Biscaro · Accepted Answer · 2012-01-08 16:13:18Z

2

Instead of using BIT and converting it each time you need to use, you can use BOOL (which is already a TINYINT) and store TRUE (1) or FALSE (0) values.

answered Jan 8, 2012 at 16:13

Marco Biscaro

1,27811 silver badges17 bronze badges

2 Comments

Kumar Kush Over a year ago

I am sorry but, I am using Navicat for MySQL version 8.0.26 for managing my MySQL databases. I don't see any datatype named BOOL there. That might be because my version of Navicat is older. I will see to it. Thank anyways.

Marco Biscaro Over a year ago

@kush the BOOL type is just an alias to TINYINT(1), so you can use this type in your field.

Collectives™ on Stack Overflow

Fetching bit field with mysql_query()

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related