TalkPHP - Check that values aren't the same

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - Check that values aren't the same (http://www.talkphp.com/mysql-databases/4599-check-values-arent-same.html)

rguy84

06-24-2009 04:55 AM

Check that values aren't the same

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.

All times are GMT. The time now is 05:18 PM.