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
– probsJustin, Commented Sep 27, 2015 at 1:03
You should use if( condition ) { //error message exit; } else { //insert to db } — aldrin27
– aldrin27, Commented Sep 27, 2015 at 1:36

Community · Accepted Answer · 2017-05-23 11:44:25Z

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 · Accepted Answer · 2015-09-27 23:36:59Z

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();

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related