MYSQL e-mail query in PHP

[mutasim]

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.

PHP Code:

$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

[mr e]

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

PHP Code:

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


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

PHP Code:

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


[CFMaBiSmAd]

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.

[firepages]

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.

[mutasim]

Originally Posted by mr e

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)

PHP Code:

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

[daemonkin]

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.

[mutasim]

Which mysql_fetch shall I use ??

[aedrin]

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

PHP Code:

if (mysql_num_rows($result) < 1) { 


[mutasim]

: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

PHP Code:

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


[PappaJohn]

PHP Code:

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

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


[mutasim]

Thanks guys... issue resolved

[aedrin]

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

It was mostly aimed at daemonkin.

[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.