mySQL/PHP question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Smeagol
Forum Newbie
Posts: 14
Joined: Tue Jan 20, 2004 11:51 pm

mySQL/PHP question

Post 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
}
User avatar
Smeagol
Forum Newbie
Posts: 14
Joined: Tue Jan 20, 2004 11:51 pm

figured it out

Post 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! :)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
Smeagol
Forum Newbie
Posts: 14
Joined: Tue Jan 20, 2004 11:51 pm

Post 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?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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 
} 
?>
User avatar
Smeagol
Forum Newbie
Posts: 14
Joined: Tue Jan 20, 2004 11:51 pm

Post by Smeagol »

Thanks, much better... I didnt want to really have to do two queries :)
Post Reply