CODEWITHSUNDEEP
phpmysqldatabaseselect
Mike
Mike

Reputation: 103

Unique entry verification using MySQL and PHP

I need to verify that a user registering for a website enters a unique 16 digit number that no one else prior to him/her has entered.

The relevant database information is that the 16 digit numbers are stored in a column called card1, the name of the entire table is users, and the user-entered number is stored in $card1.

Here is what I have so far...

$query2 = "SELECT card1 FROM users WHERE card1='$card1' LIMIT 1";
$result2 = smart_mysql_query($query2);

if (mysql_num_rows($result2) != 0)
{               
    header ("Location: register.php?msg=exists");
    exit();
}

The idea is that it will find any examples already in the database and if it finds a duplicate, it will display and error message.

The problem is that it is continuing to allow users to register(submit their registration form to the db) even when there is a duplicate. Immediately after this block of code is the insertion call to the db with all of the user information collected from the form.

NOTE: I'm not very familiar with handling PHP error messages and what I've used is just an example that I found in another instance in the example code.

Upvotes: 0

Views: 263

Answers (4)

Nisarg
Nisarg

Reputation: 3282

try this one 

$query2 = "SELECT card1 FROM users WHERE card1='".$card1."' LIMIT 1";
$result2 = mysql_query($query2);

if (mysql_num_rows($result2) > 0)
{               
    header ("Location: register.php?msg=exists");
    exit();
}

Upvotes: 0

rkosegi
rkosegi

Reputation: 14678

First of all, create unique index in DB on this column. This is best practice:

ALTER TABLE `users`
ADD UNIQUE INDEX `card1` (`card1`);

I should modify your SQL as follows:

$query2 = "SELECT COUNT(1) FROM users WHERE card1='$card1'";
$res = mysql_query($query2);

$data = mysql_fetch_array($res);

if ($data == 1)
{               
   header ("Location: register.php?msg=exists");
   exit();
}

It will check for existence of row within table and return 0 or 1.

If exists (1) then it will redirect you.

Upvotes: 0

Manuel
Manuel

Reputation: 10303

Make the if statement like this:

$query2 = "SELECT * FROM users WHERE card1='$card1'";
$result2 = mysql_query($query2);

if ($result2 !== false)
{               
   header ("Location: register.php?msg=exists");
   exit();
}

Should fix the problem:)

Upvotes: 3

DaveRandom
DaveRandom

Reputation: 88697

The correct way to do this is to add a unique index on the field that holds the number that the use has entered (card1).

You will then try and INSERT the new row without trying to SELECT it first, and if this operation fails you redirect the user to the msg=exists page. This lets the database handle the duplicate detection and removes the problem inherent in your method - if two users submit the same number at the same time, there is no guarantee that SELECT -> INSERT will detect it. A unique index will detect and prevent this.

This will also have the advantage of reducing database traffic, since only one query is executed in order to get this happen.

Upvotes: 2

Related Questions