9 Replies - 1654 Views - Last Post: 14 July 2013 - 12:42 PM Rate Topic: -----

#1 Yeronox  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 102
  • Joined: 12-March 12

Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 02:43 AM

Hey everyone, so here`s the code:
function user_exist($email)
{
$conn = new PDO('mysql:host=localhost;dbname=php', 'root', '');
		$email = mysql_real_escape_string($email);
		$stm = $conn->prepare("SELECT COUNT(`user_id`) FROM `users` WHERE `email`=':email';");
		$email = '%'.$email.'%';
		$stm->bindParam(":email",$email,  PDO::PARAM_STR);
		$stm->execute();
		return (($stm->rowCount()>0)==1) ? true : false;
}

While the original code was something like:
function user_exist($email){
$email = mysql_real_escape_string($email);
$query = mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email`=':email';");
return (mysql_result($query,0)==1) ? true : false;
}

I would prefer to use PDO as a lot of users say it is better;
and anyway
return (mysql_result($query,0)==1) ? true : false;


this code doesn`t work, so I cannot use it.
Basically I have to check if the email entered already exists in my database.

This code isn`t working, too.
	function user_exist($email)
	{
		$conn = new PDO('mysql:host=localhost;dbname=php', 'root', '');
		$email = mysql_real_escape_string($email);
		$stm = $conn->prepare("SELECT COUNT(`user_id`) FROM `users` WHERE `email`='$email';");
		
		$stm->execute();
		return (($stm->rowCount()>0)==1) ? true : false;
		
	}

Any idea?

This post has been edited by Yeronox: 14 July 2013 - 02:46 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Error with PDO: check if email exists in a row (mySQL)

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3524
  • View blog
  • Posts: 10,169
  • Joined: 08-June 10

Re: Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 02:56 AM

View PostYeronox, on 14 July 2013 - 11:43 AM, said:

function user_exist($email)
{
$conn = new PDO('mysql:host=localhost;dbname=php', 'root', '');
		$email = mysql_real_escape_string($email);
		$stm = $conn->prepare("SELECT COUNT(`user_id`) FROM `users` WHERE `email`=':email';");
		$email = '%'.$email.'%';
		$stm->bindParam(":email",$email,  PDO::PARAM_STR);
		$stm->execute();
		return (($stm->rowCount()>0)==1) ? true : false;
}

Any idea?

lots of problems.
  • line #4: mysql_* ≠ PDO. either you use mysql_* or you use PDO, but not both. Prepared Statements must not be escaped anyways.
  • line #5: prepared statementís placeholders ainít quoted (thatís how they work).
  • line #9: rowCount() will always be one due to the nature of your query. what you want is to fetch the number returned by COUNT(), use fetchColumn() for that.

Was This Post Helpful? 0
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3717
  • View blog
  • Posts: 5,981
  • Joined: 08-June 10

Re: Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 03:05 AM

Line #6 also doesn't make much sense given the context. Why are you wrapping the email in percentage chars for a normal boolean comparison? Those are used when you want to search for a keyword using the LIKE operator. That's definitely not what you want when checking if a user exists.

By the way, you may want to check on the documentation for the rowCount() method. Despite what the name suggests, it may in fact not return the number of rows in a result set. It can't be relied on for that.

This post has been edited by Atli: 14 July 2013 - 03:08 AM

Was This Post Helpful? 0
  • +
  • -

#4 Yeronox  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 102
  • Joined: 12-March 12

Re: Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 04:05 AM

	function user_exist($email)
	{
		$conn = new PDO('mysql:host=localhost;dbname=php', 'root', '');
		$stm = $conn->prepare(SELECT COUNT(`user_id`) FROM `users` WHERE `email`=:email;);
		$email = ''.$email.'';
		$stm->bindParam(":email",$email,  PDO::PARAM_STR);
		$stm->execute();
		return (($stm->rowCount()>0)==1) ? true : false;
	}

So, right now the code is this one.
But how should I use fetchColumn() to see if the email already exists ?
Was This Post Helpful? 0
  • +
  • -

#5 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3717
  • View blog
  • Posts: 5,981
  • Joined: 08-June 10

Re: Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 04:15 AM

Consider line #5 a little bit better. What is it doing exactly? What effect does it have on the variable it's setting?

Also, look through line #4 again. What is missing there? How will PHP read the SQL query when it's set up like that?

Quote

But how should I use fetchColumn() to see if the email already exists ?

The COUNT() in the SQL will find out the number of rows matching the email and put that number in the first field of the only row. The fetchColumn function fetches a single column from the result set. - I suggest you read the documentation for the function if this isn't making sense to you.
Was This Post Helpful? 0
  • +
  • -

#6 Yeronox  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 102
  • Joined: 12-March 12

Re: Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 04:29 AM

View PostAtli, on 14 July 2013 - 11:15 AM, said:

Consider line #5 a little bit better. What is it doing exactly? What effect does it have on the variable it's setting?

Also, look through line #4 again. What is missing there? How will PHP read the SQL query when it's set up like that?

I read that by doing so I`ll be able to pass $email to bindParam
About the line #4, well, I don`t know, I was following a tutorial and it worked fine for him. It`s quite old though.

Quote

The COUNT() in the SQL will find out the number of rows matching the email and put that number in the first field of the only row. The fetchColumn function fetches a single column from the result set. - I suggest you read the documentation for the function if this isn't making sense to you.

return (($stm->fetchColumn()>0)==1) ? true : false;

Will this work then?
Was This Post Helpful? 0
  • +
  • -

#7 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3717
  • View blog
  • Posts: 5,981
  • Joined: 08-June 10

Re: Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 09:17 AM

View PostYeronox, on 14 July 2013 - 11:29 AM, said:

I read that by doing so I`ll be able to pass $email to bindParam

How so? You are taking the string variable $email, adding an empty string on both sides of it, and then reassigning it to that same variable. Think of it like this:
// Say this is $email to begin with:
$email = "john.doe@example.com";

// Now we run it through your line:
$email = ''.$email.'';

// If we evaluate that line to their values, it'll look like this:
"john.doe@example.com" = '' . "john.doe@example.com" . '';

// And the resulting value is....
//  > "john.doe@example.com"
// The same value it started out as.


See what I mean? You are doing precisely nothing to the variable with that line.

View PostYeronox, on 14 July 2013 - 11:29 AM, said:

About the line #4, well, I don`t know, I was following a tutorial and it worked fine for him. It`s quite old though.

Old tutorials can be pretty bad, that's true, but line #4 as you had it in your last snippet would never have worked, not even in ancient versions of PHP. The problem is that it's missing the quote marks around the query.
// You were doing this:
$stmt = $conn->prepare(SELECT stuff FROM tbl WHERE other='stuff');

// But you forgot to quote the SQL query:
$stmt = $conn->prepare("SELECT stuff FROM tbl WHERE other='stuff'");


Otherwise the query seems fine.

View PostYeronox, on 14 July 2013 - 11:29 AM, said:

return (($stm->fetchColumn()>0)==1) ? true : false;

Will this work then?

It should, bit it's a bit odd. Let me explain by breaking the statement into it's components...
// First we can fetch the count.
$count = $stmt->fetchColumn();

// And check if the count indicates that rows exist matching
// the SQL criteria.
$doRowsExist = $count > 0;

// This is where it gets weird. Why compare the TRUE or FALSE
// value above to 1? - In fact, PHP will implicitly convert 1
// to TRUE when you do this, so this does return the correct
// result, but it still doesn't make much sense.
if ($doRowsExist == 1) {
    return true;
}
else {
    return false;
}


One more thing. You manually specify TRUE or FALSE to be returned, when in fact you already have that value from the condition.

In the end, you could have written the line like this to get the same results.
return $stmt->fetchColumn() > 0;


All the other stuff around it was just complicating what was essentially a very simple thing.
Was This Post Helpful? 2
  • +
  • -

#8 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2934
  • View blog
  • Posts: 10,139
  • Joined: 08-August 08

Re: Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 09:33 AM

A more readable way might be to do this:
if($stmt->fetchColumn() > 0) {
	return true;
}
return false;

This would also work:
return ($stmt->fetchColumn() > 0);

Was This Post Helpful? 1
  • +
  • -

#9 Yeronox  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 102
  • Joined: 12-March 12

Re: Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 10:42 AM

return (($stm->fetchColumn() > 0));

This works! Anyway I misunderstood Dormilich when he wrote "line #5: prepared statementís placeholders ainít quoted (thatís how they work)."
And I removed the quotes "", otherwise if you look at my first post I wrote them.
Thanks a lot guys!
Was This Post Helpful? 0
  • +
  • -

#10 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2934
  • View blog
  • Posts: 10,139
  • Joined: 08-August 08

Re: Error with PDO: check if email exists in a row (mySQL)

Posted 14 July 2013 - 12:42 PM

I used one set of parens "()" just for readability. They're not really necessary, so a second set "(())" is more than redundant. This may be a bit pedantic, but little things like this can add up to making code less readable and therefore harder to debug.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1