CODEWITHSUNDEEP
phpmysql
iOflower
iOflower

Reputation: 39

NOT NULL default 1 inserting both 0 and 1

In MySQL I added a new column to a table...

ALTER TABLE `articles` ADD `visible` NOT NULL DEFAULT (1)

I used the default as 1 for new articles to be visible and if an article get disabled, it will be updated to 0 meaning it can't be viewed.

Now on new article inserts instead of MySQL inserting the default 1 as defined, it is rather inserting either 0 on some rows and 1 other rows.

I would like to know whats causing this and the possible solution to this issue instead of having to update those rows to 1 manually.

Eg of an insert is...

INSERT INTO articles (filename, owner, name, descr, image1, image2, category, added, info_hash, size, numfiles, save_as, news, external, nfo, lang, anon,tube, last_action) VALUES (".sqlesc($fname).", '".$CURUSER['id']."', ".sqlesc($name).", ".sqlesc($descr).", '".$inames[0]."', '".$inames[1]."', '".$catid."', '" . get_date_time() . "', '".$infohash."', '".$articlesize."', '".$filecount."', ".sqlesc($fname).", '".$news."', '".$external."', '".$nfo."', '".$langid."','$anon', ".sqlesc($tube).",'".get_date_time()."')");

Then I tried updating it to

INSERT INTO articles (filename, owner, name, descr, image1, image2, category, added, info_hash, size, numfiles, save_as, news, external, nfo, lang, visible, anon,tube, last_action) VALUES (".sqlesc($fname).", '".$CURUSER['id']."', ".sqlesc($name).", ".sqlesc($descr).", '".$inames[0]."', '".$inames[1]."', '".$catid."', '" . get_date_time() . "', '".$infohash."', '".$articlesize."', '".$filecount."', ".sqlesc($fname).", '".$news."', '".$external."', '".$nfo."', '".$langid."','$visible', '$anon', ".sqlesc($tube).",'".get_date_time()."')");

And 

$visible = 1;

Upvotes: 1

Views: 46

Answers (2)

user4765675
user4765675

Reputation:

You need to define the column that you are inserting to specific type. A generic ALTER statement should follow this format. 

ALTER TABLE table_name ADD column_name column-definition;

I'm assuming you only want to have a 1 or a 0 so the following should work: 

ALTER TABLE `articles` ADD `visible` TINYINT NOT NULL DEFAULT 1

Upvotes: 1

jeroen
jeroen

Reputation: 91762

You forgot to add a data type.

You probably want something like:

ALTER TABLE `articles` ADD `visible` TINYINT NOT NULL DEFAULT 1

If that does not solve it, you are setting default values in your code somewhere.

Upvotes: 2

Related Questions