Jump to content

Check to see if MySQL Data exists?


Twango
 Share

Recommended Posts

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!

Link to comment
Share on other sites

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.

Edited by Fmdpa
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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';};?>

Edited by thescientist
Link to comment
Share on other sites

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.

Edited by Fmdpa
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...