Check that values aren't the same

rguy84 · 06-24-2009, 04:55 AM

I am playing around (locally). I want to make up a sign up form for this cms-like thing I am building. I want to make sure that the user login and e-mail aren't in the db already.

My SQL Statement is:

Code:

INSERT INTO tbluser 
 (user_loginname, 
  user_pass,
  user_firstName,
  user_lastName,
  user_email)
 VALUES(
  $userLogin,
  $userPass,
  $userFirst,
  $userLast,
  $userEmail)
 (SELECT user_loginname, 
  user_email
  FROM tbluser
  WHERE NOT ( 
  user_loginname = $userLogin AND 
  user_email = $userEmail))

I get the following error:

Quote:

Could not add: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@yahoo.com)(SELECT user_loginname, user_email FROM tbluserWHERE NOT ( user_login' at line 1

Any hints?

Hightower · 06-24-2009, 07:28 AM

I think (off the top of my head) I've always done this with two seperate statements. Not sure if it's the best way but it does work. Something like:

PHP Code:


			
$r = mysqli_query("SELECT * FROM db WHERE username='$u' LIMIT 1");
$rows = mysqli_num_rows($r);
if($rows == 0) { // 0 means user doesn't already exist
//Put the new user details into the database
}

Wildhoney · 06-24-2009, 10:35 AM

Why are you wanting to know this information? Essentially, my reason for asking is that you could check this information on INSERT, if you wanted to. Therefore saving you a SELECT statement.

rguy84 · 06-24-2009, 05:46 PM

@Wildhoney, can you show me an example? All the examples I could find have the SELECT statement.

Wildhoney · 06-24-2009, 09:38 PM

You'll need to configure the indexes on your table. So the username and email address fields would become unique indexes. When you then attempt to INSERT into the table, an error will be thrown. This can be retrieved using the MySQL error number, and then handed accordingly.

It saves on a SELECT statement to check if it is unique or not.

Village Idiot · 06-25-2009, 01:08 AM

Put spaces between every line in the string, I noticed that the error said "tbluserWHERE NOT", it should be "tbluser WHERE NOT" with a space between tbluser and WHERE.

I've been writing queries all day, that is a common error I hit unless I make sure there was a space at the end of every line in the string.

Salathe · 06-25-2009, 10:50 AM

It also doesn't appear that string values are wrapped with quotes.