If variable is in db then stop- if variable is not- then enter it

Question

i have been trying to get this script done for a while now - im kind of new to php and mysql but i have been trying to get this to check the db for the username and then if the username exists - stop checking the db and if it doesn't exists add it to the db.

here is my code:

                    //input from application
                    $test = "wheelsmanx";
                    // Create connection
                    $conn = new mysqli($servername, $username, $password, $dbname);
                    // Check connection
                    if ($conn->connect_error) {
                         die("Connection failed: " . $conn->connect_error);
                    }

                    $sql = "SELECT mainusername FROM CCCpro_test";
                    $result = $conn->query($sql);

                    if ($result->num_rows > 0) {

                        while($row = $result->fetch_assoc()) { 
                            if ($row["mainusername"] === $test) {
                                echo "User Name Already In Use.";


                            }if($row["mainusername"] !== $test){
                                echo "this statement";

                            [code that inserts into db i can do this part myself]   

                            }

                            }
                        $conn->close(); 

                    } else {
                         echo "0 results";
                    }

                    $conn->close();

output:User Name Already In Use.this statement Note: i know why its doing this- i just dont know how to stop it from going through all the db with out doing exit — probsJustin, Sep 27 '15 at 01:03
You should use `if( condition ) { //error message exit; } else { //insert to db }` — aldrin27, Sep 27 '15 at 01:36

score 1 · Accepted Answer · edited May 23 '17 at 11:44

The problem with your code is that you do the INSERT of the new name inside an if statement that has confirmed the existence of that user already. In addition I think you messed up your SELECT statement by selecting all the users.

Look into INSERT ON DUPLICATE for a better way to do it, or revise your code as below.

                $sql = "SELECT mainusername FROM CCCpro_test WHERE mainusername = $test";
                $result = $conn->query($sql);

                if ($result->num_rows > 0) {
                    echo "User Name Already In Use.";
                }
                else{  //no rows selected therefore the user doesn't exist
                        [code that inserts into db i can do this part myself]   
                }

                $conn->close();

PLEASE READ I have somewhere to go so I am being lazy so I did not bind the $test variable therefore DO NOT copy and paste this code without updating it to bind the $test variable. Please read this post about PDO and variable binding to prevent SQL injection.

probsJustin · Answer 2 · 2015-09-27T23:36:59.263

here is my full working code if anyone needs it - it uses the post method - from an html form .... in case some one needs to hack it to pieces for something else

well guys i appreciate all of your help :D but i have found an answer or a way around it i suppose- i thought of it all night and day on how i could make it work and i came up with this

                    $servername = "127.0.0.1";
                    $username = "TESTUSER";
                    $password = "TESTPASS";
                    $dbname = "TESTDB";

                    $testusername = $_POST['mainusername'];
                    $testpassword = $_POST['mainpassword'];

                    //input from application
                    $test = $_POST['mainusername'];
                    $test2 = "0";
                    //Count switch 
                    $countswitch = "0";
                    // Create connection
                    $conn = new mysqli($servername, $username, $password, $dbname);
                    // Check connection
                    if ($conn->connect_error) {
                         die("Connection failed: " . $conn->connect_error);
                    }
                    $sql1 = "INSERT INTO CCCpro_test ( mainusername, mainpassword ) VALUES ('$testusername','$testpassword' )";
                    $sql = "SELECT mainusername FROM CCCpro_test";
                    $result = $conn->query($sql);

                    if ($result->num_rows > 0) {

                        while($row = $result->fetch_assoc()) { 
                            if ($row["mainusername"] === $test) {
                                echo "Im Sorry Username Already In Use";
                                 $countswitch ++;
                            }
                            }

                            if($countswitch == $test2){
                                echo "User Name Registered";

                                $db_handle = mysql_connect($servername, $username, $password);
                                $db_found = mysql_select_db($dbname, $db_handle);

                                if ($db_found) {

                                $result1 = mysql_query($sql1);

                                mysql_close($db_handle);

                                }
                            }
                            if ($countswitch == 3){
                                echo "this";
                            }   
                            } else {
                         echo "0 results";
                    }

                    $conn->close();

If variable is in db then stop- if variable is not- then enter it

2 Answers2