Tinyint(1) field type for boolean data in MySQL table

To store Boolean data, MySQL uses Tinyint(1) field type. We can store, update or delete Boolean data buying Tinyint(1) field type.

Reading data from tinyint field

SELECT * FROM `plus2_boolean` WHERE feb =true
We will get a return where feb column is equal to 1. This query will also return the same result.
SELECT * FROM `plus2_boolean` WHERE feb =1
namejanfebmar
Ronn010
Using False
SELECT * FROM  `plus2_boolean` WHERE feb = False
Output is here
namejanfebmar
Alex101
Let us add more records and check the table with a different query. Here is the full table. We kept more than 1 in our table. All such data will be considered as True only. ( Zero is considered as False and not-zero value is considered as True )
namejanfebmar
Alex101
Ronn010
John102
Lone114
SELECT * FROM `plus2_boolean` WHERE mar is TRUE
namejanfebmar
Alex101
John102
Lone114
SELECT * FROM `plus2_boolean` WHERE mar is NOT TRUE
namejanfebmar
Ronn010

Using IF Condition

We can get different output strings by using SQL IF conditions.
SELECT name, if(jan,'OK','NOT OK') as jan, 
if(feb,'OK','NOT OK') as feb,
if(mar,'OK','NOT OK') as mar  
 FROM `plus2_boolean`
namejanfebmar
AlexOKNOT OKOK
RonnNOT OKOKNOT OK
JohnOKNOT OKOK
LoneOKOKOK

Showing checkbox data

Using the above concepts we can show checked checkbox as selected for True or only checkbox for False data.

Tutorial on how checkbox is used to update Boolean data in MySQL table.

SELECT name, if(jan,'<input type=checkbox name=c1 value=yes  checked>','<input type=checkbox name=c1 value=yes>') as jan, 
if(feb,'<input type=checkbox name=c1 value=yes  checked>','<input type=checkbox name=c1 value=yes>') as feb,
if(mar,'<input type=checkbox name=c1 value=yes  checked>','<input type=checkbox name=c1 value=yes>') as mar
 FROM `plus2_boolean`
Output is here
namejanfebmar
Alex
Ronn
John
Lone

Updating data

UPDATE `plus2_boolean` set mar=True  WHERE name='Ronn'
This will update mar field to 1 for record with name='Ronn'
We can use 0 or 1 also.
UPDATE `plus2_boolean` set mar=0  WHERE name='Ronn'

Toggling data ( updating )

UPDATE `plus2_boolean` set mar = !mar  WHERE name='Ronn'
Above query will change the data of mar column from True to False or from False to True

Storing data in tinyint field

We can use 0 or 1 to insert data.
INSERT INTO  `plus2_boolean` (name ,jan ,feb ,mar)
VALUES ('Alex', 1, 0, 1), ('Ronn', 0,  1,  0)
We can use True or False to add data
INSERT INTO  `plus2_boolean` (name ,jan ,feb ,mar)
VALUES ('Alex2', True, False, 1), ('Ronn2', 0,  True,  0)
SQL dump of plus2_boolean table
CREATE TABLE IF NOT EXISTS `plus2_boolean` (
  `name` varchar(10) NOT NULL,
  `jan` tinyint(1) NOT NULL,
  `feb` tinyint(1) NOT NULL,
  `mar` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `plus2_boolean`
--

INSERT INTO `plus2_boolean` (`name`, `jan`, `feb`, `mar`) VALUES
('Alex', 1, 0, 1),
('Ronn', 0, 1, 0),
('John', 1, 0, 2),
('Lone', 1, 1, 4);


plus2net.com



Post your comments , suggestion , error , requirements etc here .










We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer