0

Yes I know this has been asked before and I literally copied code from multiple answers from this site trying to get it to work. This is the code I've been using now but it keeps allowing me to enter duplicate entries.

$query = mysqli_query($con, "SELECT * FROM Email WHERE Email = '".$testemail. "'");
if(mysqli_num_rows($query) > 0){
    echo "Email is already in use.<br>";
}else{
    $query = mysqli_query($con, "SELECT * FROM Username WHERE Username = '".$testname. "'");
  if(mysqli_num_rows(  $query) > 0){
    echo "Username is already in use.<br>";
}else{
    $sql = "INSERT INTO users (Username, Password, Email, Firstname, Lastname, Lastlogin, Registered) VALUES ('$testname', '$testpass', '$testemail', '$testfirstname' , '$testlastname', '$lastlogin', '$registered')";
    if ($conn->query($sql) === TRUE) {
        echo "New account created successfully<br>";
    } 
  }
}

Is the specific code that should stop this from happening but here is the full page:

First time that I'm working with a login system like this so I wouldn't be surprised if I'm making some stupid mistake.

EDIT: I tried editing it but its still not working, I also made the 'Email' and 'Username' column unique in my database. But all this does is stop the data from being inputed at all. I also tried a workaround where it displays a error at error number 1062 but that happens hasn't worked yet.

The new code

I missed something obvious as well, I'm using a IF statement so it only loops through the fie query check once I think

Crecket
  • 718
  • 2
  • 7
  • 24
  • 1
    What are the values being compared? If the values truly should be unique then putting a unique constraint on the database columns would help as well. Side note: You may be wide open to SQL injection attacks with this code. – David Feb 19 '15 at 13:36
  • in the code link he's using mysqli_real_escape_string for sql injection. e.g. $testname = mysqli_real_escape_string($con, $_POST['username']); – justAnotherUser Feb 19 '15 at 13:47
  • Yes one of my friends told me to use the mysqli_real_escape_strings as a main counter vs those. Are there better/safer way to protect vs injections? – Crecket Feb 19 '15 at 13:54
  • @Crecket: Prepared statements help, as well as carry other performance benefits: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – David Feb 19 '15 at 16:22

2 Answers2

1

I suspect those SELECT queries are failing because they're not reading the same data that's inserted into the database. Here's your insert:

INSERT INTO users ...

But you're selecting from different tables:

SELECT * FROM Email ...
SELECT * FROM Username ...

If the values are in a table called users, why are you selecting from tables called Email and Username? Maybe you meant to select from users instead? Which also means you can do it in one query instead of two:

SELECT * FROM users WHERE Username = '".$testname. "' OR Email = '".$testemail. "'
David
  • 208,112
  • 36
  • 198
  • 279
  • Could you perhaps edit my code so that it checks all values from the column? I think that it only checks the first row for some reason – Crecket Feb 19 '15 at 16:19
  • @Crecket: It's not really clear what you mean. For one thing, there is no "first row". Row ordering in the database isn't guaranteed unless explicitly defined. If usernames and/or emails should be unique then I would *expect* the above query to only return a single row. (Or zero rows.) In what way is your updated attempt not working? Feel free to edit the question to indicate that. – David Feb 19 '15 at 16:23
0

I made the name column UNIQUE within my database and than added new data through this line:

$sql = "INSERT INTO users (Username, Password) VALUES ('$testname', '$testpass')";
if (!($conn->query($sql))) {
    echo "Username is already in use";
}else{
    echo "New account created successfully";
}

If the entered data already exists it will give a error which than simply sends a message that the name is already in use.

Crecket
  • 718
  • 2
  • 7
  • 24