Get all usernames from mysql ?

[perficut]

This is simple I'm sure, but I cant get the syntax right for it to work.

 

I need to fill the $existing_users array with all the username's in my database. In the code below I have inserted 3 names manually for testing purposes, but as you can see, I need to be able to put all the currently registered user names in this array for validation purposes.

 

$sql="SELECT username, FROM ILContractors WHERE username='".$user_name."'";
$result=mysql_query($sql);
$row=mysql_fetch_array($result); 


//this varible contains the array of existing users
$existing_users=array('roshan','mike','jason'); 
//value got from the get metho
$user_name=$_POST['user_name'];
//checking weather user exists or not in $existing_users array
if (in_array($user_name, $existing_users))
{
//user name is not availble
echo "no";
} 
else
{
//user name is available
echo "yes";
}
?>

[Mchl]

Remove , after `username` here:

$sql="SELECT username, FROM ILContractors WHERE username='".$user_name."'";

 

And see Example #2 here mysql_query to see how to loop through query results

[Jnerocorp]

is this just to check if the username is already registered?

 

-John

[perficut]

This is a snipit of an ajax code I D/L which check to see if a USERNAME currently exists, when my users try to register.

 

It return, obviously, a yes or no, the reste of code, then diplays a message telling them that name is used already, or it just lets them finish entering their registration info.

[PFMaBiSmAd]

The query you posted (after you fix the comma problem in it) will find any rows that match the $user_name variable. All you need to do is use mysql_num_rows() to find if the query returned a matching row. In general, you should not find yourself iterating through the rows that a query returns, testing values in it to see if there is a match. The query should perform that test, not some slow parsed/tokenized/interpreted php code. The only time you should find yourself iterating through the rows that a query returns is if you are actually using the values in each row on your web page.

 

Your thread title is "Get all usernames ...". If what you are doing is just testing if a specific usename is in the table, you don't get all the usernames to do that. The only time you would want to get all the usernames in a table is if you intended on using all the usernames, such as displaying all of them or emailing all of them...

[perficut]

PFM:  I get what your saying. No sense on actually grabbing all the data to then sift through it. Using a query with specific peramaters will do that.  However, I think the rest of the javascript need the information to process it.

 

Heres the sample program. Which in its entirety works fine. But it only has 3 names which are manually entered into the array which to compare against for availability.

<?php
//  Developed by Roshan Bhattarai 
//  Visit http://roshanbh.com.np for this script and more.
//  This notice MUST stay intact for legal use

//this varible contains the array of existing users
$existing_users=array('roshan','mike','jason'); 
//value got from the get metho
$user_name=$_POST['user_name'];
//checking weather user exists or not in $existing_users array
if (in_array($user_name, $existing_users))
{
//user name is not availble
echo "no";
} 
else
{
//user name is available
echo "yes";
}
?>

 

 

What I was trying to do is grab all the existing usernames in the database and put them into that same array so now the program will check availability against whats in the database and not just the three names.

 

Heres where Im at.  Its returning a 'YES' even if the username is in the database. So somethings not quite right.

<?php
//  Developed by Roshan Bhattarai 
//  Visit http://roshanbh.com.np for this script and more.
//  This notice MUST stay intact for legal use
$link = mysql_connect("localhost","login","pw");
mysql_select_db("my_database",$link);

$sql="SELECT username FROM ILContractors";
$result=mysql_query($sql);
$row=mysql_fetch_array($result); 


//this varible contains the array of existing users
$existing_users=array($result); 
//value got from the get metho
$user_name=$_POST['user_name'];
//checking weather user exists or not in $existing_users array
if (in_array($user_name, $existing_users))
{
//user name is not availble
echo "no";
} 
else
{
//user name is available
echo "yes";
}
?>

[PFMaBiSmAd]

<?php
// minimal code (no validation, error checking, error reporting, or error recover logic)

// output 'no' (not available) if the username was found in the table
// output 'yes' (available) if the username was not found in the table

$link = mysql_connect("localhost","login","pw");
mysql_select_db("my_database",$link);

$user_name=mysql_real_escape_string($_POST['user_name']);
$sql="SELECT username FROM ILContractors WHERE username='$user_name'";
$result=mysql_query($sql);
if(mysql_num_rows($result) == 1){
// the specific username was found
echo 'no'; // not available
} else {
// the specific username was not found (or there was an error performing the query)
echo 'yes'; // available or possibly an error with the database
}
?>

 

Edit: You should in fact make the username column a unique key so that it would be impossible to insert duplicate rows.

[perficut]

Edit: You should in fact make the username column a unique key so that it would be impossible to insert duplicate rows.

 

Thank you this worked great.

 

The script I am running, checks the username entry when they tab out of the form box, if the name is already used, they must enter a unique name to continue.  This if im not mistaken, should prevent duplicate entries before the the program tries to actually write the data, and at the same time informs the user of the availability.

 

Thank you again, I appreciate the help.

[PFMaBiSmAd]

If you have concurrent visitors to your site, you cannot guarantee the order in which their usernames will actually be inserted into the table. You must enforce uniqueness in the table and you actually need to check if the INSERT query succeeded.