SELECT doesn't work when condition is TINYINT = 1

Question

I'm trying to get all the rows where online=1, but it doesn't return anything when I have the condition on SELECT.

But when I do SELECT * there are columns that online is 1

The column is a TINYINT and I'm using MySQLWorkbench

SOLVED: I HAD TO USE BACKTICKS, single quotes only returned rows when the condition was equal to 0. When the condition was =1 it didn't returned anything with single quotes, only with backticks.

SELECT * FROM dbname.example

SELECT * FROM dbname.example WHERE 'online'=1

This is the table schema

  +--------------------+----------------------+------+-----+---------+----------------+
| Field              | Type                 | Null | Key | Default | Extra
 |
+--------------------+----------------------+------+-----+---------+----------------+
| char_id            | int(11) unsigned     | NO   | PRI | NULL    | auto_increment |
| account_id         | int(11) unsigned     | NO   | MUL | 0       |
| char_num           | tinyint(1)           | NO   |     | 0       |
| name               | varchar(30)          | NO   | UNI |         |
| class              | smallint(6) unsigned | NO   |     | 0       |
| base_level         | smallint(6) unsigned | NO   |     | 1       |
| job_level          | smallint(6) unsigned | NO   |     | 1       |
| base_exp           | bigint(20) unsigned  | NO   |     | 0       |
| job_exp            | bigint(20) unsigned  | NO   |     | 0       |
| zeny               | int(11) unsigned     | NO   |     | 0       |
| str                | smallint(4) unsigned | NO   |     | 0       |
| agi                | smallint(4) unsigned | NO   |     | 0       |
| vit                | smallint(4) unsigned | NO   |     | 0       |
| int                | smallint(4) unsigned | NO   |     | 0       |
| dex                | smallint(4) unsigned | NO   |     | 0       |
| luk                | smallint(4) unsigned | NO   |     | 0       |
| max_hp             | int(11) unsigned     | NO   |     | 0       |
| hp                 | int(11) unsigned     | NO   |     | 0       |
| max_sp             | int(11) unsigned     | NO   |     | 0       |
| sp                 | int(11) unsigned     | NO   |     | 0       |
| status_point       | int(11) unsigned     | NO   |     | 0       |
| skill_point        | int(11) unsigned     | NO   |     | 0       |
| option             | int(11)              | NO   |     | 0       |
| karma              | tinyint(3)           | NO   |     | 0       |
| manner             | smallint(6)          | NO   |     | 0       |
| party_id           | int(11) unsigned     | NO   | MUL | 0       |
| guild_id           | int(11) unsigned     | NO   | MUL | 0       |
| pet_id             | int(11) unsigned     | NO   |     | 0       |
| homun_id           | int(11) unsigned     | NO   |     | 0       |
| elemental_id       | int(11) unsigned     | NO   |     | 0       |
| hair               | tinyint(4) unsigned  | NO   |     | 0       |
| hair_color         | smallint(5) unsigned | NO   |     | 0       |
| clothes_color      | smallint(5) unsigned | NO   |     | 0       |
| body               | smallint(5) unsigned | NO   |     | 0       |
| weapon             | smallint(6) unsigned | NO   |     | 0       |
| shield             | smallint(6) unsigned | NO   |     | 0       |
| head_top           | smallint(6) unsigned | NO   |     | 0       |
| head_mid           | smallint(6) unsigned | NO   |     | 0       |
| head_bottom        | smallint(6) unsigned | NO   |     | 0       |
| robe               | smallint(6) unsigned | NO   |     | 0       |
| last_map           | varchar(11)          | NO   |     |         |
| last_x             | smallint(4) unsigned | NO   |     | 53      |
| last_y             | smallint(4) unsigned | NO   |     | 111     |
| save_map           | varchar(11)          | NO   |     |         |
| save_x             | smallint(4) unsigned | NO   |     | 53      |
| save_y             | smallint(4) unsigned | NO   |     | 111     |
| partner_id         | int(11) unsigned     | NO   |     | 
| online             | tinyint(2)           | NO   | MUL | 
| father             | int(11) unsigned     | NO   |     | 
| mother             | int(11) unsigned     | NO   |     | 
| child              | int(11) unsigned     | NO   |     | 
| fame               | int(11) unsigned     | NO   |     | 
| rename             | smallint(3) unsigned | NO   |     | 
| delete_date        | int(11) unsigned     | NO   |     | 
| moves              | int(11) unsigned     | NO   |     | 
| unban_time         | int(11) unsigned     | NO   |     | 
| font               | tinyint(3) unsigned  | NO   |     | 
| uniqueitem_counter | int(11) unsigned     | NO   |     | 
| sex                | enum('M','F')        | NO   |     | 
| hotkey_rowshift    | tinyint(3) unsigned  | NO   |     | 
| hotkey_rowshift2   | tinyint(3) unsigned  | NO   |     | 
| clan_id            | int(11) unsigned     | NO   |     | 
| last_login         | datetime             | YES  |     | 
| title_id           | int(11) unsigned     | NO   |     | 
| show_equip         | tinyint(3) unsigned  | NO   |     | 
+--------------------+----------------------+------+-----+---------+----------------+

I wonder how this works at all as `table` is a reserved mySql keyword — B001ᛦ, Jun 06 '20 at 17:58
@B001ᛦ Although changing it to `char` another reserved word does not help much either :) :) — RiggsFolly, Jun 06 '20 at 18:02
_another reserved word does not help much either..._ Absolutely :) @RiggsFolly — B001ᛦ, Jun 06 '20 at 18:04
Maybe you should restart this by posting the schema for this table — RiggsFolly, Jun 06 '20 at 18:04
So which of the tinyint columns is it you are using in the select query — RiggsFolly, Jun 06 '20 at 18:15

score 0 · Answer 1 · answered Jun 06 '20 at 18:01

0

You compare column with integer value but 'online' is string and is never equal to 1

Use online instead 'online' as below:

SELECT * FROM table WHERE online=1

answered Jun 06 '20 at 18:01

Shirke Amol

16

MySQLWorkbench gives an error when there are no quotes. "SELECT" is not valid at this position. And when the condition is 'online'=0 it returns all the rows, even the ones that online=1 – Nabih Abou Jun 06 '20 at 18:03
Take both table name and column name in quote SELECT * FROM 'table' WHERE 'online'=1 – Shirke Amol Jun 06 '20 at 18:09
@ShirkeAmol column name are NEVER valid in single quotes, you can use `backticks` though – RiggsFolly Jun 06 '20 at 18:10

SELECT doesn't work when condition is TINYINT = 1

1 Answers1