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 :)