[Scenario]

I have this piece of code:

pawn Код:

stock DoesEmailAddressExist(const emailaddress[])
{
    new szQuery[300], iReturnVal;
    format(szQuery, sizeof(szQuery), "SELECT `Email Address` FROM `Accounts` WHERE `Email Address` = '%s'", emailaddress);
    mysql_query(szQuery);
    
    mysql_store_result();
    if(mysql_num_rows() > 0)
    {
        mysql_free_result();
        iReturnVal = 1;
    }
    else if(mysql_num_rows() == 0)
    {
        mysql_free_result();
        iReturnVal = 0;
    }
    return iReturnVal;
}

When players register on my server, their email address is stored. When new players register, I don't want them using the same email address. Therefore, this function is there to check if the email address is already saved in the DB. Now, my question is; Is the query used in this function the most efficient way to determine if the address exists, or is there an actual syntax for this?

[[HiC]TheKiller]

I don't see any issue with it. You could possibly add it to another query that checks something else too.

[Calgon]

Theoretically, this should work: (credits)

Код:

SELECT COUNT('Email Address') AS NumOccurrences FROM Accounts HAVING ( COUNT(NumOccurrences) >= 1 )

(If that doesn't work, it's because of the two speech marks in the COUNT area, it works fine if you use one word without speech marks.)

You can just return the result, 0 if no, higher (or equal) 1 if so.

Also, a little tip: It's really not wise to use spaces for column names, it's more problematic. Use camel case.

Oh yeah, this code will only check replicates in the database already, not what criteria you specify.

[Scenario]

Quote:

Originally Posted by Calgon Theoretically, this should work: (credits) Код: SELECT COUNT('Email Address') AS NumOccurrences FROM Accounts HAVING ( COUNT(NumOccurrences) >= 1 ) (If that doesn't work, it's because of the two speech marks in the COUNT area, it works fine if you use one word without speech marks.) You can just return the result, 0 if no, higher (or equal) 1 if so. Also, a little tip: It's really not wise to use spaces for column names, it's more problematic. Use camel case.

Wouldn't the second query there be better?

Код:

SELECT email FROM users GROUP BY email HAVING ( COUNT(email) = 1 )

And thanks for the tip, I always wondered about that!

[Calgon]

Quote:

Originally Posted by RealCop228 Wouldn't the second query there be better? Код: SELECT email FROM users GROUP BY email HAVING ( COUNT(email) = 1 ) And thanks for the tip, I always wondered about that!

That only checks if there's 1 result, mine checks if there's 1 or more.

[Scenario]

Quote:

Originally Posted by Calgon That only checks if there's 1 result, mine checks if there's 1 or more.

Oh okay, but I think I'll only need the one that check's if there's one result.

[Calgon]

Never hurts to be safe. Except in some cases...

[Scenario]

Quote:

Originally Posted by Calgon Never hurts to be safe. Except in some cases...

You pose a good point there! Thanks again Calgon.

One more question, is it okay to use spaces in table names, or should I avoid spaces in MySQL as much as possible?

[Lorenc_]

So, you're creating a string 300 cells, "SELECT `Email Address` FROM `Accounts` WHERE `Email Address` = '%s'" = 67 + A normal email length = 30 + a null character = 98

You just need 98 characters, save some memory realcop! You can round off the 98 to 100 if you want

[jamesbond007]

Quote:

Originally Posted by Calgon Never hurts to be safe. Except in some cases...

"except in some cases... " lol SQLI )))