Formatting array output using foreach function

Question

I have a script that follows that is supposed to collect data from a field"UserID" in my sql table, submit all data into an array, and then compare a variable to whats in the array. If the value of the variable is already in the array, tell the user that that value is invalid.

$sql = "SELECT *" //User info
    . " FROM Users" ;
$result = mysql_query($sql); 
//insert where line for assessorid


$users = array();

while(($user = mysql_fetch_assoc($result))) { 
$users[] = $user;
}

foreach($users as $user){ 
$user['UserID']; 
 }

I need the output of $users to be equivalent to array('user1','user2','user3'); Whats happening is data comes in from a form as $user_name. I want to use this in a statement like follows:

if(in_array($user_name,$users)){
  echo "username available"
}
else{
  echo "not available"}

I tried using the extract function, but that just created a big mess. Im not sure what is incorrect about what I'm doing, unless the format of $users as an array cannot be parsed in the in_array() function as it is formatted currently. Any advice is much appreciated. Thanks!

score 1 · Answer 1 · answered Dec 13 '10 at 20:24

$sql = "SELECT USERID FROM Users" ;

$result = mysql_query($sql); 

$users = array();

while(($user = mysql_fetch_assoc($result))) {
$users[] = $user['USERID'];
}

When you are saying

$users[] = $user;

You are not specifying which column in the result set to be appended to the array.

score 0 · Accepted Answer · answered Dec 13 '10 at 20:23

0

Maybe I am missing something... Why not do it like this:

SELECT UserID FROM Users WHERE Username = 'username'

Then just use mysql_num_rows() to check if the username already exists or not. This should be both faster and more efficient (memory-wise).

answered Dec 13 '10 at 20:23

mishmash

4,547
3
31
56

score 0 · Answer 3 · answered Dec 13 '10 at 20:24

In that case, you collect all data from the database and need to do some inefficient processing in PHP as well. It is better to query for that value to see if it is in the database, so:

$username = mysql_real_escape_string($username);
$query = "
  select
    count('x') as usercount
  from
    users u
  where
    u.username = '$username'";

The, if the 'usercount' is 0, the username does not exist. If > 0, the username does exist. This way, you let the database do the work it is designed to do, and the only value that is actually retreived is that single number.

score 0 · Answer 4 · answered Dec 13 '10 at 20:26

Have you tried modifying your query? Currently you are getting all of the values for every user, but you just seen to need UserID. You could do this:

$sql = "SELECT UserID FROM Users";
$result = mysql_query($sql); 

$users = array();

while(($user = mysql_fetch_assoc($result)))
{ 
  $users[] = $user['UserID'];
}

// ...
if (in_array($user_name, $users))
{
  echo 'Username not available';
}
else
{
  echo 'Username available';
}

Or you could just look up in the database for the given username:

$sql = 'SELECT count(*) FROM Users WHERE UserID = '.mysql_escape_string($user_name);
$result = mysql_query($sql);

// and then just check if the resulting row is equal to 0

score 0 · Answer 5 · answered Dec 13 '10 at 20:27

0

Are you attempting to write a script that will check if a username is taken?

If so, it may be easier (and more efficient) to structure the actual query towards this end rather than relying on the programmatic approach.

$sql = "SELECT COUNT(*) FROM Users WHERE Username = '$username'";

Then you could apply this result to a count and allow the user to register or not based on whether a value greater than zero (a user has already taken that name) or not (its free) is returned.

answered Dec 13 '10 at 20:27

DeaconDesperado

8,971
6
43
75

Of course you should never put $username unescaped into your SQL, unless you are eager to find out what others could do to your site with SQL-injection. – Jan Thomä Dec 13 '10 at 20:29
Of course - I provide this merely as an example of the COUNT query as the OP seems unaware of it. This would never be used to insert or validate a login. I generally use PDO in all database access to help proof against this as well. – DeaconDesperado Dec 13 '10 at 20:31

score 0 · Answer 6 · edited May 23 '17 at 11:55

As has been mentioned, that is a rather inefficient way to check for an existing username. The suggestions for modifying your query are good advice.

However, to address the problem with the code you provided:

in_array() will not detect the presence of a value in a multi-dimensional array. Your $users array probably looks something like this:

$users = array(
   array('userID', 'foo', 'bar'),
   array('userID', 'foo', 'bar'),
   array('userID', 'foo', 'bar')
)

and in_array will not search below the first set of indexes. If this is really what you want to do, see this question: in_array() and multidimensional array

Formatting array output using foreach function

6 Answers6