MYSQL e-mail query in PHP

This is a search I wrote, which will be looking for a unique email address in a database and bring back the fields that I asked for (for 1 record). I believe I have done the right thing using mysql_results (please advise if not..)

My problem comes when the e-mail address does not exist.. It brings up an error about not being able to select that record.

I need an "if" written in PHP so i can script what to do if the e-mail address is not registered.


$query = "SELECT `user_id`, `user_email`, `username` FROM `database` WHERE `user_email` = '$_POST[email]'";

$result = mysql_query($query);

$userid=mysql_result($result,0,"user_id");



Thanks in advance,

- Mutasim

First, sanitize your input!

All someone has to do is set their email to something like this and they'll drop your database, that = bad ;)

'; DROP TABLE database; SELECT * FROM database WHERE user_id='


To sanitize, do something like

$email = mysql_real_escape_string($_POST['email']);


For your problem, something along the lines of this should work

if(!empty(trim($email)))
{
// If the email is not empty, query the result
}

The mysql_result() function, besides being the SLOWEST way to get a piece of data from the result set, has the unfortunate problem of throwing a PHP Warning message when the result set contains no rows, because it always attempts to access the row given by the second parameter.

If you want to use the mysql_result() function, you must first use the mysql_num_rows() function to insure that there is at least a row with the row number that you are accessing, or

You should use one of the mysql_fetch_xxxxxx functions. They operate faster than the mysql_result() and they don't throw a Warning message when there are no rows in the result set.

All someone has to do is set their email to something like this and they'll drop your database
MySQL does not support chained queries so that will not work , that said, mr e is correct in that all potentially tainted data needs to be sanitized.

All someone has to do is set their email to something like this and they'll drop your database, that = bad ;)


I appreciate your concern :), I think I already have that covered in PHP:
(Only to avoid spamming)
if(eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $_POST[email]))
{
// e-mail is valid so search, if not register
}


So I need to use mysql_fetch ? Please advise

Yeah,

I would query the number of rows containing the required fields
if(Query_num_rows <1){
//error
} else {
//run query
}

Hope this helps.

D.

Which mysql_fetch shall I use ??

My recommendation is mysql_fetch_assoc().

if(Query_num_rows <1){

This is not helpful. Invalid name, and it's not called as a function. PHP would treat it as a constant, one which does not exist (which results in a warning).


if (mysql_num_rows($result) < 1) {

:P

Yea , I kinda guessed that is wasn't that code... I'm not a complete n00b ;)

this is what I did...

not sure if i need to and how to include mysql_fetch_assoc


$result = mysql_query($query);

if (mysql_num_rows($result) !== 0)
{
$userid=mysql_result($result,0,"user_id");
$username=mysql_result($result,0,"username");
}

$result = mysql_query($query);

if (mysql_num_rows($result) !== 0)
{
$row = mysql_fetch_assoc($result);

$userid = $row['userid'];
$username = $row['username'];
}

Thanks guys... issue resolved ;)

Yea , I kinda guessed that is wasn't that code... I'm not a complete n00b

It was mostly aimed at daemonkin.

sorry about that aedrin. Was just writing pseudo code quickly. If I do give snippets I'll be sure to use the PHP tags and insert the correct function names.

D.