Cant define a field

[TheCase]

Hi,

This is my table structure

[CODE]

-- Table structure for table showbeta

id int(11) NOT NULL auto_increment,
presenter1id int(5) NOT NULL,
presenter2id int(5) NOT NULL,
engineerid int(5) NOT NULL,
presenter1status enum('0','1','2') NOT NULL,
presenter2status enum('0','1','2') NOT NULL,
engineerstatus enum('0','1','2') NOT NULL,
[/CODE]

Now if userid 5 wants to update his status I cant do a simple query such as

UPDATE showbeta SET presenter1status=2 WHERE id=$id

because user 5 could be in presenter1id, presenter2id, or engineerid.

So either I have set up the database wrong or I need to do the following but just not sure how.

1. Check if the user falls into presenter1id, presenter2id or engineerid

2. With that info update the corresponding status field.

For example user 5 is in presenter1id so update query presenter1status.

Hope this is clear,
Thanks

[Krik]

Then have your code determine which they are before you do the update query. It is as simple as running a query to find out which they are and then creating the update query based on that.

$result = mysql_query("SELECT * FROM `showbeta` WHERE `id` = '" . $id . "'");

while ($row = mysql_fetch_array($results)) {

if($row['presenter1id'] != 0) {
    $sql_column = 'presenter1status';
}
if($row['presenter2id'] != 0) {
    $sql_column = 'presenter2status';
}
if($row['engineerid'] != 0) {
    $sql_column = 'engineerstatus';
}
}

mysql_query("UPDATE showbeta SET $sql_column = 2 WHERE id = $id";)