check for email and user in 2 different tables

supahfly

Hi there,

I have two tables: users1 and users2.
Both tables contain email and username.

When I want to register , I want to check if the inserted username and email address is already present in one of those 2 tables.
Then I want to give an error if email or user already exists in of the 2 tables.

Now I have this, but it is only on one table:
I think it is really bad, but that's why I am posting it in the newbie forum :-)

Thanks already !

$checkuser 		= mysql_query("SELECT username FROM tb_users WHERE username='$username'");
$username_exist = mysql_num_rows($checkuser);

$checkemail = mysql_query("SELECT email FROM tb_users WHERE email='$email'");
$email_exist = mysql_num_rows($checkemail);

if ($email_exist>0|$username_exist>0) {
//echo "Username or Email Already in Use.";
	$err[] = trns('item_exists');
			}

leatherback

Hm.. The main question to you would be: WHY do you have them stored in TWO tables? You'd at best have one table with people information, and use a record identifier in other tables to refer to that person. Is there a specific reason why you do not set your database up in that way?

supahfly

Yes, one table needs to be scanned all the time when a page is hit
The other table is a list of banned users, which we don't want to include in the full scans, so we save a little processing power ...

leatherback

Hm.. Personally, I would use a binary field 'Banned' which you set to 1 whenever a user is banned. As such you keep all the users in one table. Then, when you do generic query you can decide to include a where statement: where banned=0. That should be very effortless for the server, unless the majority of users are banned, and you have a massive table (100K+ records).

If you stay with your design, I think you might need a union in your select

bradgrafelman

leatherback wrote:
you have a massive table (100K+ records).

Heh, unless you have very crappy DB servers (isn't it time to throw away that 386?), 100K+ records is far from a "massive table". I'm no DB guru, but I'm sure those that are will tell you how they handle a couple of million rows without too much trouble.

Thus, even if you had 100,000+ rows, there's really no reason to worry about separating the data off into two separate tables (plus, it would make your question moot and the whole process much easier for you).

EDIT: Speaking of the Database forum, I went ahead and moved this thread on over to it.

Also, another benefit of keeping the data in one table would be that you wouldn't need a separate SELECT query for what you're talking about; just use a couple of UNIQUE keys and perform a normal INSERT. If the query fails and you get error #1062 back from MySQL (duplicate entry for a key), then you know that the username or e-mail wasn't unique.