Page 1 of 1

Hot to get empty field in column, and get column name?

Posted: Wed May 15, 2002 8:36 pm
by Gath
Hi.

This is a mix of PHP and MySQL question, so excuse me...

Having a table with the following configuration:
groupID, user1, user2, user3, ... untill user10

Each time a user registers (different table), i want to put him in a group (any group), wich has an empty spot (any spot)...

But i can't manage to do that... the main problem is that i can't get the column name of the first result i get looking for an empty field.

Any help?
Thanks in advance (yep, i'm new at both PHP and SQL)
:)

Posted: Wed May 15, 2002 9:51 pm
by volka
not quiet sure what you want to achieve, but this how I get it:
you have groups of max. 10 members. New members shall be related to a group having less than 10 members at this time regardless what group it might be. right?
ok, since I'm (still) unfamiliar with SQL my suggestion might be suboptimal but you may create a relation table group<->user

Code: Select all

CREATE TABLE `groups` ( `groupId` mediumint(3) unsigned NOT NULL default '0', `userId` mediumint(3) unsigned default '0' );
get all groups having less than 10 members

Code: Select all

SELECT groupId, COUNT(groupId) as members FROM groups GROUP BY groupId HAVING (members < 10)
but if all users only are members of one group there is no need for this table as you may extend the user-table by a groupId-field

Re: Hot to get empty field in column, and get column name?

Posted: Thu May 16, 2002 12:20 pm
by Gath
Thanks, but i found a way :)

And i share it here...

$checkgroup = mysql($database,"SELECT group_id FROM groups WHERE user1=0 OR user2=0 OR user3=0 OR user4=0 OR user5=0 OR user6=0 OR user7=0 OR user8=0 OR user9=0 OR user10=0");

list($checkgroup) = mysql_fetch_row($checkgroup);

for($num=1;$num<=10;$num++)
{
$col = mysql($database,"SELECT user$num FROM groups WHERE group_id=$checkgroup");
list($col) = mysql_fetch_row($col);
if($col == 0)
{
$user = "user$num";
$num = 11;
};
};

echo "$user";
[/quote]