verify data before goes live

jd57

I was wondering if there is a way to flag new database entries and make them go live after all data has been verified.
if so can someone explain how.
Regards JD

Drakla

put a field in the database for verified - default to zero. When you approve then set it non-zero. When you do your select pick fields that have the verified column non-zero.

jd57

I've already have a database with existing data, where would I place this new field, at the end or at the begining , also do I need to use enum and set it to 0 and when data verified reset it to 1, I'm not sure how this works, can you be more specific?

put a field in the database for verified - default to zero. When you approve then set it non-zero. When you do your select pick fields that have the verified column non-zero.

Drakla

To add the new column to your table

ALTER TABLE foo ADD verified tinyint not null default 0

Then when you say it's all good, assuming you've got your primary key as id

UPDATE foo SET verified = 1 WHERE id = whatever

Then when you pick data for display

SELECT a,b,c,d FROM foo WHERE verified = 1

Roger_Ramjet

A more fundamental solution to this is not to add the data to the live table untill it has been verified. Just insert it into a work table untill it has been validated and verified manually, then copy it into the master table when satisfied.

This is the general solution that has been used in relational databases for decades. It protects the master table from corruption with rubbish.

The other solution that Drakla proposes means that your queries will all require an additional WHERE condition. That is not always desirable and is easy to forget. It is also generally less efficient.

jd57

You have been very helpful, but I'm not sure exactely what to do, can you be so kind and add this field on the table below.
Thanks

DROP TABLE IF EXISTS ihsreg;
CREATE TABLE ihsreg (
id int(10) unsigned NOT NULL auto_increment,
Bloom_Name text NOT NULL,
Pod_Name text NOT NULL,
Pollen_Name text NOT NULL,
Hybridiser text NOT NULL,
Origin text NOT NULL,
Grower text NOT NULL,
Color_Group text NOT NULL,
Bloom_Type text NOT NULL,
Reg_Mini text NOT NULL,
Size_Range text NOT NULL,
Propagation text NOT NULL,
Bloom_Color text NOT NULL,
Bloom_Characteristics text NOT NULL,
Leaf_Characteristics text NOT NULL,
Bush_Characteristics text NOT NULL,
Photo_Credit text NOT NULL,
Bloom_Image text NOT NULL,
Pod_Image text NOT NULL,
Pollen_Image text NOT NULL,
Hybridiser_Image text NOT NULL,
Leaf_Image text NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY bloom_index (Bloom_Name(30))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Created by phpFormGenerator';

--
-- Dumping data for table ihsreg

Drakla

If you're using the two table method you'd just create another table with the same data as that one, then just transfer across with a SELECT then delete the row in the provisional table.

If you do want to use the verification flag then I've added the field at the end, but it could be anywhere.

CREATE TABLE ihsreg (
id int(10) unsigned NOT NULL auto_increment,
Bloom_Name text NOT NULL,
Pod_Name text NOT NULL,
Pollen_Name text NOT NULL,
Hybridiser text NOT NULL,
Origin text NOT NULL,
Grower text NOT NULL,
Color_Group text NOT NULL,
Bloom_Type text NOT NULL,
Reg_Mini text NOT NULL,
Size_Range text NOT NULL,
Propagation text NOT NULL,
Bloom_Color text NOT NULL,
Bloom_Characteristics text NOT NULL,
Leaf_Characteristics text NOT NULL,
Bush_Characteristics text NOT NULL,
Photo_Credit text NOT NULL,
Bloom_Image text NOT NULL,
Pod_Image text NOT NULL,
Pollen_Image text NOT NULL,
Hybridiser_Image text NOT NULL,
Leaf_Image text NOT NULL,
validated tinyint NOT NULL default 0,
PRIMARY KEY (id),
UNIQUE KEY bloom_index (Bloom_Name(30))
)