Handling Nulls from form input to SQL

Question

So,

I am trying to create dynamic SQL queries where we assume:

If the _GET or _POST doesn't have a variable set we call it NOT SET

If the _GET or _POST is set but the value is empty then we call it EMPTY

If the _GET or _POST is set but the value is not empty then we call it NON-EMPTY

Now we can easily store the EMPTY and NON-EMPTY variables in MySQL as they are (because we know the intention of the end user since the variables have been set)

OUR Mapping so far:

EMPTY & NON-EMPTY = whatever the value is...

(for date column in MySQL since it doesn't allow empty we put in 0000-00-00 00:00:00. We use this logic for both the INSERTS and UPDATES

NOT-SET in PHP = NULL in database? (since we don't know anything about the value)

Now this works perfectly for situations where data doesn't already exist for example INSERT statements, but what about UPDATE STATEMENTS?

What if the record already has a value?

I am thinking of using NOT SET variable as to be ignored in UPDATE statements?

So if, lets say POST["name"] is not set on INSERT, then we still insert it as a null

INSERT INTO person (name) VALUES (POST["name"]);

but, if it is an UPDATE statement, we ignore it completely.

UPDATE person 
isNull(POST["name"]) ? SET name = POST["null"]

My dilemma is what do we do for INSERTS and UPDATES when these variables are NOT SET?

SeanW333 · Accepted Answer · 2018-06-27 01:53:38Z

1

<html>
    <body>
        <form method="post">
            <label for="field_1">Field 1</label>
            <br />
            <input type="text" name="field_1">
            <br />
            <br />
            <label for="field_1">Field 2</label>
            <br />
            <input type="text" name="field_2">
            <br />
            <br />
            <label for="field_1">Field 3</label>
            <br />
            <input type="text" name="field_3">
            <br />
            <br />
            <input type="submit" name="submit" value="submit">
        </form>

        <?php

            // BASIC IDEA:

            // (This example assumes all incoming post values are strings,
            //  if you have, for example, integers, you'd need to remove the 
            //  single quotes around the values in the SQL statement)

            // Loop through POST array
            // foreach _POST variable as key => value
            foreach($_POST as $k => $v) {
                // if not null, blank, or "submit"
                if(!is_null($v)&& $v != ''&& $v != 'submit') {
                    if(!isset($strSQL)) {
                        // If this is the first value, start off the sql string w/ UPDATE
                        $strSQL = "UPDATE your_table " ."SET " .$k ." = '" .$v ."'";
                    } else {
                        // For subsequent values, include a comma and space before new value
                        $strSQL = $strSQL ."', SET " .$k ." = '" .$v ."'";
                    }

                }
            }

            // Finish off SQL Command, if one was ever started
            if(isset($strSQL)){
                $strSQL = $strSQL ." WHERE some_field meets_your_conditions;";
                // Run SQL Command (echo just used for example)
                echo $strSQL;
            }

        ?>

    </body>
</html>

edited Jun 27, 2018 at 1:53

answered Jun 25, 2018 at 3:05

SeanW333

4894 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vish Over a year ago

You understood what I wanted to know. I know I can do what you are doing, but is there a better or more non-tedious way to do this?

SeanW333 Over a year ago

Okay, check out the revised example above. Doesn't get much more DRY than that. If that's all you need, please remember to mark as answered. If you need more assistance, please post the code you have so far. Thanks!

Vish Over a year ago

thanks, upvoted the answer. I already have a much drier code, but thanks for the attempt :).

Aman Chhabra · Accepted Answer · 2018-06-25 02:39:55Z

0

In case of no set:

Insert:

INSERT INTO person (name) VALUES (NULL);

Update:

Don't call update if no set as the value can either be :

1) Null : If null no need to update it

2) Not Null: If not null, then you will be loosing values if you assign it null.

PS: This is my suggestion as per understanding of the requirement, but it may differ as per actual requirements.

answered Jun 25, 2018 at 2:39

Aman Chhabra

3,9241 gold badge25 silver badges39 bronze badges

Collectives™ on Stack Overflow

Handling Nulls from form input to SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related