How to create a mySQL query for user ids

Anon

Hello
I'm relatively new to the whole PHP/mySQL area. I have a members area on my site and when people sign up they must select an id. How can I write a script that could query my mySQL database to see if the name is taken already? I'd like the result shown to the user to say something like "that ID is taken" or "that ID is available". Any help would be appreciated. Thanks!

Anon

If the user is requesting $requested_id, then...

$sql = "SELECT table_blah.id FROM table_blah WHERE table_blah.id='$requested_id'"
$results = mysql_query($sql);

if (empty(mysql_result($results, 0)))
{
echo "ID '$requested_id' is available.";
}
else
{
echo "Hey bozo, ID '$requested_id' is already taken.";
}

You may want to change the "Hey bozo" part.

Aaron Zoller
www.AaronZoller.com (Opening June 15th, 2002)

Anon

Okay, this is what I have for the script:

<?php
//connect to db
$db = mysql_connect("localhost", "username", "password");
mysql_select_db("mydatabasename",$db);

$sql = "SELECT table_username.id FROM table_emailbase WHERE table_username.id='$requested_id'"
$results = mysql_query($sql);

if (empty(mysql_result($results, 0)))
{
echo "ID '$requested_id' is available.";
}
else
{
echo "Hey bozo, ID '$requested_id' is already taken.";
}

}
?>

I keep getting a parse error at this line:
$results = mysql_query($sql);

Am I setting this up wrong? Sorry to sound dumb--told you I was a newbie!!

nyxx23

$sql = "SELECT table_username.id FROM table_emailbase WHERE table_username.id='$requested_id'"

should read:

$sql = "SELECT table_username.id FROM table_username WHERE table_username.id='$requested_id'"

Your table names didn't match.

Anon

$sql="SELECT * FROM table WHERE id='$id'"
$result=mysql_query($sql);
$num=mysql_num_rows($result);

if ($num) {

echo "Taken fool, think of a different name!";

}

you can echo whatever you want, but I pity the fool who tries to take my name!

🙂

Anon

Yes yes I know I forgot the ";" after the sql statement. Blah noone is perfect ya know!

Anon

The reason that you are getting a parse error is because of the line right BEFORE it, you forgot a semi-colon. The SQL does not look right, as you have two different table names as jon pointed out.

Hope that helps.

Chris King

Anon

sigh

Okay, I went back and fixed it (I think) THis is what it looks like now:

<?php
//connect to db
$db = mysql_connect("localhost", "dbusername", "dbpassword");
mysql_select_db("dbname",$db);

$sql="SELECT * FROM table WHERE id='$id'";
$result=mysql_query($sql);
$num=mysql_num_rows($result);

if (empty(mysql_result($results, 0)))
{
echo "ID '$requested_id' is available.";
}
else
{
echo "Hey bozo, ID '$requested_id' is already taken.";
}

}
?>

Now I get this error:
Parse error: parse error, expecting T_VARIABLE' or'$''at line 18, which is this:
if (empty(mysql_result($results, 0)))

??????
By the way, thanks for all your help so far!

Anon

No wories... Just three little fixes and you'll be up.

You have an extra curly bracket } at the end of your code.

You do not need to use this code...

$num=mysql_num_rows($result);

...if you are using the mysql_result method. You would use the mysql_num_rows function if you wanted to replace the code...

if (empty(mysql_result($results, 0)))

... with...

if ($num==0)

Personally I only use mysql_num_rows if I am actually going to use the $num variable in something other than one if statement.

You have stored your query results in the variable $result...

$result=mysql_query($sql);

... But then you are trying to look in a variable called $results in the code...

if (empty(mysql_result($results, 0)))

Have fun.

AaronZoller.com (Opens June 15th, 2002)

Anon

OK....
I'm making progress!! LOL...I can get the form to return a result, however it keeps saying the user name is available when i know it's in the table on my mySQL server. My next question is how do I get it to look at a specific table for the query? Is that possible? Or how could I put the user ids on there so the script could read them?
This is what it looks like so far:
<?php
//connect to db
$db = mysql_connect("localhost", "dbusername", "dbpass");
mysql_select_db("dbname",$db);

$sql="SELECT * FROM table WHERE id='$id'";
$result=mysql_query($sql);

if ($num==0)
{
echo "ID '$id' is available.";
}

else if ($num>=0)
{
echo "Hey bozo, '$id' is already taken.";
}

Thanks again! 🙂

aaronzoller

Add this code "$num=mysql_num_rows($result);" like below. Need to actually count the nunmber of rows.

<?php
//connect to db
$db = mysql_connect("localhost", "dbusername", "dbpass");
mysql_select_db("dbname",$db);

$sql="SELECT * FROM table WHERE id='$id'";
$result=mysql_query($sql);
$num=mysql_num_rows($result);

if ($num==0)
{
echo "ID '$id' is available.";
}

else if ($num>=0)
{
echo "Hey bozo, '$id' is already taken.";
}

I assume your table is actually named "table" and your id field is actually called "id". Those should definitely match the names of your table's fields.

AaronZoller.com

Anon

That did it!!!

Thank you SO much!!! All of you were wonderful in guiding this rookie through the scripting process. It wasn't nearly as hard as I thought it would be. 🙂

Thanks again!
Jen

Anon

Is there any way to set up my signup page so that I can have (for example) a check id button next to the field where they enter their desired id and then when they click on that button have the result open up in a popup window?