Help stopping multple entries of the same name in database!

[thesecraftonlymultiply]

Hi there, hope everyone is all well and good.  I am completely new - nice to meet you!

 

I have a small database which stores the username and passwords of users.  My problem is that I cannot stop users from being able to choose a username that already exists.  here is a snippit of the code:

 

 

//  CHECK TO SEE IF USERNAME IS ALREADY IN USE - INFORM USER IF IT IS

while ($userinfo = mysql_fetch_array($userlist)){

 

if ($newUser == $userinfo['username']) {

$status = "* That name is taken";

 

//  THE PART ABOVE WORKS FINE

 

} else if ($newUser != $userinfo['username']) {

insert_database();

}

}

// EVERYTHING FOLLOWING ELSE IF DOESN'T

 

function insert_database()

{

 

//  WHEN NEWUSER AND NEWPASS ARE ASSIGNED INSERT INTO DATABASE

if ($newUser && $newPass) {

$query = "insert into auth (userid,username,userpassword) ";

$query .= "VALUES(0000,'$newUser','$newPass')";

    mysql_connect("localhost","luke2727_luke","********")

                  or die("Unable to connect to SQL server");

    mysql_select_db("luke2727_login") or die("Unable to select database");

    $result = mysql_query($query) or die("Insert Failed!");

}

 

return;

}

 

 

 

I must be doing something wrong and I am thinking it is probably simple.  Can anyone help me out?  Thanks alot for reading and all the best, Luke

 

 

[psychowolvesbane]

I can see that you're missing single quotes around the sql value 0000.

[thesecraftonlymultiply]

Thankyou, that wasn't causing a problem because before and without username checking it was entering data fine.  I have changed though to tidy it up so thankyou.  Anything else?

[benphp]

Create your form page. Send the form page to a SQL page. Use header after the SQL to send them back, or wherever you want them to go.

 

In the SQL page, you need to verify that the user doesn't exist yet BEFORE you run INSERT.

 

SELECT username from auth where username = '$username';

 

if (rowcount == 0) {

    RUN INSERT CODE

} else {

    header('Location: myform.php');

}

 

[thesecraftonlymultiply]

thankyou for the reply.  is there no way to keep it within the same page?  thanks

[jonsjava]

First, go to your mysql table (I hope you have a primary key set up), and do this:

ALTER TABLE `auth` ADD UNIQUE (
`userid`
)

This will stop people from creating 2 users with the same name. The database won't allow it. 2nd, you need to attempt a cleanup on your code:

<?php
$link = mysql_connect("localhost","luke2727_luke","********") or die("Unable to connect to SQL server");
mysql_select_db("luke2727_login" ,$link) or die("Unable to select database");


//  CHECK TO SEE IF USERNAME IS ALREADY IN USE - INFORM USER IF IT IS
$newUser = addslashes($_POST['username']); //whatever your form uses for the username, change it in $_POST['HERE']
$newPass = addslashes($_POST['password']);
$sql = "SELECT * FROM auth WHERE username = $newUser;";
$result = mysql_query($sql);
while ($userinfo = mysql_fetch_array($result)){

if ($newUser == $userinfo['username']) {
   $status = "* That name is taken";

//  THE PART ABOVE WORKS FINE   

} else if ($newUser != $userinfo['username']) {
insert_database();
}
}

// EVERYTHING FOLLOWING ELSE IF DOESN'T

function insert_database($newUser, $newPass)
{
//  WHEN NEWUSER AND NEWPASS ARE ASSIGNED INSERT INTO DATABASE
if ($newUser && $newPass) {
$query = "insert into auth (userid,username,userpassword) ";
$query .= "VALUES(0000,'$newUser','$newPass')";
mysql_query($query) or die("Insert Failed!");
   }
   
   return;
}
?>

 

 

[benphp]

You can do it all on one page, but it works better if you don't. With a SQL page, you can run your SQL and return them to the same page - all invisibly. It helps keep your page clean, and you can put all your Update/Insert/Delete in them. Separate the SQL out by:

 

if (isset($_POST['btnDelete'])) { ....

  DELETE

if (isset($_POST['btnUpdate'])) { ....

  UPDATE

if (isset($_POST['btnInsert'])) { ....

  INSERT

}

 

It works very well.

 

[thesecraftonlymultiply]

sorry for the delayed reply...

 

thankyou very much for all your help!  I have it sorted now.  Much appreciated m:)