Help with loop and count function

[smoke]

I am going bannanas so if ayone can help I would be forever thankful. I am trying to create a simply membership db that will give all regular members a member id numer that is 500 or higher and all special members an id that is 1-500. I originally planned to give special members a classification of 'S' and regular members a classification of 'm' to seperate the two. Now the hard part! What I want to do when the page is loaded is to query the members table if the new member isn't 's' then get the highest memberid availible and add 1 to the memberid number. (Thereby setting the new memberid to an increment of one higher that the highest member that was already stored in the datatbase) If the new member was special (S), then I want to get the highest memberid with the 'S' classification and do the same. That was each time a new member is added the memberid will increase to the next highest number and I won;t have to try and figure out the memberid per each new member added. I hope that this problem is easy to understand. Anybody, please help me.

Desperate and reallllly tired,
The Big Goof

[bpat1434]

What significance will the memberid hold? If none, drop it and just use the 's'/'m' flags that you have.

// For special members.  Member flag of 'S'
$squery = "SELECT MAX(memberid) FROM `table` WHERE memberflag='s'";

// For regular members.  Member flag of 'M'
$mquery = "SELECT MAX(memberid) FROM `table` WHERE memberflag='m'";

Then, just add 1 to teh result of that query, and you're on your way:

$flag = 'S';
switch($flag)
{
  case 'S':
    $query = "SELECT MAX(memberid) AS maxid FROM `table` WHERE memberflag='s'";
    break;
  case 'M':
    $query = "SELECT MAX(memberid) AS maxid FROM `table` WHERE memberflag='m'";
    break;
}

$result = mysql_query($query);
if($result)
{
  $max = mysql_result($result, 0, 'maxid');
  $max = $max+1;
}
else
{
  echo 'Error in query.<br>'.mysql_error();
}

Then just do a normal insert.

~Brett

[smoke]

Thanks for the reply! All members must have an ID # for identification purposes. (logins, stats, etc.)

[smoke]

OK I tried the above suggestion and I am still having problems. Basically what I need to do is when a new member registers (all members registering on the webpage will be regular) I need for the php to grab the highest mid(member id #) and add 1 so that the new member will recieve a unique member ID #(mid). One of the big problems is that special members need to be assigned id #'s from 1 to 1000 and regular members need to be assigned numbers 1000-infinity. I am very new to php so I am sorry if I just don't understand the solution above.

Please Help,

[bpat1434]

okay, so you're missing my point. If each member needs a unique number, use an auto-increment field that is "UNIQUE" (or the primary key). Then, the member id will be in sequential order, and you have a column that is the "flag". This column holds either 1 or 0 ('s' or 'm') for special or regular.

SOmething like:

CREATE TABLE `users` (
`mid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 35 ) NOT NULL ,
`password` VARCHAR( 255 ) NOT NULL ,
`last_login` DATETIME NOT NULL ,
`posts` INT( 11 ) NOT NULL ,
`flag` TINYINT( 1 ) NOT NULL DEFAULT '0'
) TYPE = MYISAM ;

(that is mySQL 5 so there may be some differences).

Now, in flag 0 is considered false (or regular member) and anything else is true (or special).

Doing it that way would make it 100 times easier since you wouldn't ahve to get the last ID and increment it one. Rather, you just set a flag.

~Brett

[smoke]

ohhhhh ok, I think I may understand now. I will try this out and see if your genuis is my savior. Thanks!