Page 1 of 1
mySQL/PHP question
Posted: Tue Jan 20, 2004 11:51 pm
by Smeagol
Hi guys, my first post and needed some help with some code. I basically want to see if a certain table contains a username and email address which the user has entered in a form. If the sql query returns no rows or doesnt match the username or email address the user has entered, then obviously they are a new user. How can I express this in my 'if' statement. (ie how do I catch it if there are no rows returned.)
TIA - code is below. Should I compare ALL email addresses in the table to see if there are duplicates? I am confusing myself... please help

hehe.
$sql = "SELECT username, email_address FROM ".$table." WHERE username = '".$add_userName."' AND email_address = '".$add_emailAddress."'";
$users = mysql_query($sql);
$user = mysql_fetch_array($users);
if ( <what??> ) {
// do something
} else {
// do something else
}
figured it out
Posted: Tue Jan 20, 2004 11:59 pm
by Smeagol
Sorry, I was too premature in my excitement

hehe
Here was my solution... I think its a little hacked... anyone have any neater solutions, or am I spot on?
$sql = "SELECT username from ".$table." WHERE username = '".$add_userName."'";
$users = mysql_query($sql);
$matchedUserName = mysql_fetch_array($users);
$sql = "SELECT email_address from ".$table." WHERE email_address = '".$add_emailAddress."'";
$users = mysql_query($sql);
$matchedEmail = mysql_fetch_array($users);
if ( !$matchedUserName && !$matchedEmail ) {
// do something
} else {
// do something else
}
Ahh, my brain is melting, hehe!

Posted: Wed Jan 21, 2004 3:24 am
by twigletmac
You could simplify the code a bit:
Code: Select all
<?php
// SQL to SELECT the username to see if a record matches
$sql = "SELECT username from ".$table." WHERE username='".$add_userName."' AND email_address='".$add_emailAddress."'";
// Query the database with error handling in case the SQL statement
// doesn't work
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
// Now count the number of rows returned to see if the user is
// already in the database
if (mysql_num_rows($result) > 0) {
// user exists
} else {
// user is new
}
?>
Mac
Posted: Wed Jan 21, 2004 5:14 pm
by Smeagol
Yes, that would simplify it, however, doesnt the sql query still not verify that other users are already using this email address? I just wanted to see:
a) if the username already exists
b) if anyone else is using the entered email address
The sql query will only return a row if the specified username and email address are within that row. i.e.
username email_address
user1
user1@user1.com
user2
user2@user2.com
if I wanted to add a new user (via a form etc) and I entered 'user1' and '
user2@user2.com' respectively for username and email_address, the sql query would allow me to do that, and subsequently meet the else condition in the if statement.
Hmm... <insert newbie brain hurting> heheh. Thoughts anyone?
Posted: Wed Jan 21, 2004 5:29 pm
by microthick
Using twigletmac's code, we can do this:
Code: Select all
<?php
// SQL that returns any rows that contain the same username or the
// same email address. Notice that I changed it to OR rather than AND.
$sql = "SELECT username, email_address from ".$table." WHERE username='".$add_userName."' OR email_address='".$add_emailAddress."'";
// Query the database with error handling in case the SQL statement
// doesn't work
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
// If any rows were returned, either the username is already taken
// or the email address already exists in your database.
if (mysql_num_rows($result) > 0) {
// username exists or email_address exists
} else {
// user is new
}
?>
Posted: Wed Jan 21, 2004 5:31 pm
by Smeagol
Thanks, much better... I didnt want to really have to do two queries
