Function no longer working / wont check if email already exists

[justin7410]

Hey freaks,

 

i have been working on a log in / register system for a couple days now, and i am in the process of finalizing my end users activation of a newly registered account.

 

I was having no real issues until now, to where i am utterly confused as to why i am having such a core problem.

 

So for the first part i had a user register, and wanted to make sure the user is not already entering an email that already exists in my Database.

 

 

if (email_exists($_POST['register_email']) === true) {
$errors[] = 'The email you provided is already in use. If you are having trouble remembering your user info click <a href="#">here</a>';
}

the error goes to an errors array i have setup with all the errors that would display to the end user if any occur.

 

the email_exists() is the following :

function email_exists($email) {


$username = sanitize($email);
$query = (" SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' ");
$results = mysql_query($query);


if(mysql_error()) {
echo 'MySQL Error: '. mysql_error() .'<br>';
echo 'Query: '.$query;
}  else if(!mysql_num_rows($results)) {
//###  row has not been returned 
}  else {
//### row has been returned
}


return (mysql_result($results, 0) == 1) ? true : false;
}
 

 

Now everything in the function calls correctly, and as you see in the error check, row will be returned.

 

Yet, as you see in my IF statement in the error check above, nothing is being returned with the variable i am inputting ( yet the other IF statement all seem to work with the same variable $_POST)

 

now this i figured was the problem to my MAIN and number one issue. Getting my user activation working.

 

if(isset($_GET['success']) === true && empty($_GET['success']) === true){ 
?>
<h2> Thank you, your account has been activated</h2>
<h3> You can now log in and access the site and join the disscussions in out forum !</h3>
<?
} else if (isset($_GET['email'], $_GET['email_code']) === true){ 
$email  =  trim($_GET['email']); 
$email_code = trim($_GET['email_code']);


if (email_exists($email) === false ){
$errors[] = ' We have a problem, we couldn\'t find that email address !'; // THIS IS THE ERROR GIVEN TO USER
} else if (activate($email, $email_code) === false) {
$errors[] = ' We had a problem activating your account ';
}
if(empty($errors) === false){
?>
<h2>Oops...</h2>
<?
echo output_errors($errors);
} else {
header('Location: activate.php?success');
exit();
}


} else {
header('Location:  index.php');
exit();
 }
 

Now above i have highlighted what conditional is passed, when a end user click on the return link to activate their account.

 

as i mentioned above , i know its the email_exists function, since not only does that conditional fail . in database i can create multiple users with the same email.

 

Please guys any help would be greatly appreciated. 

 

I am a PHP / Programming noob , but i am trying my best to debug and catch my issues. i just cant figure this one out on my own .

 

 

 

[justin7410]

just wanted to bump this up ...

 

i am still struggling to find an answer .. 

 

i do realize i added $username in the function above ... i have changed that to $email = sanitize($email);

 

still no luck

 

i have also changed the function to the following:

 

function DoesEmailExist($email) {
    $query = 'SELECT COUNT(`user_id`) FROM `users` WHERE `username` = ' . "'" . mysql_real_escape_string($email) . '"';
    if (!$rs = mysql_query($query)) {
        return -1;  //cannot run query to check email
    } else {
        if (!$numRows = mysql_num_rows($rs)) {
            return -2;  //cannot check the number rows in result set
        } else {
            return ($numRows == 0) ? false : true;
        }
    }
}

 

Any php gurus out there want to take a look and give a helpful hand ?

 

i really do appreciate any help . 

[DavidAM]

 

 

if (email_exists($email) === false ){

    if (!$rs = mysql_query($query)) {
        return -1;  //cannot run query to check email
    } else {
        if (!$numRows = mysql_num_rows($rs)) {
            return -2;  //cannot check the number rows in result set
        } else {
            return ($numRows == 0) ? false : true;
        }

 

 

Did you change your check in the main file (the first line of code above).

Returning -1 or -2 from this function will NOT be interpreted as FALSE (not even with two-equal signs).

 

Be aware, a SELECT COUNT(... will always return a row. Your query will always return 1 row, unless it fails. The value of the count column may be zero, but there will be a row.

[justin7410]

Hey David thanks for the reply, i did change the conditional after to make sure the new function name was declared. in all honesty the new function you speak of , was not really my self created function , it was just another suggestion someone else had made. im more curious as to why my first function 

 email_exists();

is failing , while the exact function is working for the another validation function called user_exists();. To me it would just mean that my variables im passing inside the function are not passing, yet this is not the case. if you have more suggestions as to why the first function is failing, that would be of more value to me , although i appreciate all feedback. thanks again

Edited April 9, 2013 by justin7410

[justin7410]

Hey Guys,

 

i just wanted to bump this since the issue is not solved, and is continually giving me problems.

 

so i have a very simple function called email_exists();

function email_exists($email) {
$email = sanitize($email);
return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email'"), 0 ) == 1) ? true : false;

now the query has been checked and is returned ok.

 

i then get to the register page:

 

 

if (empty($errors) === true) {
if (user_exists($_POST['register_username']) === true) {
$errors[] = 'Sorry, the username \'' . $_POST['register_username'] . '\' has already been taken. Please choose another username.';
}
if (preg_match("/\\s/", $_POST['register_username']) == true) {
$errors[] = 'Sorry your username must not contain any spaces!';
}
if (strlen($_POST['password']) < 6 ){ 
$errors[] = 'Your Password must be a minimal 6 characters long';
}
if ($_POST['password'] !== $_POST['confirm_pass']) {
$errors[] = 'Your Passwords do not match, please make sure both passwords are the same!';
}
if (filter_var($_POST['register_email'], FILTER_VALIDATE_EMAIL) === false) {
$errors[] = 'A valid email adress is required';
}
if(email_exists($_POST['register_email']) === true){  // THIS CONDITIONAL DOES NOT PASS
$errors[] = 'The email you provided is already in use. If you are having trouble remembering your user info click <a href="#">here</a>';
        
         }
  }
}
print_r($errors);
print_r($_POST['reigster_email']);

Now when i print both these things out, no error is showing , when i use the same email that already exists in the database. Yet, the variable of the $_POST shows the email being passed correctly.

 

The reason as to why i am so utterly confused is that , i have an exact duplicate function that checks if an username is already taken.

 

 

function user_exists($username) {

 

$username = sanitize($username);
$query = (" SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' ");
$results = mysql_query($query);


if(mysql_error()) {
echo 'MySQL Error: '. mysql_error() .'<br>';
echo 'Query: '.$query;
}  else if(!mysql_num_rows($results)) {
//###  row has not been returned 
}  else {
//### row has been returned
}


return (mysql_result($results, 0) == 1) ? true : false;
}

same logic different variables. this passes with no problems and everything works fine.

 

i am beyond frustrated at this point. and my lack of programming knowledge and php knowledge is somewhat handicapping me with a solution from the forums.

 

any help or suggestions... better yet a solution would be GREATLY appreciated. 

 

thanks guys

[mac_gyver]

you need to actually debug why your code is not returning the expected result. by just randomly trying different code, you are never going to find out why it is failing and the reason it is failing might be somewhere besides the code you are changing.

 

the first step would be to form the query statement in a php variable and then echo that variable so that you know what the actual query statement is.

[justin7410]

you need to actually debug why your code is not returning the expected result. by just randomly trying different code, you are never going to find out why it is failing and the reason it is failing might be somewhere besides the code you are changing.

 

the first step would be to form the query statement in a php variable and then echo that variable so that you know what the actual query statement is.

 

thank you sir for your suggestion

 

debugging is definitely something i want to get better at , and i know is crucial in solving my own issues without having to rely on other people. i think this will come with more exp, and gaining more of a grasp of the logic. its not only understanding what you want to do with code but what it is your actually doing.

 

anyway, i will give that a shot and hopefully i can find a better path at finding the solution.

 

any other suggestions are still very welcome.

[DavidAM]

Debugging 101 - PRINT

 

Add a bunch of print() (or echo()) statements at key points (just before an IF test, etc.) So you can see if the variables contain what you would expect.

 

 

You definitely do NOT want to reduce the function down to a single line of code if you are are not sure it is working!

 

Going back to your first post. I added a few print statements so we can see what is happening.

 

 

 

<?php

function email_exists($email) {
	$username = sanitize($email);
	$query = (" SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$username' ");
## Oops, you were using $email which is the UN-sanitized version of that value
	
# Let's see what the query looks like 
	printf("Query: %s<BR>", $query);

	$results = mysql_query($query);

	// Let's see if the query succeeded
	if (!$results) {
		printf("Query Failed!<BR>%s<BR>", mysql_error());
		/* We probably should do something more intelligent than just return false, but 
			I don't know the application so, hey, I didn't find the email address. */
		return false; 
	}

	// OK, The query executed, let's see what we found
	if (mysql_num_rows($results) == 0) {
		/* This should never happen. Since we used COUNT(user_id), we should at least
			get a row with a count of zero. */
		printf("Query Returns Zero Rows???<BR>");
		/* We probably should do something more intelligent than just return false, but 
			I don't know the application so, hey, I didn't find the email address. */
		return false; 
	}
	
	// OK, Now let's see what we got
	$theCount = mysql_result($results, 0);
# Show the count, just so we know
	printf("Query Says Count is %d<BR>", $theCount);
	
	return ($theCount > 0);
}

If you have a redirect ("header(Location: ...") after you call this function, you may need to change it to a die() statement while you debug so you can see these printed messages.

[justin7410]

Hey David, that was an awesome post, and very helpful.

 

before i go ahead and spend time on going over this .. 

 

i just wanted to add ..after hours on end debugging this code to see why this is not working as a function

 

 

i found the problem , being that the query

 

(" SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' ");

 

is always returning a false bool , due to the reason that the COUNT() is returning 0 every time it queries.

 

this is not making any sense as to why this is happening.

 

EDIT: just wanted to add the results of the code i added thanks to DavidAM

 

as i suspected the results are the same as what i feared.

 

Query: SELECT COUNT(`user_id`) FROM `users` WHERE `email` = 'justin7410@email.com' 
Query Says Count is 0
Array ( ) //  = $errors()

 

i played around with my SQL to see if the field of email was having issues. but when i do count() for the email without the variable it counts correctly

 

although i am very happy to find the core problem of the issue. it just makes me scratch my head as to why the query result is 0 for every email registered.

 

 

the data field is labeled correctly and the names in the field are correct.

 

when i echo the query it returns correct

 

SELECT COUNT(`user_id`) FROM `users` WHERE `email` = 'justin7410@email.com'

 

unlike for the users function where it returns the correct count for a user found.

Edited April 11, 2013 by justin7410

[DavidAM]

I see you edited your post while I was responding. If the COUNT is zero, then there are no records. See the red statements below:

 

It is kinda hard to understand the problem, you need to be more exact in your statements:

 

being that the query ... is always returning a false bool

The query will never return a boolean false. Are you talking about the function or the call to mysql_query(). If the call to mysql_query() returns false, then the query failed. The call to mysql_error should tell you exactly why it failed.

 

If the query looks correct, run it in your SQL control panel (phpmyadmin or whatever your host provides). There could be a number of problems: column or table name is wrong; database user does not have SELECT rights on the table/column; the email column width is too small and does not actually contain the full email address; the email address in the database has spaces in front or escaping characters inside, table/column is case-sensitive; etc. If you get the correct data from the SQL control panel, then the problem is in the PHP (or the database credentials).

 

Try this in the SQL control panel:

SELECT CONCAT('|', email, '|') AS emailD FROM users
WHERE email like '%justin%'

or this

 

 

SELECT CONCAT('|', email, '|') AS emailD FROM users

and have a look at your data.

 

 

 

I don't know how I missed this before, but this statement

 

 

 

$query = (" SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$username' ");

 

does NOT need the parenthesis. I don't think it is causing a problem, but you should take them out:

 

$query = " SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$username' ";

[justin7410]

I see you edited your post while I was responding. If the COUNT is zero, then there are no records. See the red statements below:

 

It is kinda hard to understand the problem, you need to be more exact in your statements:

 

The query will never return a boolean false. Are you talking about the function or the call to mysql_query(). If the call to mysql_query() returns false, then the query failed. The call to mysql_error should tell you exactly why it failed.

 

If the query looks correct, run it in your SQL control panel (phpmyadmin or whatever your host provides). There could be a number of problems: column or table name is wrong; database user does not have SELECT rights on the table/column; the email column width is too small and does not actually contain the full email address; the email address in the database has spaces in front or escaping characters inside, table/column is case-sensitive; etc. If you get the correct data from the SQL control panel, then the problem is in the PHP (or the database credentials).

 

Try this in the SQL control panel:

SELECT CONCAT('|', email, '|') AS emailD FROM users
WHERE email like '%justin%'

or this

 

 

SELECT CONCAT('|', email, '|') AS emailD FROM users

and have a look at your data.

 

 

 

I don't know how I missed this before, but this statement

 

 

does NOT need the parenthesis. I don't think it is causing a problem, but you should take them out:

 

$query = " SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$username' ";

 

 

Hey DavidAM,

 

thanks again for the reply.

 

regarding the query returning false . all i meant was when 

 

var_dump(return (mysql_result($results, 0) == 1) ? true : false;)

 

 

it would always return false unlike the passing conditionals i have for username which would return true and give an error. this is how i figured the query was an issue before i read your previous post.

 

now regarding your two sql querier:

 

 

SELECT CONCAT('|', email, '|') AS emailD FROM users
WHERE email like '%justin%'
 
or this


SELECT CONCAT('|', email, '|') AS emailD FROM users 

 

 

i just dont understand what your getting at with this ?

 

in myphpadmin these queries all work , but so does 

SELECT COUNT( `user_id` ) 

FROM `users` 

WHERE email LIKE '%justin%'..

 

i just cant get that passing variable which is being sanitized wont match up ..

 

thanks for the patience btw..

 

 

[DavidAM]

So you are saying that if you run SELECT COUNT(`user_id`) FROM `users` WHERE `email` = 'justin7410@email.com' in myphpadmin it returns a count of 1 (one). But the function I posted above prints that query, and says "Query Says Count is 0"?

 

I can't see how this could happen. Copy-and-Paste the function code as it is now (be sure to use [ code ] tags). Also Copy-and-Paste about five or ten lines around where you are calling that function.

 

There has to be a logical explanation.

 

 

Oh, those other two queries. I was saying to run them in the SQL console (myphpadmin). With the pipe-character concatenated on either side of the email address. That way if the data was inserted incorrectly you might spot the problem.

[justin7410]

so david,

 

i figured out the reason why the query is not working and why this whole time i am having such a difficult time

 

as you mentioned there was a good possibility that there was a space or extra character in the field.

 

this was the exact issue , that when the email is being inserted it has a space in the field first.

 

so when typing the sql query email = $email i simply had to put a space infront of the variable email.

 

now how can i escape that space from being inserted ?

[jcbones]

Always trim data before database insertion.  This will eliminate the problem you just had.