MySQL if? statement

Question

In my MySQL table, there is a row labeled "state". There are 3 values possible for this. How could I utilise PHP to echo "Unbanned" as apposed to "0" in state? And "Banned" as apposed to "1" and "Temp-Banned" as apposed to "3"?

How would I use php to display "UnBanned" instead of 0 in the MySQL? I am using a table for this.

<?
          while($row = $result->fetch_assoc()){
          echo'<td>' . $row['player'] . '</td>';
          echo'<td>' . $row['admin'] . '</td>';
          echo'<td>' . $row['reason'] . '</td>';
          echo'<td>' . $row['created_at'] . '</td>';
          echo'<td>' . $row['expires_at'] . '</td>';
          echo'<td>' . $row['state'] . '</td>' . '</tr>';
          }
?>

All help is incredibly appreciated :)

@Mihai pastebin.com/1H1FyefC

Daryin Littlejohn
– Daryin Littlejohn

2013-10-08 02:00:19 +00:00
Commented Oct 8, 2013 at 2:00 — Daryin Littlejohn
– Daryin Littlejohn, Commented Oct 8, 2013 at 2:00

Explosion Pills · Accepted Answer · 2013-10-07 19:34:51Z

3

You can do it either via PHP or MySQL. There are a lot of possibilities.

SELECT
    CASE
        WHEN state = 0 THEN 'UnBanned'
        WHEN state = 1 THEN 'Banned'
        ELSE 'Temp-Banned'
    END AS state

In PHP:

while ($row = $result->fetch_assoc()) {
    switch ($row['state']) {
        case 0: $row['state'] = "UnBanned";
            break;
        case 1: $row['state'] = "Banned";
            break;
        default: $row['state'] = "Temp-Banned";
    }

answered Oct 7, 2013 at 19:34

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kermit Over a year ago

I think OP should create a table of possible status'

Explosion Pills Over a year ago

@FreshPrinceOfSO That would work great since you could select the state's name by joining on the ID which would be the number

Snowburnt · Accepted Answer · 2013-10-07 19:38:27Z

2

<?
while($row = $result->fetch_assoc()){
    echo'<td>' . $row['player'] . '</td>';
    echo'<td>' . $row['admin'] . '</td>';
    echo'<td>' . $row['reason'] . '</td>';
    echo'<td>' . $row['created_at'] . '</td>';
    echo'<td>' . $row['expires_at'] . '</td>';

 switch ($row['state']){
    case 0: echo '<td>Unbanned</td>';
        break;
    case 1: echo '<td>Banned</td>';
        break;
    case 3: echo '<td>Temp-Banned</td>';
        break;
      } 
}
?>

Alternatively, you could have a look up table for state where the records are:

STATE
STATEID STATE
0       Unbanned
1       Banned
3       Temp-Banned

in your players table you have a foreign key for state where it just holds the StateId.

Change your query to be:

SELECT * FROM Players JOIN STATE on STATE.STATEID=Players.state WHERE...

I like breaking stuff like that out, it makes it easy to create selectable lists later and it's much more extensible in the long run if other possible states are created...like if you want to break Temp-Banned into several groups, 30-60 day ban, full season ban, etc, you just change the records in the table rather than having to search for all of your queries and switch statements.

answered Oct 7, 2013 at 19:38

Snowburnt

6,9558 gold badges33 silver badges44 bronze badges

1 Comment

gen_Eric Over a year ago

You forgot the case in your switch() :-P

aaron · Accepted Answer · 2013-10-07 19:47:11Z

0

You could use the CASE statement in MYSQL OR if you don't have many states use the following code:

<?
          $a = array('0'=>'Unbanned','1'=>'Banned','3'=>'Temp-Banned');
          while($row = $result->fetch_assoc()){
          echo'<td>' . $row['player'] . '</td>';
          echo'<td>' . $row['admin'] . '</td>';
          echo'<td>' . $row['reason'] . '</td>';
          echo'<td>' . $row['created_at'] . '</td>';
          echo'<td>' . $row['expires_at'] . '</td>';
          echo'<td>' . $a[$row['state']] . '</td>' . '</tr>';
          }
?>

edited Oct 7, 2013 at 19:47

answered Oct 7, 2013 at 19:40

aaron

6874 silver badges11 bronze badges

Collectives™ on Stack Overflow

MySQL if? statement

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related