INSERT INTO new MYSQL row only if all values are present

Question

I'm trying to work out a MySQL query that only adds a new row to an existing table if all fields have a value present, otherwise it should be dropped/ignored.

The query I have at the moment is as follows:

 INSERT INTO markers (`name`, `address`, `details`, `date`, `link`, `lat`, `lng`, `type`)
    VALUES ("{1}", "{2}", "{3}", "{15}", "{4}", "{11}", "{12}", "{5}")

If {15} is left blank by my form then I don't want any of the other values added to the table.

Hope that makes sense!

Thanks

You should check and handle any input values in the scripting language that is handling the form submits (you didn't specify). — Digital Chris, Aug 27 '15 at 20:28
simple use if(!empty('field-name') && !empty('second-field')){--your query--} — Prashant Valanda, Aug 27 '15 at 20:31
Are you working with PHP or something else? Post your complete code, then we can help you. — etalon11, Aug 27 '15 at 20:39

score 3 · Answer 1 · answered Aug 27 '15 at 20:45

I would suggest you do a couple of different solutions.

You could setup your database to not allow null values, and then when you go to insert, handle your null errors (probably not the best solution out there though). If you need to have null values, then you will have to handle this in code.

The other thing is to implement form validation. You can do this with JavaScript, code behind, or even both. Both is suggested as some people may have JavaScript disabled on their browser.

Thanks Green Chilli, adding a NOT NULL sorted it:0) – Alf1ee Aug 28 '15 at 20:44 — Alf1ee, Aug 28 '15 at 20:44

score 1 · Answer 2 · answered Aug 27 '15 at 20:33

1

I'd say check the inputs on the front-end with Java, C#, Swift, whatever. Then if an entry is blank, just make a pop-up message letting your user know to fill out every field, or for your application to just skip that record.

answered Aug 27 '15 at 20:33

scubasteve623

637
4
7

score 0 · Answer 3 · edited May 23 '17 at 11:52

If you want to do this directly in MySQL, you can create a trigger that checks the values before they are inserted, and throw an error if some validation rule is not fulfilled.

Example:

delimiter $$
create trigger no_emtpy_values before insert into markers
for each row
begin
    declare msg varchar(255);
    declare condition_fail int default 0;
    -- Write the appropriate validations and set condition_fail to 1
    -- if the validations are not fulfilled. For example:
    if NEW.`date` IS NULL then
        set condition_fail = 1;
    end if;
    -- That "NEW" refers to the row about to be inserted

    -- If the validation rules are not fulfilled, throw an error:
    if condition_fail then
        set msg = "You can't insert the data!";
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    end if;
end $$
delimiter ;

References:

Hope this helps

INSERT INTO new MYSQL row only if all values are present

3 Answers3