Ignore blank fields in form before writing to database using MySQLi

Question

ok so the problem is that when this form is submitted and there are some input fields that have been left blank the blank data is written to the database and because I do not wish for duplicate inputs of each field the second person who fills in the form can't submit unless they have left different fields empty.

I do not wish to make it so people have to fill out all fields as not everyone will have the usernames that each field asks for.

if(isset($_POST["signupbtn"])) {
    if ($log_username) {
        /// getting data from submitted form into local variables
        $x = preg_replace('#[^a-z 0-9]#i', '', $_POST['xbox']);
        $p  = preg_replace('#[^a-z 0-9]#i', '', $_POST['psn']);
        $s = preg_replace('#[^a-z 0-9]#i', '', $_POST['steam']);
        $ip = preg_replace('#[^0-9.]#', '', getenv('REMOTE_ADDR'));
        // DUPLICATE DATA CHECKS FOR GAMER PROFILES
        $sqli = "SELECT username FROM player WHERE xbox='$x' LIMIT 1";
        $query = mysqli_query($db_conx, $sqli); 
        $x_check = mysqli_num_rows($query);
        // -------------------------------------------
        if ($x_check > 0){ 
            echo "Xbox Gamer-Tag already linked to a user on this website";
            exit();
        } else if (is_numeric($x[0])) {
            echo 'Xbox Gamer-Tag cannot begin with a number';
            exit();
        }
        $sqli = "SELECT username FROM player WHERE psn='$p' LIMIT 1";
        $query = mysqli_query($db_conx, $sqli); 
        $p_check = mysqli_num_rows($query);
        // -------------------------------------------
         if ($p_check > 0){ 
            echo "PSN User already linked to a user on this website";
            exit();
        } else if (is_numeric($p[0])) {
            echo 'PSN User cannot begin with a number';
            exit();
        }
        $sqli = "SELECT username FROM player WHERE steam='$s' LIMIT 1";
        $query = mysqli_query($db_conx, $sqli); 
        $s_check = mysqli_num_rows($query);
        // FORM DATA ERROR HANDLING
         if ($s_check > 0){ 
            echo "Steam account already linked to a user on this website";
            exit();
        } else if (is_numeric($s[0])) {
            echo 'Steam account cannot begin with a number';
            exit();
        } else  { $sqli = "INSERT INTO player (id, username, xbox, psn, steam, ip, created, lastupdated, notecheck)        
                VALUES ('$id','$log_username','$x','$p','$s','$ip',NOW(),NOW(),NOW())"; 
                $query = mysqli_query($db_conx, $sqli);
                   echo "Gamer Profiles Updated";
                }   

        exit();

            if (!file_exists("p_player/$log_username")) {
                mkdir("p_player/$log_username", 0755);
            } 


     }


}

Above is the php and this works fine it writes the information to the table without a problem other then the fact it also rights the blank data to the database. If any could point me in the right direction or even solve my problem I will appreciate it a lot. I have looked all over google but I can't seem to find anything because all the answer I do find are not exactly what I mean and as I am new to PHP the task seems to be harder to find the correct answer I am looking for. Thanks for you time in reading this.

Are you saying a that you have made a column that can be blank, unique? — Rob, Jan 16 '14 at 06:24
yes the blank field gets made into a unique field on submitting this form so that others cannot submit the same field blank as the blank field slot has already been taken — Luke G, Jan 16 '14 at 06:26
If the field is allowed to be blank why is that field unique, that doesn't make sence? — Rob, Jan 16 '14 at 06:26
I know right. I personally thought that if a field was left blank then nothing would get written into the database on that row, column or anything as it is blank but from what I have found out that isn't the case and it needs to be made to ignore blank fields so it doesn't get written to using the blank data — Luke G, Jan 16 '14 at 06:30
U either need to check to make sure that field isnt blank before the insert, or you need to allow duplicates on that field. From what i can tell, u looking for all usernames for certain apps. So u i would allow dups, and do a query on the table to ensure that column does contain the same username. — Rob, Jan 16 '14 at 06:31
The problem is the fact that you can't get duplicate Xbox Tags, PSN or Steam accounts and with the internet being full of trolls these days I need to stop people from making a troll account. I think I will just make more tables on the database and split the inputs into different forms. Thanks for the help — Luke G, Jan 16 '14 at 06:35

score 0 · Answer 1 · edited Jan 16 '14 at 06:55

0

You can still allow multiple NULL values. Set those unique columns to have a default value of NULL, so if nothing get passed it will store NULL instead of an empty value.

edited Jan 16 '14 at 06:55

Aniket Kulkarni

12,825
9
67
90

answered Jan 16 '14 at 06:34

Toby Liu

1,267
9
14

If it is that easy then that is awesome. Thanks dude. I did think it was kind of strange the way it would right the empty fields. – Luke G Jan 16 '14 at 06:39
OK so I just went into my database and changed them all to null. No on submitting the form the form submits the null values a value of the empty data again. – Luke G Jan 16 '14 at 06:45
You need to establish a DEFAULT value for any new INSERTS. This will require you to edit some settings with your columns. See: [How to set a default value for an existing column](http://stackoverflow.com/questions/6791675/how-to-set-a-default-value-for-an-existing-column) – Toby Liu Jan 16 '14 at 06:54
Ok so thank you for your answer but I can't make out what they are saying to be honest. I have set my database tables to default null. – Luke G Jan 16 '14 at 07:00
I think the best approach for you, then, will be to drop the UNIQUE and use input validation to check if a duplicate exists. – Toby Liu Jan 16 '14 at 07:11

score 0 · Answer 2 · answered Jan 16 '14 at 06:39

0

You pretty much got it, I would do the following

For steam for example

if ($s != ''){
        $sqli = "SELECT username FROM player WHERE steam='$s' LIMIT 1";
        $query = mysqli_query($db_conx, $sqli); 
        $s_check = mysqli_num_rows($query);
        // FORM DATA ERROR HANDLING
         if ($s_check > 0){ 
            echo "Steam account already linked to a user on this website";
            exit();
        } else if (is_numeric($s[0])) {
            echo 'Steam account cannot begin with a number';
            exit();
        }
} else  { 
                $sqli = "INSERT INTO player (id, username, xbox, psn, steam, ip, created, lastupdated, notecheck)        
                VALUES ('$id','$log_username','$x','$p','$s','$ip',NOW(),NOW(),NOW())"; 
                $query = mysqli_query($db_conx, $sqli);
                   echo "Gamer Profiles Updated";
}

Then allow duplicate values in that field. This will allow blank fields, but not duplicate fields that contain data.

answered Jan 16 '14 at 06:39

Rob

409
4
20

I believe you are asking me to swap ($s_check > 0) with ($s != '') . As soon as I do that I start getting { errors – Luke G Jan 16 '14 at 06:52
Give me a moment and I will re code it again and make sure I never messed up – Luke G Jan 16 '14 at 06:53
ok so I have done that again and all comes back fine however it still gives me the same problem – Luke G Jan 16 '14 at 06:58
Sorry Luke i got side tracked. leave your `$s_check > 0` as is just place that `if` before the `$sqli` and only insert in the `else` (u can pretty much copy paste what i put there). Also make sure that column can accept duplicate values (IE '') – Rob Jan 16 '14 at 08:49
I have solved the problem by splitting up the forms. This will also help when it comes down to updating a field anyway as I have had problems allowing to update fields. Now when stuff gets updated it only needs to replace one field. Thanks for the help tho dude. – Luke G Jan 16 '14 at 18:45

Ignore blank fields in form before writing to database using MySQLi

2 Answers2