Check to see if MySQL Data exists?

[Twango]

I'm creating a user-creation form.I have the form username form: $_POST[Account]Now... here's my problem..I want something that checks to see if it already exists.Basically i add w/$sql= "INSERT INTO Accounts (Usr, Pw, Special)VALUES ('$_POST[Account]', '$_POST[Password]', '$_POST[sPECIAL]')";So how would i check to see (before i add the account) if it already exists? thanks!

[Fmdpa]

Usually, the user has a unique identifier, such as an email address. At registration, you could do this:

$user_exists = $db->query("SELECT * FROM Accounts WHERE email = '" . $db->real_escape_string($email) . "'")->num_rows;if ($user_exists) //errorelse //register user

Basically, what this does is selects all rows from the database where the email is equal to the registration email. ->num_rows gets the number of rows returned by this query. If there are zero rows, $user_exists will evaluate to false. If there are more than zero rows, the if conditional statement will pass, and you can echo an error.

[Twango]

Usually, the user has a unique identifier, such as an email address. At registration, you could do this:

$user_exists = $db->query("SELECT * FROM Accounts WHERE email = '" . $db->real_escape_string($email) . "'")->num_rows;if ($user_exists) //errorelse //register user

Basically, what this does is selects all rows from the database where the email is equal to the registration email. ->num_rows gets the number of rows returned by this query. If there are zero rows, $user_exists will evaluate to false. If there are more than zero rows, the if conditional statement will pass, and you can echo an error.

Not at all working for me...I cant have a unique ID right now... I want it to check if the USERNAME already exists...I changed your code to this:$user_exists = mysql_query("SELECT * FROM Accounts WHERE Usr = '" . $con->real_escape_string($_POST[Account]) . "'")->num_rows;Usr is the table column for Accounts, and $_POST[Account] is where i get the data for my account,for some reason $db->query wasnt working, (i use mysql_ query [Example: mysql_query($sql,$con)] for adding the data finally)could the same be happening with $con->real_escape_string?Thanks!

[thescientist]

probably. I didn't really follow along with Fdmpa's explanation, so I'll give you a second opinion I guess.anyway, you get the user name, and then you can put it in your query. then you just have to do a couple of steps to figure out what you got.

<?php $username = real_escape_string($_POST[Account]);$con = mysql_connect("localhost",username,password);if (!$con){  die('Could not connect: ' . mysql_error());};$sql = "SELECT * FROM Accounts WHERE email = $username";$users = mysql_query($sql,$con);if(mysql_num_rows($users) > 0){  echo 'user ' . $username . 'exists';}else{   echo 'user not found';};?>

[Fmdpa]

I was trying to figure out what $_POST['Account'] is...now that you say it is the username, that clears things up. Some sites use the email address as the username, so that's what I was doing.The reason mine didn't work is because I used object-oriented MySQLi. Basically, thescientist did the exact same thing, just using procedural MySQL. MySQLi has some advantages (like prepared statements), so that's why I use it. The complete code would look like this:

$db = new mysqli('localhost','username','pass','database');$user_exists = $db->query("SELECT * FROM Accounts WHERE Usr = '" . $db->real_escape_string($_POST['Account']) . "'")->num_rows;if ($user_exists) //errorelse //register user

But if you are already using plain mysql, then thescientist's suggestion would be fine.Tip: $db-> is basically the same as mysqli_ in the function name.

[thescientist]

ah, that's what tricks you were hiding up your sleeves