Sorting through multiple arrays...i think?

[aeboi80]

I have a table called members.  Each member located within the members table has a unique id field called ID

 

I have a second table called user_program in which also contains the id number of the member from the members table

 

Each member has 5 entries in the table user_program using the field called program_id  which has a value of either 1, 2, 3, 4 or 5

 

Additionally there is a user_status field within the same user_program table

 

The value of user_status is either Active, Comped, Temp-Comped or Unpaid

 

 

What I am trying to do is determine is which is the highest program_id that a specific member holds a user_status equal to Active and then assign the program_id to a variable.

 

 

Here is the structure of the user_program table

 

user_program_id

program_id

user_status

auto incrementing number

    1

Active

auto incrementing number

    2

Active

auto incrementing number

    3

Unpaid

auto incrementing number

    4

Unpaid

auto incrementing number

    5

Unpaid

 

 

Here is the code I have been working with trying to figure it out.....any help would be appreciate :-)

 

$r1 = mysql_query("SELECT * FROM members WHERE Username='".$_SESSION['loggedin']."'");
$buffer_r1 = mysql_fetch_assoc($r1);
$usrid = $buffer_r1['ID'];


$r2 = mysql_query("SELECT * FROM user_program WHERE ID='".$usrid."'");
$buffer_r2 = mysql_fetch_assoc($r2);

$level_id = $buffer_r2['program_id'];
$user_status = $buffer_r2['user_status'];
$grace_start = $buffer_r2['grace_start'];



while(!$user_status ==("Comped" || "Unpaid")){
if($level_id == "1" && ($user_status == "Active")){
	$level_name = "Professional";
}elseif($level_id == "2" && ($user_status == "Active")){
	$level_name = "Gold";
}elseif($level_id == "3" && ($user_status == "Active")){
	$level_name = "Diamond";
}elseif($level_id == "4" && ($user_status == "Active")){
	$level_name = "Platinum";
}elseif($level_id == "5" && ($user_status == "Active")){
	$level_name = "Elite";
}
}

 

 

I know the code within the while state is correct to assign the end variable, but my problem is that its only looking at the first program_id returned from the database that matches the user ID and does loop through the rest of the entries for that person.

 

 

Thanks in advance :-)

 

[sasa]

try to do all job witk SQL

<?php
$r2 = mysql_query("SELECT MAX(progrm_id) AS level FROM user_program WHERE ID='".$usrid."' AND user_status='Active'");
$buffer_r2 = mysql_fetch_assoc($r2);
$level_id = $buffer_r2['level'];
$levels = array(1 => "Professional", "Gold", "Diamond", "Platinum", "Elite");
$level_name = $levels[$level_id];
?> 

[aeboi80]

try to do all job witk SQL

<?php
$r2 = mysql_query("SELECT MAX(progrm_id) AS level FROM user_program WHERE ID='".$usrid."' AND user_status='Active'");
$buffer_r2 = mysql_fetch_assoc($r2);
$level_id = $buffer_r2['level'];
$levels = array(1 => "Professional", "Gold", "Diamond", "Platinum", "Elite");
$level_name = $levels[$level_id];
?> 

 

Thanks for your reply.  I tried that but its throwing an error:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/secret/public_html/new/Members/includes/which_level.php on line 8

 

<?php
//determines which level the member is upgraded to
$r1 = mysql_query("SELECT * FROM members WHERE Username='".$_SESSION['loggedin']."'");
$buffer_r1 = mysql_fetch_assoc($r1);
$usrid = $buffer_r1['ID'];

$r2 = mysql_query("SELECT MAX(progrm_id) AS level FROM user_program WHERE ID='".$usrid."' AND user_status='Active'");
$buffer_r2 = mysql_fetch_assoc($r2);
$level_id = $buffer_r2['level'];
$levels = array(1 => "Professional", "Gold", "Diamond", "Platinum", "Elite");
$level_name = $levels[$level_id];
?>

 

Any ideas?

[aeboi80]

Never mind I figured it out.  The word field program_id was misspelled in the query statement.

 

 

Thanks so much for your help!  Its working like a charm!