mySQL database searching for registration

[Killer_Typo]

i need to know the format to search for a user name in the database, im using PHP to submit the data

so far this is what ive got
[php]
@mysql_select_db("$DBName") or die("Unable to select
database $DBName");

$sqlquery = "INSERT INTO $table
VALUES('$id','$name','$email','$opinion')";

$results = mysql_query($sqlquery);

mysql_close();
[/php]

im using that to insert the data into the database, but i need to know the format to search the database to make sure that the username isnt already in use.

$sqlquery = "SEARCH * FROM table WHERE name =(username user wants)";

but i dont know how to tell it to output if the user name has infact been found or if it has not and the user name is good. any help would be greatly appreciated.

[samaru]

Try:

select count(user_name) as usercount from $table where user_name = '$name'

Then extract the value 'usercount' from the recordset and if it's 0, none exists. If it's greater than 0, then it exists.

[Killer_Typo]

[php]
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); //connecting to the database using the variable set
@mysql_select_db("$DBName") or die("Unable to select database $DBName"); //at connection to the databse select DBNAME (phpforms) or tell that it couldnt connect
//$sqlquery = "SELECT usr_name FROM $Table WHERE usr_name = '$_POST[usr_name]'";
$sqlquery = "SELECT COUNT(usr_name) FROM $Table WHERE usr_name = '$usr_name'";
$results = mysql_query($sqlquery);
echo "current user name " . $_POST[usr_name] . "
";
echo "query returned " . $sqlquery . "
";
echo "query returned " . $results . "
";
[/php]

i added the lines query returned .$sqlquery and query returned $results to see exactly what it was spewing out.

and heres the output of it now

current user name michael
query returned SELECT COUNT(usr_name) FROM reg_info WHERE usr_name = 'michael'
query returned Resource id #3
sorry that user name has been taken! please use your browsers back button and try a different one!

what in the world is Resouce id #3??

also i believe using mysql_query is the wrong command, so i tried mysql_field_count() in its place, but it gave me an error saying that i used the wrong syntax within the () fields. I am determined to figure this out!...but it seems to be more advanced than i thought it to be when i first set out on this task to create a login. cuz after i figure this out, im creating a login/logout and a cookie which will automaticly log a user back in!! man this is gonna be fun!

[samaru]

You're executing the sql statement but you're not retrieving anything. You have to fetch the results into something like an associative array, so you use mysql_fetch_assoc(). Try this:

[php]
$Table = 'user';
$name = 'rootx';
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); //connecting to the database using the variable set
@mysql_select_db("$DBName") or die("Unable to select database $DBName"); //at connection to the databse select DBNAME (phpforms) or tell that it couldnt connect
$sqlquery = "SELECT COUNT(user_name) as user_name FROM $Table WHERE user_name = '$name'";
$results = mysql_query($sqlquery);
$user_count = mysql_fetch_assoc($results);
echo "current user name " . $_POST['usr_name'] . "
";
echo "query returned " . $sqlquery . "
";
echo "query returned " . $user_count['user_name'] . "
";
[/php]

Also, don't retrieve form variables as $_POST[whatever], use the apostrophes like I did in the code.

[Killer_Typo]

heres the error i get

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM reg_info WHERE usr_name = 'michael'' at line 1

and heres the code that gives it.



[php]mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); //connecting to the database using the variable set
@mysql_select_db("$DBName") or die("Unable to select database $DBName"); //at connection to the databse select DBNAME (phpforms) or tell that it couldnt connect
$sqlquery = "SELECT COUNT(usr_name) as FROM $Table WHERE usr_name = '$usr_name'";
if (!$sqlquery)
{
echo(mysql_error());
}
else
{
$results = mysql_query($sqlquery);
if (!$results)
{
echo(mysql_error());
}
else
{
$user_count = mysql_fetch_assoc($results);
if (!$user_count)
{
echo(mysql_error());
}
else
{
echo "current user name " . $_POST['usr_name'] . "
";
echo "query returned " . $sqlquery . "
";
echo "query returned " . $results . "
";
echo "array returned " . $user_count . "
";
}
}
}
[/php]

i think my main problem is that im new to php and mysql, and i dont quite no all of the syntax.


sort ($user_count)
foreach ($user_count as $array)

if ($array >= 1)

stop

else

continue.

[samaru]

There's an error in your SQL statement:

$sqlquery = "SELECT COUNT(usr_name) as FROM $Table WHERE usr_name ='$usr_name'";

The "as" statement assigns aliases to columns. In this case, your're not assigning an alias to the result that count(user_name) spits out. You need to put a name for a column's alias after the "as" but you skipped that and just wrote the "FROM."

Killer_Typo, check your PMs.