Mysql query assistance

help with syntax

[mack]

Hi all.

Heres the scenario...

I have a mysql database called foo. within that batabase I have one table called members. What I want to do is have a form where by a user can sign up and their data will be stored within the database.

The sign up will be a three stage.

1. check member id hasn't already been registered

2. if name available it will provice a form

3. store all form info in the db

It is stage 1 that is causing me problems.

What I have is a one field form for the user to insert a user id. It will then perform a search on the member id field. If it finds a match it will ask th euser to choose another name.

What I have done it assemple an sql query like the following...

$query = "select userid from members where userid like \"%$id%\"
order by userid";

$id is a variable that has been pased from the form.

Thanks very much in advance.

Mack.

[mikeyb]

Hi mack,

First off, I wouldn't use like in your query, if you had a member of bobby, then bob wouldn't be able to sign up.

What I would do is return the count of matching users:

$query = "select count(userid) as usercount from members where userid = '$id'";

If that member name is already in use, it will return 1 (or more! but it shouldn't!) if not, will be 0

[coopster]

Another approach I often use is to *serialize* the rows by using an AUTO_INCREMENT primary key. Then throw a UNIQUE keyword on the acutal userID field.

If you try to INSERT a row where the userID already exists, you'll get an error and can monitor for that.

[mack]

Thanks mikeyb

$query = "select count(userid) as usercount from members where userid = '$id'";
if (usercount == 0)
{
echo "The username you choose is available. please fill out the fields below to continue." ;
echo "<form method=\"post\" action=\"Handler.php\">";
echo "<input type=\"hidden\" name=\"userid\" value=\"$id\">";
echo "Your first name";
echo "<input type=\"text\" name=\"firstname\" size=\"50\">";
echo "your sirname";
echo "<input type=\"text\" name=\"sirname\" size=\"50\">";
echo "please choose a password";
echo "<input type=\"text\" name=\"password\" size=\"50\">";
echo "your email address";
echo "<input type=\"text\" name=\"email\" size=\"50\">";
echo "receive email?";
echo "<input type=\"text\" name=\"rmail\" size=\"50\">";
echo "male or female";
echo "<input type=\"text\" name=\"mf\" size=\"50\">";
echo "do you reside within the UK?";
echo "<input type=\"text\" name=\"ukyn\" size=\"50\">";
echo "UK area if applicable";
echo "<input type=\"text\" name=\"county\" size=\"50\"><br>";
echo "<input type=\"submit\" name=\"submit\" value=\"Submit!\">";
echo "</form>";

}
else
{
echo 'Sorry but that username has been taken. Please go <a href \"$sitemainurl/members/register.php\"back</a> and try another name';
}

That is an excerpt from my code :)

as you can probably tell in a php newbie

does that look like the correct way to go?

Mack.