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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Gath
Forum Newbie
Posts: 20
Joined: Wed May 15, 2002 8:36 pm

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

Post 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)
:)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
Gath
Forum Newbie
Posts: 20
Joined: Wed May 15, 2002 8:36 pm

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

Post 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]
Post Reply